Simple SQL trigger

In a previous post I wrote how to create a simple trigger in RPG. Comments made upon that post suggested I could also create a trigger using SQL, using a lot less code than the RPG equivalent.

Whether I use a RPG trigger program or a SQL trigger the same logic applies. The trigger is attached to a file, and every time a database operation is performed to the file the logic within the trigger is performed. I am going to apply my SQL trigger to a file called TESTFILE (I know I get no marks for originality with object names) and whenever an insert, delete, or update performed I will output to my trigger output file, TRGOUTFILE.

There is a difference in the Trigger output file compared to the one I used with the RPG trigger program. In SQL I can retrieve the entire job name (job number/user/job name) in one value, so I decided, for speed purposes, not to split it into its constituent parts. Therefore, my output file looks like:

A REF(MYLIB/TESTFILE) A R RCDFORMAT A TRGTIME Z COLHDG('Trigger' 'time') A JOBNAME 28A COLHDG('Job' 'name') A TRGTYPE 2A COLHDG('Trigger' 'type') A PARTNBR R A PARTDESC R

I put the SQL statement for the trigger in a source member that way there is a record of what the trigger is, and if needed it can be recreated. The statement itself may look a bit different from others I have used in this web site, but most of it is easy to understand.

01 CREATE OR REPLACE TRIGGER TRG_TESTFILE 02 AFTER INSERT OR DELETE OR UPDATE ON MYLIB.TESTFILE 03 REFERENCING NEW ROW AS N OLD ROW AS O 04 FOR EACH ROW MODE DB2ROW 05 BEGIN 06 DECLARE TSTAMP TIMESTAMP ; 07 IF INSERTING THEN 08 INSERT INTO MYLIB.TRGOUTFILE VALUES(CURRENT TIMESTAMP, JOB_NAME, 'I', N.PARTNBR, N.PARTDESC) ; 09 END IF ; 10 IF DELETING THEN 11 INSERT INTO MYLIB.TRGOUTFILE VALUES(CURRENT TIMESTAMP, JOB_NAME, 'D', O.PARTNBR, O.PARTDESC) ; 12 END IF ; 13 IF UPDATING THEN 14 SET TSTAMP = CURRENT TIMESTAMP ; 15 INSERT INTO MYLIB.TRGOUTFILE VALUES(TSTAMP, JOB_NAME, 'U0', O.PARTNBR, O.PARTDESC) ; 16 INSERT INTO MYLIB.TRGOUTFILE VALUES(TSTAMP, JOB_NAME, 'U1', N.PARTNBR, N.PARTDESC) ; 17 END IF ; 18 END ;

Line 1: CREATE OR REPLACE is a nice feature added in IBM i 7.2 and the later TRs for 7.1 . It saves me having to drop the trigger before creating the new version of it. For earlier releases I would just have CREATE. The rest of the line says that my trigger will be called TRG_TESTFILE. I always include this name in the text of the source member to make it easier to identify which source member is the one for the trigger.

Line 2: AFTER indicates that the trigger will happen after the database operation for inserts, deletes, and updates to the file TESTFILE in my library.

Line 3: This line means that all the columns/fields in the new row/record will be prefixed with "N", and the old with "O".

Line 4: This is a bit of an oddity. DB2ROW means that the trigger will be executed after each row/record operation. The alternative, DB2SQL, will only execute after all the row operations have completed.

Line 5: Marks the beginning of the trigger code.

Line 6: I am defining a variable, TSTAMP, which is a timestamp. I will use this when inserting the update rows.

Lines 7 – 9: If the operation was an insert then a row is inserted into the trigger output file. As this is for an insert only the new values from the file are used.

Lines 10 – 12: When a delete is performed this part of the trigger is performed, which inserts the old values into the output file.

Lines 13 – 17: For an update I want both the old and new values. I also want both rows to have the same timestamp. If I had used CURRENT TIMESTAMP then the two rows would have different timestamps. By moving CURRENT TIMESTAMP to the variable I defined on line 6, and then using that I can guarantee both rows will have the same timestamp value.

Line 18: This END matches with the BEGIN on line 5, and marks the end of my trigger's code.

I use the Run SQL Statements command, RUNSQLSTM, add the trigger to the file. The CREATE TRIGGER creates a ILE C program in my library which is my trigger program.

OBJECT TYPE ATTRIBUTE TEXT TEST_00001 *PGM CLE SQL TRIGGER TEST_TESTFILE

