SQL Triggers

Introduction

Why do we need Triggers?

• Monitors Database Activity
• Maintain Database Consistency and Enforce business rules
• Concept of Trigger
• Insert, Delete, Update on Table/View
• Called By Database
• Allow granularity at the column level
• UDF/stored procedure

Simple SQL Triggers Example

• Trigger Condition
• Trigger Action
• Example#1: After Trigger
• Example#2 Before Trigger
• Example#3 Multi condition Triggers
• Example#4 Conditional Triggers
• Example#5 Trigger Using Stored Procedure

How to create SQL triggers

How to see SQL triggers

How to remove SQL triggers

Introduction

When an add, remove, or update operation is made to a table, triggers offer a mechanism to keep an eye on, adjust, and manage the tables

When there is significant interdependence between the tables or when a certain action has to be taken in response to a table change, it is highly beneficial.

Why do we need Triggers?

• Monitors Database Activity

  • Only when there is an addition, deletion, or insert made to the trigger-associated table does the trigger become active.

• Maintain Database Consistency and Enforce business rules

  • When two or more tables are co-linked to one another, any changes made to the trigger-associated table will cause all related tables to synchronize with one another.

    Additionally, the trigger’s action will modify the related table to achieve this synchronization.

• Concept of Trigger

  • A certain set of activities are carried out upon the trigger’s execution.

• Insert, Delete, Update on Table/View

  • On insert, remove, or upon adding a record to the table or view, a trigger can be inserted.

• Called By Database

  • The database (database management system) itself calls the specific activities when the trigger performs them.

• Execute actions that are not database-related.

  • The trigger can also be used for non-database tasks, such as emailing or sending messages.

• Allow granularity at the column level

  • Instead of adding a trigger to the entire table, you may apply it to a single column.

• UDF/stored procedure

  • When the triggering process is carried out, SQL triggers are called by the database management system, which may then run UDF or SQL stored procedures.

Simple SQL Triggers Example

The table structures that will be utilized in the example are listed below.

Table1- TGRPF1

    A          R RTGRPF1
    A            CUSTID           9P 0
    A            NAME           10A
    A            DEPARTMENT     50A
    A          K CUSTID
    

    Table2 – TGRPF2

A          R RTGRPF2
A            TRGTIME         Z         COLHDG('Trigger' 'time')
A            JOBNAME       28A         COLHDG('Job' 'name')
A            TRGTYPE        2A         COLHDG('Trigger' 'type')

• Trigger Condition

  • The trigger will be added to TGRPF1 and it will get triggered when any record is inserted in the table.

• Trigger Action

  • Newly Added information will be logged in TGRPF2 field on each row insert.

• Example#1: After Trigger

  • The trigger name that will be activated upon the insertion of a record in TGRPF1 is New Customer.

    CREATE TRIGGER NEW_CUSTOMER
    AFTER INSERT ON TGRPF1
    FOR EACH ROW MODE DB2ROW
    INSERT INTO RTGRPF2
                   VALUES(CURRENT TIMESTAMP,
                          JOB_NAME,
                          'I') ;
    
                    

• Example#2 Before Trigger

  • The trigger name that will be activated Before to a record being placed into TGRPF1 is New Customer.

                        CREATE OR REPLACE TRIGGER NEW_CUSTOMER
                        BEFORE INSERT ON TRGPF1
                        FOR EACH ROW MODE DB2ROW
                        INSERT INTO RTGRPF2
                                       VALUES (CURRENT TIMESTAMP,
                                              JOB_NAME,
                                              'I');
                    

