Wednesday, September 21, 2016

Simple SQL trigger

create a trigger using sql

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:

 

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

19 comments:

  1. Thanks Simon, do we need a exclusive lock on the file to add a trigger or drop it?

    ReplyDelete
    Replies
    1. As a trigger is a trigger, no matter whether it is RPG or SQL, you do need an exclusive lock.

      Delete
    2. I believe the latest DB2 update removed that requirement. I don't recall if it was with adding/removing or enabling/disabling or both but there was something about not needing an exclusive lock anymore. A search should turn up the exact details.

      Delete
  2. FYI, you can name your source member TESTFILETR and then in the trigger program script have
    PROGRAM NAME TESTFILETR
    after FOR EACH ROW MODE DB2ROW
    this will allow the iSeries to build the Trigger program with a recognizable name instead of TEST_00001 which is system generated.
    BEGIN and END are used for compound statements if you only have one statement I don't believe you need them, unless you are also using variables then you would need it to.

    ReplyDelete
  3. When was JOB_NAME introduced? It will not compile on my system. :(

    ReplyDelete
    Replies
    1. Having a search in the KnowledgeCenter it is in the 7.2 documenation, but not in the 7.1.

      Delete
    2. I wrote a UDF to get the value. Thanks for the push.

      Delete
  4. Hi Simon, nice article, thanks.

    Yes, you're right, in "common" SQL is easy to avoid adding all columns:
    INSERT INTO MYLIB.T_ORDHDR
    SELECT
    CURRENT TIMESTAMP, JOB_NAME, 'I',
    ORDHDR.*
    FROM ORDHDR


    But here, unless you use a common INSERT (taking data from the source file), I don't know any other method.
    But if we do that in that way (INSERT INTO...SELECT FROM...), we aren't taking advantage of the data stored in the trigger.

    Could be an option, but not the best one...

    INSERT INTO T_ORDHDR A
    SELECT
    CURRENT TIMESTAMP, JOB_NAME, 'I',
    ORDHDR.*
    FROM ORDHDR B
    WHERE A.KEY=B.KEY

    ReplyDelete
  5. Hi, We just ran into an issue wherein we have the TRGOUTFILE in different libraries and wanted the corresponding file used based on the library list. The trigger was still using the file from MYLIB. (Did not hard code MYLIB in the trigger code. MYLIB was part of the lib list when the trigger was created.) Is there a way to get around this?

    ReplyDelete
    Replies
    1. Having a quick think I can come up with two solutions (I am sure there are many more)

      Put the library name in a data area in each of the libraries. Then get the data from the data area and set on a flag, make a variable for the library/file name, open the file. Have logic in the program to check the library/file name field, it is not blank skip the logic I mentioned above.

      Or

      Use a data area with a value in it to denote which library the data comes from, get that in the trigger, and output to a field in the TRGOUTFILE. By all means you have one TRGOUTFILE, but you can separate the records within it by that flag file.

      I am sure you can come up with some alternatives too.

      Delete
  6. Thanks for the quick reply Simon. We are trying something similar by creating a function to call an existing procedure to get the value but having some issues with that as well. I guess if we were to use an RPG trigger, it would just read from the file that is available in the library list and we need not do all this.

    ReplyDelete
  7. Is there a way to get the program name that is actually making the update/insert/delete? Similar to 'current user'. And how can we get a list of such usable fields like, 'current_user', 'job_name' etc?

    ReplyDelete
    Replies
    1. I am not sure if you can the name of the program that causes the trigger to "fire" in SQL. It is only available in a RPG trigger as the trigger passes it to the trigger program.

      The CURRENT_USER is a special register and JOB_NAME is a built in global variable. If you click on the links you will see what others are available.

      Delete
  8. Hi Simon, thanks for the great article.
    I use IBM-Data Studio to apply SQL trigger but it doesn't work. please help me which tool I should use to apply SQL trigger. Thank you

    ReplyDelete
    Replies
    1. Try using "green screen" and add it manually using IBM i commands.

      Delete
  9. Can some one tell me if SQL Trigger is efficient when compared to traditional external trigger?

    ReplyDelete
  10. of course late to the party on this topic, but used it for the first time and it is so amazing! THank you!
    Question is possible to add what the called/calling program was that triggered the event? thank you
    robert wagner
    rwagner1968@sbcglobal.net

    ReplyDelete
    Replies
    1. Alas, for some reason it is not possible for the trigger to easily determine what program caused the trigger to execute.

      You'll have to go to the program stack and see if you can retrieve from there the program. This article might help.

      Delete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.