CODEWITHSUNDEEP
triggersinsertdb2db2-400
Marçal Torroella
Marçal Torroella

Reputation: 143

Simple IBM DB2 Trigger

I'm trying to create a trigger in DB2 (AS400) to insert/delete a row into a table when an insert/delete is triggered on a different table, but I need to use information about the triger table.

The example would be I would like is like this (column 1 from table 1 and table 2 are the same and unique in table 2):

CREATE TRIGGER MY_TRIGGER
AFTER INSERT OR DELETE ON DB1.TABLE1
BEGIN
      IF INSERTING 
              THEN INSERT INTO DB1.TABLE2 (Col1, Col2) VALUES (Db1.TABLE1.Col1, 0);
      ELSEIF DELETING
              THEN DELETE FROM Db1.TABLE2 WHERE Col1=TABLE1.Col1;      
      END IF;
END

But this doesn't work (it doesn't recognize TABLE1.Col1 on insert/delete statements).

Also it would prompt me an error (I guess) since it would create a duplicate key when a second row is inserted in Table 1. How could I avoid errors (just skip the insert) when the Table2.Col1 already exists?

Upvotes: 3

Views: 6079

Answers (1)

jmarkmurphy
jmarkmurphy

Reputation: 11473

Try adding correlation names like this:

CREATE TRIGGER MY_TRIGGER
AFTER INSERT OR DELETE ON DB1.TABLE1
REFERENCING OLD ROW AS OLD
            NEW ROW AS NEW
BEGIN
      IF INSERTING 
              THEN INSERT INTO DB1.TABLE2 (Col1, Col2) VALUES (NEW.Col1, 0);
      ELSEIF DELETING
              THEN DELETE FROM Db1.TABLE2 WHERE Col1=OLD.Col1;      
      END IF;
END

The trigger has access to both the old and new image of a row. You need to tell it which to use. BTW, only the update action populates both the old and new image. Insert only provides the new image, and delete only provides the old image. One might think that SQL could figure that out, but no, you still have to tell it explicitly.

EDIT This is the final trigger actually used (from comments, thank you @MarçalTorroella)

CREATE TRIGGER MY_TRIGGER 
  AFTER INSERT OR DELETE ON DB1.TABLE1 
  REFERENCING OLD ROW AS OLD 
              NEW ROW AS NEW 
  FOR EACH ROW MODE DB2ROW
  BEGIN 
    DECLARE rowcnt INTEGER; 
    IF INSERTING THEN
      SELECT COUNT(*) 
        INTO rowcnt 
        FROM DB1.TABL2 
        WHERE Col1 = NEW.Col1; 
      IF rowcnt = 0 THEN 
        INSERT INTO DB1.TABLE2 (Col1, Col2) 
          VALUES (NEW.Col1, 0); 
      END IF;
    ELSEIF DELETING THEN 
      DELETE FROM Db1.TABLE2 
        WHERE Col1=OLD.Col1; 
    END IF; 
  END

Upvotes: 4

Related Questions