Simple SQL Trigger Example

Simple SQL Trigger Example

Introduction

This article will explain a simple SQL trigger example.

Below are the table structures that will be used in the example

Table1 

A          R RTGPF1             
A            ROLLNO         9P 0
A            NAME          10A  
A            COLLEGE       50A  
A          K ROLLNO            

Table2

A          R RTGPF2              
A            STUCNT         9P 0 

Trigger Condition

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

Trigger Action

Increment of one in TABLE2 field on each row insert. 

Example#1: After Trigger

New_Student is the trigger name that will get triggered AFTER a record is inserted in TABLE1

CREATE TRIGGER NEW_STUDENT             
AFTER INSERT ON TRGPF1                 
FOR EACH ROW MODE DB2ROW               
UPDATE TRGPF2 SET STUCNT = STUCNT + 1; 

Example#2: After Trigger In Begin/End Tag

New_Student is the trigger name that will get triggered AFTER a record is inserted in TABLE1. But the trigger action is in BEGIN / END tag. Begin / End tag is used when multiple actions need to be performed by the trigger.

CREATE OR REPLACE TRIGGER NEW_STUDENT  
AFTER INSERT ON TRGPF1                 
FOR EACH ROW MODE DB2ROW               
BEGIN                                  
UPDATE TRGPF2 SET STUCNT = STUCNT + 1; 
END;                                   

Example#3 Before Trigger

New_Student is the trigger name that will get triggered BEFORE a record is inserted in TABLE1

CREATE OR REPLACE TRIGGER NEW_STUDENT    
BEFORE INSERT ON TRGPF1                  
FOR EACH ROW MODE DB2ROW                 
UPDATE TRGPF2 SET STUCNT = STUCNT + 1;   

Example#4: Before Trigger In Begin/End Tag

New_Student is the trigger name that will get triggered BEFORE a record is inserted in TABLE1. But the trigger action is in BEGIN / END tag. Begin / End tag is used when multiple actions need to be performed by the trigger.

CREATE OR REPLACE TRIGGER NEW_STUDENT   
BEFORE INSERT ON TRGPF1                 
FOR EACH ROW MODE DB2ROW                
BEGIN                                   
UPDATE TRGPF2 SET STUCNT = STUCNT + 1;  
END;                                    

Trigger object is a CLE program object which can be debugged.

Related Post

Read also :

• SQL Triggers concept in IBM i DB2
• Syntax for Create Trigger in IBM i AS400
• Conditional SQL Trigger Example
• OF Clause in SQL Trigger
• Multiple Event Execution Through SQL Trigger
• Change Row Before Inserting In Table (Before SQL Trigger)
• Stored Procedure calling in SQL Trigger
• Transition Tables in SQL Triggers
• Error Handling in SQL Trigger Using Signalling
• Self Referencing SQL trigger
• INSTEAD OF SQL Trigger (Adding / Deleting / Inserting record in the table through SQL View)
• Trigger Limitations and Program Attributes
• Effects On Trigger When New File Field Is Added
• SYSTRIGGERS Catalog Table for SQL Trigger
• SYSTRIGDEP Catalog Table for SQL Trigger
• SYSTRIGUPD Catalog Table for SQL Trigger
• SYSTRIGCOL Catalog Table for SQL Trigger