TRIGGERS insert same table - PLSQL

问题:

Here's my problem:

I have to transform something like this

Into this in the same table

I've tried triggers but I have mutating issues, I have no control about the inserts but I need to do that for every row with value larger than 1. Any sugestions?

回答1:

Depending on the number of transactions you're looking at, it may not be a feasible approach, but you might try something along these lines:

CREATE OR REPLACE TRIGGER {TRIGGER NAME}
BEFORE INSERT ON {TABLE HERE}
FOR EACH ROW
DECLARE
   l_val INTEGER;
BEGIN
   l_val := :new.value - 1;

   IF l_val > 0 THEN
      :new.value := 1;

      FOR i IN 1..l_val LOOP
         INSERT INTO {TABLE HERE}
         VALUES (:new.name, 1);
      END LOOP;
   END IF;
END {TRIGGER NAME};
/

回答2:

You need to use view and instead of trigger.

1. Creating table create table testing_trig (c char(1), i integer);
2. Creating view create or replace view testing_trig_view as select * from testing_trig;
3. Creating trigger

CREATE OR REPLACE TRIGGER testing_trg
INSTEAD OF INSERT
ON testing_trig_view
BEGIN
  FOR i IN 1 .. :NEW.i
  LOOP
    INSERT INTO testing_trig VALUES (:NEW.c, 1);
  END LOOP;
END;
/

4. Insert into view insert into testing_trig_view values ('A', 3);
5. Validate

SQL> select * from testing_trig;

C    I
- ----------
A    1
A    1
A    1