 |
| INSTEAD OF SQL Trigger (Adding/Deleting/Inserting record in the table through SQL View) |
Introduction
This article will explain the use of INSTEAD OF SQL triggers. There are some limitations present for INSTEAD OF SQL trigger.- Allowed on SQL View only
- Not allowed on DDS logical files
- Only one INSTEAD OF allowed for each SQL event
- WHEN clause can't be used
- FOR EACH STATEMENT can't be used
Below are the table structures that will be used in the example
Table 1
Create two tables with the same structure.
A R RTGPF9 A STUID 9P 0 A STUCLASS 2P 0 A STUSTATUS 10A A STUFEES 9P 0
Table 2
Create two tables with the same structure.
A R RTGPF9 A STUID 9P 0 A STUNAME 20A A STUDOB L A STUADDR 50A
View on Table 1 and Table 2
CREATE VIEW TRGPFVIEW AS
(SELECT A.STUID,
A.STUCLASS,
A.STUSTATUS,
A.STUFEES,
B.STUNAME,
B.STUDOB,
B.STUADDR
FROM TABLE1 A JOIN TABLE2 B
ON A.STUID = B.STUID);
Trigger Execution
The trigger will be added to TRGPFVIEW and it will get triggered when any record is updated in the View.
Trigger Condition
- When a record is inserted
- When a record is deleted
- When a record is changed except the STUID file field.
Trigger Action
The record will be added/deleted/inserted in the underlying tables through View.
Example: INSTEAD OF SQL Trigger
In this example, the INSTEADOFTRIGGERDEMO trigger gets created and if already present then that will be replaced by this trigger.
CREATE OR REPLACE TRIGGER INSTEADOFTRIGGERDEMO
INSTEAD OF
INSERT OR UPDATE OR DELETE ON TRGPF9VIEW
REFERENCING NEW ROW AS NEW OLD ROW AS OLD
FOR EACH ROW MODE DB2ROW
PROGRAM NAME TRIGGER13
BEGIN ATOMIC
IF INSERTING THEN
INSERT INTO TRGPF9 (STUID,STUCLASS,STUSTATUS,STUFEES)
VALUES(NEW.STUID, NEW.STUCLASS, NEW.STUSTATUS, NEW.STUFEES);
INSERT INTO TRGPF9A (STUID,STUNAME,STUDOB,STUADDR)
VALUES(NEW.STUID, NEW.STUNAME, NEW.STUDOB, NEW.STUADDR);
END IF;
IF UPDATING THEN
IF NEW.STUID <> OLD.STUID THEN
SIGNAL SQLSTATE 'ERROR'
SET MESSAGE_TEXT =
'STUID CAN NOT BE UPDATED';
END IF;
UPDATE TRGPF9 SET STUCLASS = NEW.STUCLASS,
STUSTATUS = NEW.STUSTATUS, STUFEES = NEW.STUFEES
WHERE STUID = OLD.STUID;
UPDATE TRGPF9A SET STUNAME = NEW.STUID, STUDOB = NEW.STUDOB,
STUADDR =NEW.STUADDR
WHERE STUID = OLD.STUID;
END IF;
IF DELETING THEN
DELETE FROM TRGPF9 WHERE STUID = OLD.STUID;
DELETE FROM TRGPF9A WHERE STUID = OLD.STUID;
END IF;
END; The above-shown trigger will be executed only when the record is added or deleted or updated in View.
Use of INSTEAD OF
INSTEAD OF INSERT OR UPDATE OR DELETE ON TRGPF9VIEW
The below statement is fetching the current row and the old row values
REFERENCING NEW ROW AS NEW OLD ROW AS OLD
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.
ATOMIC is used with BEGIN so that if any action failed then all the actions should be rolled back
BEGIN ATOMIC
Error handling code
IF NEW.STUID <> OLD.STUID THEN
SIGNAL SQLSTATE 'ERROR'
SET MESSAGE_TEXT =
'STUID CAN NOT BE UPDATED';
END IF; Condition executed when a record is inserted in the table
IF INSERTING THEN
INSERT INTO TRGPF9 (STUID,STUCLASS,STUSTATUS,STUFEES)
VALUES(NEW.STUID, NEW.STUCLASS, NEW.STUSTATUS, NEW.STUFEES);
INSERT INTO TRGPF9A (STUID,STUNAME,STUDOB,STUADDR)
VALUES(NEW.STUID, NEW.STUNAME, NEW.STUDOB, NEW.STUADDR);
END IF; Condition executed when a record is deleted in the table
IF DELETING THEN DELETE FROM TRGPF9 WHERE STUID = OLD.STUID; DELETE FROM TRGPF9A WHERE STUID = OLD.STUID; END IF;
Condition executed when a record is updated except the STUID file field
IF UPDATING THEN
IF NEW.STUID <> OLD.STUID THEN
SIGNAL SQLSTATE 'ERROR'
SET MESSAGE_TEXT =
'STUID CAN NOT BE UPDATED';
END IF;
UPDATE TRGPF9 SET STUCLASS = NEW.STUCLASS,
STUSTATUS = NEW.STUSTATUS, STUFEES = NEW.STUFEES
WHERE STUID = OLD.STUID;
UPDATE TRGPF9A SET STUNAME = NEW.STUID, STUDOB = NEW.STUDOB,
STUADDR =NEW.STUADDR
WHERE STUID = OLD.STUID;
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)
- Stored Procedure calling in SQL Trigger
- Transition Tables in SQL Triggers
- Error Handling in SQL Trigger Using Signalling
- Self Referencing SQL trigger
- 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