As with the RPG trigger program if I want to see what trigger is on the file I can use the Display File Description command, DSPFD…

DSPFD FILE(TESTFILE) TYPE(*TRG)

Or I can use the SYSTRIGGER view.

SELECT CAST(TABSCHEMA AS CHAR(10)) AS Table_library, CAST(TABNAME AS CHAR(10)) AS Table_name, TRIGTIME, EVENT_U,EVENT_I,EVENT_D, CAST(TRIGPGMLIB AS CHAR(10)) AS Trigger_library, CAST(TRIGPGM AS CHAR(10)) AS Trigger_program, TRIGNAME FROM QSYS2.SYSTRIGGER WHERE TABSCHEMA = 'MYLIB' AND TABNAME = 'TESTFILE'

Which gives me:

TABLE_LIBRARY TABLE_NAME TRIGTIME EVENT_U EVENT_I EVENT_D MYLIB TESTFILE AFTER Y Y Y TRIGGER_LIBRARY TRIGGER_PROGRAM TRIGNAME MYLIB TEST_00001 TRG_TESTFILE

You can learn more about using this view in Finding triggers using SYSTRIGGER.

I can just create a trigger for one database operation. For example, I want to insert into my trigger output file every delete operation that is performed by someone else.

01 CREATE OR REPLACE TRIGGER TRG_TESTFILE_1 02 AFTER DELETE ON TESTFILE 03 REFERENCING OLD ROW AS O 04 FOR EACH ROW MODE DB2ROW 05 BEGIN 06 IF CURRENT_USER <> 'SIMON' THEN 07 INSERT INTO TRGOUTFILE VALUES(CURRENT TIMESTAMP, JOB_NAME, 'D', O.PARTNBR, O.PARTDESC) ; 08 END IF ; 09 END ;

Line 1: I am giving this trigger a different name, TRG_TESTFILE_1.

Line 2: I am only interested when a delete is performed.

Line 3: When a delete is performed there is only information in the old row.

Lines 6 - 8: If the current user performing this database operation, delete, is not using the user profile SIMON insert the row into the output file.

I have made a deliberate mistake here. There was already a trigger on this file, all I have changed is the delete trigger the others remain.

TABLE_LIBRARY TABLE_NAME TRIGTIME EVENT_U EVENT_I EVENT_D MYLIB TESTFILE AFTER Y Y Y MYLIB TESTFILE AFTER N N Y TRIGGER_LIBRARY TRIGGER_PROGRAM TRIGNAME MYLIB TEST_00001 TRG_TESTFILE MYLIB TEST_00002 TRG_TESTFILE_1

If I only want to use the second trigger then I need to remove the existing triggers from the file. I could use the Remove Physical File Trigger command, RMVPFTRG,…

RMVPFTRG FILE(PGMSDHTST3/WA042P)

Or I could use DROP TRIGGER:

DROP TRIGGER TRG_TESTFILE

This DROP TRIGGER leaves the other trigger, the one with only the delete, in place.

While I do like the simplicity of the code for SQL triggers it does become a real chore to key in all the columns for a file with a lot of fields.

01 CREATE OR REPLACE TRIGGER TRG_ORDHDR 02 AFTER INSERT OR DELETE OR UPDATE ON MYLIB.ORDHDR 03 REFERENCING NEW ROW AS N OLD ROW AS O 04 FOR EACH ROW MODE DB2ROW 05 BEGIN 06 DECLARE TSTAMP TIMESTAMP ; 07 IF INSERTING THEN 08 INSERT INTO MYLIB.T_ORDHDR VALUES(CURRENT TIMESTAMP, JOB_NAME, 'I', N.COMPANY,N.BRANCH,N.DEPARTMNT,N.ORDNBR,N.ITEMNBR, N.DUEDATE,N.BUYER,N.REFNBR,N.UPRICE,N.EXTPRICE, N.VENDOR,N.INVOICED,N.CURRENCY,N.QTYORD,N.TAXPERCNT, N.UOFM,N.REQNBR,N.FRGHTAMT,N.TAXAMT,N.DEVQTY,

Unless you know a short cut. If you do please share it in comments below.

 

You can learn more about this from the IBM website:

• CREATE TRIGGER
• DROP including DROP TRIGGER

 

This article was written for IBM i 7.3, and should work for earlier releases too.