Category Archives: iSeries

How To Write an SQL Trigger for IBM i

In Product Development at our company we are considering a new approach to writing transactions functions (used for shopping cart applications, etc.) which suggests a use for database triggers.  We want to update the transaction header with the current timestamp whenever the transaction detail file is updated. I decided to try using an SQL trigger to do this.

There are essentially two types of i/OS triggers: External (a program – RPG, Java, C) and SQL (written in SQL).   You can read more about them at Stored Procedures, Triggers, and User-Defined Functions on DB2 Universal Database for iSeries. I chose to write an SQL trigger because it’s really simple to do and the Redbook suggests you probably get better performance than with an external trigger. Actually – I had to write 3 triggers – one each for INSERT, DELETE and UPDATE.

In my example code, table TRIGGERA is my transaction detail file. Whenever an update happens to it, I want to update the Change date in TRIGGERB. The change date field is called CHGDATE and is a TIMESTAMP SQL data type.

To write the trigger I started editing a new source member in SEU. You can use whatever source file you like. I used library FBLOGGS, source file QSQLSRC. Here’s the code:

-- Trigger example - when updating rows in a table       
-- Drop the existing trigger, if found                   
DROP TRIGGER FBLOGGS/SQLTRIGU;                            
-- Create the trigger over table TRIGGERA:               
 CREATE TRIGGER FBLOGGS/SQLTRIGU                          
  AFTER UPDATE  ON FBLOGGS/TRIGGERA                       
  REFERENCING NEW AS NEWROW                              
  FOR EACH ROW  MODE DB2ROW                              
    UPDATE FBLOGGS/TRIGGERB AS A SET CHGDATE = NOW()      
     WHERE A.SESSIONID = NEWROW.SESSIONID;               

Brief Explanation

  • CREATE TRIGGER – creates a program object named FBLOGGS/SQLTRIGU attached to the table.
  • AFTER UPDATE – means any code in the trigger happens after (not before) an update to the table in question
  • ON FBLOGGS/TRIGGERA – name of the table that will have the trigger attached to it. So, any time an update happens to any row in TRIGGERA, this trigger will run.
  • FOR EACH ROW – the other option is FOR EACH STATEMENT, which means the trigger happens on any SQL statement touching the table (like ALTER, for example). FOR EACH ROW means the trigger happens on any affected row only.
  • REFERENCING NEW AS NEWROW – this lets you refer to the ‘after image’ of variables in an affected row. The other option is REFERENCING OLD AS OLDROW (note: NEWROW and OLDROW are arbitrary names. Call them what you want). For example, if you change ITEMPRICE in a row, you could reference the new value with: NEWROW.ITEMPRICE. In this case, I want to match the sessionid column in table TRIGGERA to the corresponding session id in TRIGGERB, the session header file.
  • FOR EACH ROW MODE DB2ROW – means for each affected row (not every row in the table- only those affected by the UPDATE operation). MODE DB2ROW means perform the trigger once per affected row. The other option is MODE DB2SQL which activates the trigger after all database updates are complete. DB2ROW is more efficient.
  • UPDATE FBLOGGS/TRIGGERB AS A SET CHGDATE = NOW()
    WHERE A.SESSIONID = NEWROW.SESSIONID; – this is the cool part. You can use almost any valid SQL you like in your trigger (with some logical limitations). You can have more than one line of code in here too. The ; marks the end of a complete line. The Redbook has more examples. In this case, I’m updating the row in the session header table TRIGGERB that matches the session id of the affected row in TRIGGERA. The UPDATE statement assigns the current date and time to my column CHGDATE (recall I defined this as a TIMESTAMP field).
  • To compile the trigger you run this command from a standard command line:

    RUNSQLSTM SRCFILE(FBLOGGS/QSQLSRC) SRCMBR(SQLTRIGU)

    The first time you run it you will need to comment out the DROP TRIGGER statement in the source. But subsequent times you need it uncommented so it deletes the trigger prior to recreating it. Or you can use
    STRSQL and type the DROP TRIGGER statement on the fly.

    I repeated essentially the same code for the INSERT and DELETE triggers on TRIGGERA, the only difference being that for the DELETE trigger I used REFERENCING OLD AS OLDROW because there is no NEW.

    That’s it!

Articles I’ve Written on IBM i (iSeries) Technology Subjects

I’ve written a few articles in recent years that have been published by MC Press Online, relating to IBM i topics. If you don’t know what the IBM i is (or an AS/400, iSeries or System i), you can stop reading! Here are two lists of links to those articles. The first list is general education. The second is product-specific, related to products marketed by my company. (Articles are ordered by most recent first)

Educational Articles

Product-Specific Articles