• Example#3 Multi condition Triggers

  • 01 CREATE OR REPLACE TRIGGER NEW_CUSTOMER
    02 AFTER INSERT OR DELETE OR UPDATE ON TRGPF1
    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 TRGPF2
                   VALUES (CURRENT TIMESTAMP,
                          JOB_NAME,
                          'I' ) ;
    09    END IF;
    
    10    IF DELETING THEN
    11      INSERT INTO TRGPF2
                   VALUES(CURRENT TIMESTAMP,
                          JOB_NAME,
                          'D') ;
    12    END IF ;
    
    13    IF UPDATING THEN
    14      SET TSTAMP = CURRENT TIMESTAMP ;
    
    15      INSERT INTO TRGPF2
                   VALUES(TSTAMP,
                          JOB_NAME,
                          'U') ;
    
    16    END IF ;
    17  END
    
                    

  Line 1: One excellent feature added to IBM i 7.2 and subsequent 7.1 TRs is CREATE OR REPLACE. It keeps me from having to let go of the trigger before making the updated version. Previously, I would have just had CREATE. The remainder of the line specifies that NEW_CUSTOMER will be the name of my trigger.

  Line 2: AFTER specifies that the trigger will take place subsequent to the database activity for updates, deletes, and inserts into my library’s TRGPF2 file.

  Line 3: This line indicates that all fields/columns in the new row/record will have the prefix “N” and all fields in the previous row/record will have the prefix “O”.

  Line 4: DB2ROW indicates that each row/record action will be followed by the trigger’s execution. Only once all row operations are finished will the alternative, DB2SQL, run.

  Line 5: Indicates where the trigger code starts.

  Line 6: A timestamp (or variable TSTAMP) is defined. This is what we’ll use to insert the update rows.

  Lines 7-9: In the event that the action involved an insert, a row is added to the trigger output file. Only the updated values from the file are utilized because this is an insert.

  Lines 10–12: This portion of the trigger, which puts the previous values into the output file, is executed when a deletion is carried out.

  Lines 13–17: We would like both the old and new values for an update. Additionally, we need the timestamp in both rows to match. The timestamps in the two rows would have been different if we had used CURRENT TIMESTAMP. We can ensure that the timestamp value in both rows is the identical by relocating CURRENT TIMESTAMP to the variable specified on line 6.

  Line 18: The code for trigger ends here, matching the BEGIN on line 5.

• Example#4 Conditional Triggers

  CREATE OR REPLACE TRIGGER NEW_CUSTOMER
  AFTER UPDATE ON TRGPF1
  REFERENCING NEW ROW AS NEW OLD ROW AS OLD
  FOR EACH ROW MODE DB2ROW
  WHEN(NEW.CUSTID <> OLD.CUSTID)
  BEGIN
  INSERT INTO TRGPF2
                 VALUES (CURRENT TIMESTAMP,
                        JOB_NAME,
                        'U' ) ;
  END;
  
          

  The line below retrieves the values from both the current and previous rows.

  REFERENCING NEW ROW AS NEW OLD ROW AS OLD 

  Additionally, the condition that compares the value of the old and new College fields is shown below.

  WHEN(NEW.COLLEGE <> OLD.COLLEGE)

• Example#5 Trigger Using Stored Procedure
  

          CREATE OR REPLACE PROCEDURE SQLTRGPROC(
          IN P_CUSTID  DECIMAL(9,0),
          IN P_FLAG  CHAR(1)
           )
  SPECIFIC SQLTRGPROC
  BEGIN
   IF P_FLAG = 'I' THEN
    INSERT INTO TRGPF1(CUSTID) VALUES(P_CUSTID);
   END IF;
  
   IF P_FLAG = 'D' THEN
    DELETE FROM TRGPF1 WHERE CUSTID = P_CUSTID;
   END IF;
  
   IF P_FLAG = 'U' THEN
    UPDATE TRGPF1 SET  CUSTID = P_CUSTID WHERE CUSTID = P_CUSTID;
   END IF;
  END
  
      

  The CALLPROCFROMTRIGGER trigger is generated in this example, and if it already exists, it is replaced with this trigger.

  CREATE OR REPLACE TRIGGER CALLPROCFROMTRIGGER
  AFTER INSERT OR DELETE OR UPDATE OF NAME ON TRGPF1
  REFERENCING NEW ROW AS NEW OLD ROW AS OLD
  FOR EACH ROW MODE DB2ROW
  PROGRAM NAME TRIGGER9
  BEGIN
  DECLARE L_CUSTID DECIMAL(9,0);
  DECLARE L_FLAG CHAR(1);
   IF INSERTING THEN
      SET L_FLAG = 'I';
      SET L_CUSTID = NEW.CUSTID;
     CALL SQLTRGPROC1(L_CUSTID, L_FLAG);
  END IF;
  
  IF DELETING THEN
      SET L_FLAG = 'D';
      SET L_CUSTID = NEW.CUSTID;
     CALL SQLTRGPROC1(L_CUSTID, L_FLAG);
  END IF;
  
  IF UPDATING AND NEW.CUSTID <> OLD.CUSTID THEN
     SET L_FLAG = 'U';
     SET L_CUSTID = OLD.CUSTID;
     CALL SQLTRPROC1(L_CUSTID, L_FLAG);
   END IF;
  END;
  
      

How to create SQL triggers

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

OBJECT      TYPE        ATTRIBUTE   TEXT
    TEST_00001  *PGM        CLE         SQL TRIGGER TEST_TESTFILE
    

How to see SQL triggers

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

DSPFD FILE(TESTFILE) TYPE(*TRG)

Or we 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:

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
    

How to remove SQL triggers

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

RMVPFTRG FILE(PGMSDHTST3/WA042P)

Or we could use DROP TRIGGER:

DROP TRIGGER TRG_TESTFILE

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