What if your database could audit itself, validate data, and ensure security—all in real time?

That’s the magic of SQL triggers.

Introduction to Database Triggers 

A trigger is a predefined event that runs automatically whenever a specified action is performed on a table.

There are two types of triggers:

Type 1: High-level programming language (HLL) triggers written in RPG or another IBM i-native language. RPG triggers can be added using the ADDPFTRG (Add Physical File Trigger) CL command.

Type 2: SQL triggers. Triggers are associated with tables; those actions are the I/O operations that change the table:

INSERT, UPDATE and DELETE operations.

 In an RPG program, a remote ODBC call, or a DFU call, triggers are linked to the table, so they’ll always be activated.

Executing Basic SQL Triggers

Example 1: Creating a simple SQL trigger

Please refer to the below example for the SQL trigger.

Step 1: Create Table

CREATE OR REPLACE TABLE <Schema>.STORES(                    

STORE_ID INT GENERATED BY DEFAULT AS IDENTITY NOT NULL,

STORE_NAME VARCHAR(150) NOT NULL,                     

ADDRESS_LINE_1 VARCHAR(255) NOT NULL,              

ADDRESS_LINE_2 VARCHAR(100),                       

CITY_ID INT NOT NULL,                                

STATE_ID INT NOT NULL,                                        

ZIP_CODE VARCHAR(6),                               

STORE_DAILY_AMOUNT DECIMAL(15) NOT NULL DEFAULT 0 ,   

STORE_START_AMOUNT DECIMAL(15) NOT NULL DEFAULT 0,    

PRIMARY KEY (STORE_ID)                                      

)                                                     

TABLE STORES IN <schema> CREATED BUT WAS NOT JOURNALED.

Step 2: Add SQL Trigger

The purpose of this trigger is to set column CITY_ID to value 1000 after insert operation.

CREATE OR REPLACE TRIGGER <schema>. stores           

AFTER INSERT ON <Schema>.STORES                     

   BEGIN                                            

            UPDATE <Schema>.STORES SET CITY_ID = 1000;  

  END     

Trigger STORES created in <schema>.                 

Step 3: Test Newly Created Trigger

INSERT INTO <SCHEMA>/STORES (CITY_ID, STORE_NAME, ADDRESS_LINE_1,

ADDRESS_LINE_2, STATE_ID, ZIP_CODE) VALUES(99, ‘Test store 1’,   

‘Address line 1’, ‘Address line 2’, ‘1005’, ‘400056’)            

1 row inserted in STORES in <SCHEMA>.    

We can see the city ID is 1000. Testing is successful!

Using SQL Trigger for data validation

Example 1: Creating an SQL trigger for data validation

Step 1: Create Table

Note:

• OLD1 and NEW1 are used as a prefix, as the column name is the same.
• FOR EACH STATEMENT A trigger is only executed once per statement. It is executed even if the statement did not write any rows.

CREATE OR REPLACE TRIGGER <SCHEMA>.STORES NO CASCADE        

BEFORE UPDATE ON <SCHEMA>.STORES                                 

REFERENCING NEW ROW AS NEW1 OLD ROW AS OLD1                              

FOR EACH ROW MODE DB2ROW                                     

WHEN (OLD1.STORE_DAILY_AMOUNT <> NEW1.STORE_DAILY_AMOUNT)    

 BEGIN                                                          

 IF OLD1.STORE_DAILY_AMOUNT < NEW1.STORE_DAILY_AMOUNT * 2 THEN           

 SIGNAL SQLSTATE ‘75001’                                       

 Set MESSAGE_TEXT =’This daily amount is not valid because it is

 less than required limit’;                                     

 END IF;                                                        

 END                                                              

Step 2: Test the Newly Created Trigger

UPDATE <SCHEMA>.STORES SET STORE_DAILY_AMOUNT =5000

WHERE STORE_ID = 13  

Take F1. We can see validation is working as expected!

Using INSTEAD OF Triggers to strengthen views.

Using SQL views can hide database complexity. Using views, we can join many tables and combine columns from different tables into a single view, so that the data will appear like it is from the same table. Up until this point, we used views to display information only.

