|OF Clause in SQL Trigger|
IntroductionThis article will explain the working of OF clause in the SQL trigger and how to execute the trigger depending on the column and on complete row change.
Below are the table structures that will be used in the example
A R RTGPF1 A ROLLNO 9P 0 A NAME 10A A COLLEGE 50A A K ROLLNO
A R RTGPF2 A STUCNT 9P 0
The trigger will be added to TABLE1 and it will get triggered when any record is updated in the table.
When the value of the COLLEGE field from TABLE1 gets changed.
Only when the trigger condition is true, only the STUCNT field from TABLE2 gets increment by 1.
Example: OF Clause Trigger
In this example, the UPDATE_STUDENT_COLLEGE trigger gets created and if already present then that will be replaced by this trigger.
CREATE OR REPLACE TRIGGER UPDATE_STUDENT_COLLEGE AFTER UPDATE OF COLLEGE ON TRGPF1 REFERENCING NEW ROW AS NEW OLD ROW AS OLD FOR EACH ROW MODE DB2ROW WHEN(NEW.COLLEGE <> OLD.COLLEGE) BEGIN UPDATE TRGPF2 SET STUCNT = STUCNT + 1; END;
The above-shown trigger will be executed only when the COLLEGE file field gets updated due to OF clause on the file field COLLEGE/. It will not get executed if any other field from the file gets updated.
AFTER UPDATE OF COLLEGE ON TRGPF1
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)
Trigger object is a CLE program object which can be debugged.
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
- 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