Working with DB2 Triggers

When creating triggers in DB2, you can set them to be NO CASCADE BEFORE, AFTER, and INSTEAD OF.

Similarly to Oracle and SQL Server, AFTER triggers execute after the intended SQL command executes; they always fire if the SQL command doesn't break any constraints existing on that data table. NO CASCADE BEFORE and INSTEAD OF triggers fire before the command executes (so before any constraints are checked), allowing you to perform changes on the actions originally intended by the user. In addition, the NO CASCADE BEFORE trigger prevents the event that fired the trigger from executing any other triggers.

Like Oracle, DB2 supports row-level and statement-level triggers. Row-level triggers are applied once for each affected row, and statement-level triggers are applied once per statement (so even if a large number of rows is affected, the trigger is applied once).

You can also use the REFERENCING clause to specify some temporary variables to store information about the row or table before and after the trigger has been processed. Old rows are specified using OLD AS old_name, and new rows are specified using the NEW AS new_name, as you'll see in the examples.

Logging Table Activity using AFTER Triggers

Again, let's create the FriendAudit table, which you'll use to hold audit information:

CREATE TABLE FriendAudit
(FriendAuditID INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY NOT NULL,
 Operation VARCHAR(10),
 RecordedOn DATE DEFAULT CURRENT_DATE,
 OldName VARCHAR(50),
 NewName VARCHAR(50),
 OldPhone VARCHAR(15),
 NewPhone VARCHAR(15));

Notice that, like SQL Server, you can use a standard built-in function for ensuring that your FriendAuditID is always an automatically generated unique number. Also, you use the CURRENT_DATE function to provide the default value for the RecordedOn field.

Logging INSERT Operations

Let's now create the trigger that runs when you insert values:

CREATE TRIGGER InsFriendAuditTrig
AFTER INSERT ON Friend
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
  INSERT INTO FriendAudit (Operation, NewName, NewPhone)
  VALUES ('Insert ', N.Name, N.PhoneNo);
END

Take care to change the statement termination character in the command console before executing the statement.

Tip

Note that you must always specify MODE DB2SQL, even though DB2SQL is the only mode currently available.

It's time for a test. Run the following statements:

INSERT INTO Friend(Name, PhoneNo) VALUES('Jerry', '001-Jerry');
INSERT INTO Friend(Name, PhoneNo) VALUES('Harrison', '999-Harry');
INSERT INTO Friend(Name, PhoneNo) VALUES('Peter', '223223223');

Now query FriendAudit to see the results:

SELECT * FROM FriendAudit;

You should see the following:

   FRIEND

   AUDITID   OPERATION   RECORDEDON   OLDNAME   NEWNAME  OLDPHONE  NEWPHONE
   -------   ---------   ----------   -------   -------  --------  ---------
     1         Insert    03/10/2003      -        Jerry      -     001-Jerry
     2         Insert    03/10/2003      -        Harrison   -     999-Harry
     3         Insert    03/10/2003      -        Peter      -     223223223

You've created a trigger called InsFriendAuditTrig (you're limited to 18-character names) that fires after an INSERT is performed on the Friend table. Again, because it's an AFTER trigger, it's invoked only after all other constraints have been checked and the new row has been introduced.

In this example, you referenced new rows using the prefix N:

REFERENCING NEW AS N

This allows you to log the new information in the FriendAudit table:

BEGIN ATOMIC
  INSERT INTO FriendAudit (Operation, NewName, NewPhone)
  VALUES ('Insert ', N.Name, N.PhoneNo);
END;

Applying Conditions for the Trigger

Like Oracle, DB2 can use conditions to control whether the trigger is executed using the WHEN parameter.

Note that to amend a trigger, you first need to drop the original and replace it with the new trigger. Start by dropping the first trigger you created:

DROP TRIGGER InsFriendAuditTrig;

Now create the following trigger. This trigger only logs INSERT operations when the name to be inserted is Peter:

