PLSQL Insert and update another table with trigger

0
votes

I am new to plsql; I have 2 tables, tableA and tableB.

tableA is my main table. After insert or update on tableA I want to update it with its related table.

For example: tableA has the column named 'GID_FROM_B' and tableB has the column named 'GID'. I can match this table's values with id and counter. According to below table I want to add values of (2, 5, '') to tableA from my interface. And gid_from_b will be updated with trigger. And I wrote the trigger below.

tableA:
id     |   counter  |   gid_from_b  |
1             3            xyz                            


tableB:
id     |   counter  |      gid      |
1             3            xyz                            
2             5            abc 


CREATE OR REPLACE TRIGGER gid_update
AFTER INSERT OR UPDATE ON DBO.TABLEA
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW

UPDATE TABLEA
   SET GID_FROM_B =  TABLEB.GID
   WHERE TABLEA.ID = TABLEB.ID AND TABLEA.COUNTER = TABLEB.COUNTER;
END;
2
sqloracleplsqltriggersdatabase-trigger

2 Answers

1
votes

For only INSERT operation to be performed, we need to replace AFTER INSERT OR UPDATE with BEFORE INSERT, where not possible to create an AFTER INSERT TRIGGER with :new prefixed columns.

The following suits well for your aim :

CREATE OR REPLACE TRIGGER gid_update
BEFORE INSERT ON TableA
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN 
  for c in ( select b.gid from tableB b where b.id = :new.id and b.counter = :new.counter )
  loop
    :new.gid_from_b := c.gid; 
  end loop; 
END;
1
votes

You don't need to run any update statement, simply use a select into :NEW.gid_from_b. Note that it should be a BEFORE UPDATE TRIGGER if you want to modify values of :NEW columns.

This assumes that your TableB has a single row for each id,counter combination. If not, you may have to fetch MAX(gid) MIN(gid)or whatever suits you.

CREATE OR REPLACE TRIGGER gid_update
   BEFORE INSERT OR UPDATE ON TABLEA
FOR EACH ROW WHEN (NEW.gid_from_b IS NULL)
BEGIN
 SELECT gid INTO 
    :NEW.gid_from_b FROM tableB b 
  WHERE b.id =:NEW.id AND b.counter = :NEW.counter;
END;
/

dbfiddle