 |
| Stored Procedure calling in SQL Trigger |
Introduction
This article will explain the execution of stored procedures through SQL Trigger.Below are the table structures that will be used in the example
Table
Create two tables with the same structure.
A R RTGPF1 A ROLLNO 9P 0 A NAME 10A A COLLEGE 50A A K ROLLNO
Stored Procedure
CREATE OR REPLACE PROCEDURE SQLTRPROC1(
IN P_ROLLNO DECIMAL(9,0),
IN P_NAME CHAR(10),
IN P_COLLEGE CHAR(50),
IN P_FLAG CHAR(1)
)
SPECIFIC SQLTRPROC1
BEGIN
IF P_FLAG = 'I' THEN
INSERT INTO TRGPF5(ROLLNO,NAME,COLLEGE) VALUES(P_ROLLNO, P_NAME ,P_COLLEGE);
END IF;
IF P_FLAG = 'D' THEN
DELETE FROM TRGPF5 WHERE ROLLNO = P_ROLLNO AND NAME = P_NAME AND
COLLEGE = P_COLLEGE;
END IF;
IF P_FLAG = 'U' THEN
UPDATE TRGPF5 SET NAME = P_NAME WHERE ROLLNO = P_ROLLNO AND
COLLEGE = P_COLLEGE;
END IF;
END
Trigger Execution
The trigger will be added to TABLE1 and it will get triggered when any record is added or deleted or updated in the table.
Trigger Condition
- When a record is inserted
- When a record is deleted
- When a record is changed with the value of the COLLEGE field from TABLE1 gets changed.
Trigger Action
Stored procedure SQLTRPROC1 gets called with different parameters
Example: Trigger With Stored Procedure
In this example, the CALLPROCFROMTRIGGER trigger gets created and if already present then that will be replaced by this trigger.
CREATE OR REPLACE TRIGGER CALLPROCFROMTRIGGER
AFTER INSERT OR DELETE OR UPDATE OF NAME ON TRGPF4
REFERENCING NEW ROW AS NEW OLD ROW AS OLD
FOR EACH ROW MODE DB2ROW
PROGRAM NAME TRIGGER9
BEGIN
DECLARE L_ROLLNO DECIMAL(9,0);
DECLARE L_NAME CHAR(10);
DECLARE L_COLLEGE CHAR(50);
DECLARE L_FLAG CHAR(1);
IF INSERTING THEN
SET L_FLAG = 'I';
SET L_ROLLNO = NEW.ROLLNO;
SET L_NAME = NEW.NAME;
SET L_COLLEGE = NEW.COLLEGE;
CALL SQLTRPROC1(L_ROLLNO, L_NAME, L_COLLEGE, L_FLAG);
END IF;
IF DELETING THEN
SET L_FLAG = 'D';
SET L_ROLLNO = OLD.ROLLNO;
SET L_NAME = OLD.NAME;
SET L_COLLEGE = OLD.COLLEGE;
CALL SQLTRPROC1(L_ROLLNO, L_NAME, L_COLLEGE, L_FLAG);
END IF;
IF UPDATING AND NEW.NAME <> OLD.NAME THEN
SET L_FLAG = 'U';
SET L_ROLLNO = OLD.ROLLNO;
SET L_NAME = NEW.NAME;
SET L_COLLEGE = OLD.COLLEGE;
CALL SQLTRPROC1(L_ROLLNO, L_NAME, L_COLLEGE, L_FLAG);
END IF;
END; The above-shown trigger will be executed only when the record is added or deleted or updated in TABLE1.
The below statement is fetching the current row and the old row values
REFERENCING NEW ROW AS NEW OLD ROW AS OLD
And, below is the condition that is comparing the old COLLEGE field value and the new COLLEGE field value.
WHEN(NEW.COLLEGE <> OLD.COLLEGE)
Work variable used in the trigger
DECLARE L_ROLLNO DECIMAL(9,0); DECLARE L_NAME CHAR(10); DECLARE L_COLLEGE CHAR(50); DECLARE L_FLAG CHAR(1);
BEGIN / END is used to execute the multiple events, also IF/END IF is a condition operator to execute the action when a condition is true.
Condition executed when a record is inserted in the table
IF INSERTING THEN SET L_FLAG = 'I'; SET L_ROLLNO = NEW.ROLLNO; SET L_NAME = NEW.NAME; SET L_COLLEGE = NEW.COLLEGE; CALL SQLTRPROC1(L_ROLLNO, L_NAME, L_COLLEGE, L_FLAG); END IF;
Condition executed when a record is deleted in the table
IF DELETING THEN SET L_FLAG = 'D'; SET L_ROLLNO = OLD.ROLLNO; SET L_NAME = OLD.NAME; SET L_COLLEGE = OLD.COLLEGE; CALL SQLTRPROC1(L_ROLLNO, L_NAME, L_COLLEGE, L_FLAG); END IF;
Condition executed when a record is updated and the College field is changed in the table
IF UPDATING AND NEW.NAME <> OLD.NAME THEN SET L_FLAG = 'U'; SET L_ROLLNO = OLD.ROLLNO; SET L_NAME = NEW.NAME; SET L_COLLEGE = OLD.COLLEGE; CALL SQLTRPROC1(L_ROLLNO, L_NAME, L_COLLEGE, L_FLAG); END IF;
IF / ENF IF blocks can have multiple actions in them.
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
- Simple SQL Trigger Example
- Conditional SQL Trigger Example
- OF Clause in SQL Trigger
- Multiple Event Execution Through SQL Trigger
- Change Row Before Inserting In Table (Before 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