CREATE TRIGGER InsFriendAuditTrig
AFTER INSERT ON Friend
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
WHEN (N.Name='Peter')
BEGIN ATOMIC
  INSERT INTO FriendAudit (Operation, NewName, NewPhone)
  VALUES ('Insert ', N.Name, N.PhoneNo);
END

So, let's test this. Clear the two tables by running the following statements:

DELETE FROM Friend;
DELETE FROM FriendAudit;

Now execute the INSERT statements again:

INSERT INTO Friend(Name, PhoneNo) VALUES('Jerry', '001-Jerry');
INSERT INTO Friend(Name, PhoneNo) VALUES('Harrison', '999-Harry');
INSERT INTO Friend(Name, PhoneNo) VALUES('Peter', '223223223');

You'll see that only the last insert was logged into FriendAudit:

   FRIEND

   AUDITID OPERATION RECORDEDON OLDNAME NEWNAME  OLDPHONE NEWPHONE
   ------- --------- ---------- ------- -------- -------- ---------
      4     Insert   03/10/2003     -     Peter      -    223223223

Again, as you saw with Oracle, you only added one line to this trigger to restrict its functionality:

WHEN (N.Name='Peter')

Logging DELETEs and UPDATEs

It's time to create triggers that handle deleting and updating data in the Friend table. Before moving on, make sure you have the version of InsFriendAuditTrigger that logs all inserts, not just the ones for Peter. If you need to drop the trigger to recreate it, use the following statement:

DROP TRIGGER InsFriendAuditTrig;

Now let's create a trigger that handles updates as follows:

CREATE TRIGGER UpdFriendAuditTrig
AFTER UPDATE ON Friend
REFERENCING OLD AS O NEW AS N
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
  INSERT INTO FriendAudit (Operation, OldName, OldPhone, NewName, NewPhone)
  VALUES ('Update ', O.Name, O.PhoneNo, N.Name, N.PhoneNo);
END

And add a trigger to handle deletions as follows:

CREATE TRIGGER DelFriendAuditTrig
AFTER DELETE ON Friend
REFERENCING OLD AS O
FOR EACH ROW MODE DB2SQL
BEGIN
  INSERT INTO FriendAudit (Operation, OldName, OldPhone)
  VALUES ('Delete ', O.Name, O.PhoneNo);
END

Now, clear all data from the two tables as before:

DELETE FROM Friend;
DELETE FROM FriendAudit;

You're ready to run some test statements. Enter the following and then check the result in the FriendAudit table:

INSERT INTO Friend(Name, PhoneNo) VALUES('Jerry', '001-Jerry');
INSERT INTO Friend(Name, PhoneNo) VALUES('Harrison', '999-Harry');
INSERT INTO Friend(Name, PhoneNo) VALUES('Peter', '223223223');
UPDATE Friend SET PhoneNo = 'Unknown';
DELETE FROM Friend;

Performing a simple SELECT against FriendAudit should produce the following results:

   FRIEND

   AUDITID  OPERATION  RECORDEDON   OLDNAME   NEWNAME    OLDPHONE    NEWPHONE
   -------  ---------  -----------  -------   --------   ---------   ---------
      8       Insert    03/10/2003     -        Jerry       -        001-Jerry
      9       Insert    03/10/2003     -        Harrison    -        999-Harry
     10       Insert    03/10/2003     -        Peter       -        223223223
     11       Update    03/10/2003   Jerry      Jerry     001-Jerry  Unknown
     12       Update    03/10/2003   Harrison   Harrison  999-Harry  Unknown
     13       Update    03/10/2003   Peter      Peter     223223223  Unknown
     14       Delete    03/10/2003   Jerry       -        Unknown      -
     15       Delete    03/10/2003   Harrison    -        Unknown      -
     16       Delete    03/10/2003   Peter       -        Unknown      -

Note

For SQL Server and Oracle, you also saw how to temporarily enable and disable a trigger. DB2 doesn't have built-in support for this feature, but a few workarounds do exist. They're explained in the article "How to Temporarily Disable Triggers in DB2 Universal Database," located at http://www7b.software.ibm.com/dmdd/library/techarticle/0211yip/0211yip.html.