|Self Referencing SQL trigger|
IntroductionThis article will explain the self-referencing SQL Trigger
Below are the table structures that will be used in the example
Create tables on which trigger is attached
A R RTGPF8 A EMPNO 9P 0 A EMPNAME 20A A EMPROLE 15A A MANAGERNO 9P 0 A SALARY 9P 0 A COMMISSION 9P 0 A DEPARTNO 9P 0
The trigger will be added to TABLE1 and it will get triggered when any record is updated in the table.
Comparison between the old and new commission
Table1 will be updated according to the condition
Example: Self Referencing SQL Trigger
In this example, the SELFREFERENCINGTRIGGER trigger gets created and if already present then that will be replaced by this trigger.
CREATE OR REPLACE TRIGGER SELFREFERENCINGTRIGGER AFTER UPDATE OF COMMISSION ON TRGPF8 REFERENCING NEW ROW AS NEW OLD ROW AS OLD FOR EACH ROW MODE DB2ROW PROGRAM NAME TRIGGER12 WHEN (NEW.COMMISSION <> OLD.COMMISSION AND NEW.EMPROLE <> 'MANAGER' AND NEW.EMPROLE <> 'DIRECTOR') BEGIN DECLARE L_MANAGERNO DECIMAL(9,0); DECLARE L_MANAGERCOMMISSION DECIMAL(9,0); SET L_MANAGERNO = (SELECT MANAGERNO FROM TRGPF8 WHERE EMPNO = NEW.EMPNO); IF L_MANAGERNO IS NOT NULL THEN SET L_MANAGERCOMMISSION = (NEW.COMMISSION - OLD.COMMISSION) ; UPDATE TRGPF8 SET COMMISSION = COMMISSION + L_MANAGERCOMMISSION WHERE EMPNO = L_MANAGERNO; END IF; END;
The above-shown trigger will be executed only when the record is updated in TABLE1.
The below statement will create the transition rows. New will be having the new row data and Old will be having the old row data.
REFERENCING NEW ROW AS NEW OLD ROW AS OLD
When condition to start the execution
WHEN (NEW.COMMISSION <> OLD.COMMISSION AND NEW.EMPROLE <> 'MANAGER' AND NEW.EMPROLE <> 'DIRECTOR')
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.
Selecting the manager and commission
SET L_MANAGERNO = (SELECT MANAGERNO FROM TRGPF8 WHERE EMPNO = NEW.EMPNO);
SET L_MANAGERCOMMISSION = (NEW.COMMISSION - OLD.COMMISSION) ;
Condition to update the commission to the manager in Table 1
IF L_MANAGERNO IS NOT NULL THEN
Table1 update statement
UPDATE TRGPF8 SET COMMISSION = COMMISSION + L_MANAGERCOMMISSION WHERE EMPNO = L_MANAGERNO;
IF / ENF IF blocks can have multiple actions in them.
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
- 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
- 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