Simple SQL Trigger Example

Simple SQL Trigger Example
Simple SQL Trigger Example, sql trigger, example, trigger, sql, sql programming, db2, db2 for i sql, ibmi, as400, iseries
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

Post a Comment

© AS400 and SQL Tricks. All rights reserved. Developed by Jago Desain