• INSTEAD OF triggers describe how to perform insert, update, and delete operations against complex views.
• INSTEAD OF triggers allow applications to use a view as the sole interface for all SQL operations (insert, delete, update, and select). From the perspective of the application, this happens transparently, as it perceives that all operations are performed against the view.
• Only one INSTEAD OF trigger is allowed for each kind of operation on a given subject view.

Example 3: Creating an INSTEAD OF Trigger

Step 1: Create Table

CREATE OR REPLACE TABLE <SCHEMA>.INVOICES(                        

STORE_ID INT NOT NULL,                                        

INVOICE_ID INT NOT NULL,                                    

DOC_TYPE   VARCHAR(1) NOT NULL DEFAULT ‘1’,                 

INVOICE_AMOUNT DECIMAL(15) NOT NULL DEFAULT 0,               

INVOICE_DATE   DATE             NOT NULL DEFAULT CURRENT_DATE,

WRITEOFF_AMOUNT DECIMAL(15) NOT NULL DEFAULT 0,            

CUST_FIRST_NAME  VARCHAR(30) NOT NULL DEFAULT ‘ ‘,            

CUST_LAST_NAME  VARCHAR(30) NOT NULL DEFAULT ‘ ‘,             

PRIMARY KEY (STORE_ID,INVOICE_ID) )     

Step 2: Create a Regular View

CREATE OR REPLACE VIEW <SCHEMA>.VIEW_STORE_INV  AS                

            SELECT STORES.STORE_ID,                          

            STORES.STORE_NAME,                       

            INVOICES.INVOICE_ID INT,               

            INVOICES.DOC_TYPE    ,        

           INVOICES.INVOICE_AMOUNT,           

           INVOICES.INVOICE_DATE  ,              

           INVOICES.WRITEOFF_AMOUNT,          

           INVOICES.CUST_FIRST_NAME,          

           INVOICES.CUST_LAST_NAME            

            FROM INVOICES INNER JOIN STORES  

            ON INVOICES.STORE_ID = STORES.STORE_ID

Step 3: Create INSTEAD OF Trigger

Note: OLD1 and NEW1 are used as prefixes, as the column name is the same.

CREATE OR REPLACE TRIGGER <SCHEMA>.TRG_UPDATE_VIEW_STORE_INV

INSTEAD OF UPDATE ON <SCHEMA>.VIEW_STORE_INV                 

REFERENCING OLD AS OLD1 NEW AS NEW1                            

FOR EACH ROW MODE DB2SQL                                         

 BEGIN                                                     

   DECLARE WK_STORE_ID INT;                            

   SELECT STORE_ID                                      

   INTO WK_STORE_ID                                               

   FROM <SCHEMA>.STORES                                   

   WHERE STORES.STORE_ID = NEW1.STORE_ID;                     

   UPDATE <SCHEMA>.STORES                                           

   SET STORE_NAME = NEW1.STORE_NAME                              

   WHERE STORES.STORE_ID = WK_STORE_ID;                         

 END

Step 4: Test Newly Created Trigger

Update data using View

We can see that the Store Name has been updated successfully!

Advantages of SQL

• SQL’s simpler and more powerful language makes trigger development easier and faster.
• SQL triggers’ activation is more precise: you can, for instance, activate the trigger only if a certain column has a certain value. This type of operation is not possible with external triggers.
• It’s much easier to write an SQL trigger to handle operations that modify more than one row in the target table.
• It takes less time to test SQL trigger V/s RPG triggers.
• With SQL, you can create INSTEAD OF triggers.
• SQL’s syntax makes it easier to write and configure one trigger to process several events. 

Conclusion

SQL triggers are a powerful feature that can automate many aspects of database management, from enforcing business rules to auditing and maintaining data integrity. They can help you enforce complex database requirements at the database level, save time, and improve data quality. The performance and maintainability of these tools, however, are trade-offs. Using SQL triggers effectively can enhance your database applications if you follow best practices.

When you’re first starting with SQL triggers, set up simple scenarios in a test database and gradually explore more advanced scenarios.