Multiple events supported in a single SQL trigger
Page 1 of 1
Multiple events supported in a single SQL trigger
As part of the IBM i 7.1 Technology Refresh 6 (TR6), now Multiple events supported in a single SQL trigger is allowed.
Native triggers already have the ability to handle INSERT, UPDATE and DELETE triggering events within a single program By allowing SQL trigger programs to handle multiple events, the management, installation and maintenance are improved
A multiple event trigger is a trigger that can handle INSERT, UPDATE, and DELETE triggering events within a single SQL trigger program. The ability to handle more than one event in a single program simplifies management of triggers.
In the body of the trigger, the new INSERTING, UPDATING, and DELETING predicates can be used to distinguish between the events that cause the trigger to fire. These predicates can be specified in control statements (like IF) or within any SQL statement that accepts a predicate (like SELECT or UPDATE).
Example...the following trigger:
1) Increments the number of employees each time a new person is hired
2) Decrements the number of employees each time an employee leaves the company
3) Raises an error when a salary increase is greater than ten percent
CREATE TRIGGER HIRED
AFTER INSERT OR DELETE OR UPDATE OF SALARY ON EMPLOYEE
REFERENCING NEW AS N OLD AS O FOR EACH ROW
BEGIN
IF INSERTING
THEN UPDATE COMPANY_STATS SET NBREMP = NBREMP + 1;
END IF;
IF DELETING
THEN UPDATE COMPANY_STATS SET NBREMP = NBREMP - 1;
END IF;
IF UPDATING AND (N.SALARY > 1.1 * O.SALARY)
THEN SIGNAL SQLSTATE '75000'
SET MESSAGE_TEXT = 'Salary increase > 10%'
END IF;
END
Native triggers already have the ability to handle INSERT, UPDATE and DELETE triggering events within a single program By allowing SQL trigger programs to handle multiple events, the management, installation and maintenance are improved
A multiple event trigger is a trigger that can handle INSERT, UPDATE, and DELETE triggering events within a single SQL trigger program. The ability to handle more than one event in a single program simplifies management of triggers.
In the body of the trigger, the new INSERTING, UPDATING, and DELETING predicates can be used to distinguish between the events that cause the trigger to fire. These predicates can be specified in control statements (like IF) or within any SQL statement that accepts a predicate (like SELECT or UPDATE).
Example...the following trigger:
1) Increments the number of employees each time a new person is hired
2) Decrements the number of employees each time an employee leaves the company
3) Raises an error when a salary increase is greater than ten percent
CREATE TRIGGER HIRED
AFTER INSERT OR DELETE OR UPDATE OF SALARY ON EMPLOYEE
REFERENCING NEW AS N OLD AS O FOR EACH ROW
BEGIN
IF INSERTING
THEN UPDATE COMPANY_STATS SET NBREMP = NBREMP + 1;
END IF;
IF DELETING
THEN UPDATE COMPANY_STATS SET NBREMP = NBREMP - 1;
END IF;
IF UPDATING AND (N.SALARY > 1.1 * O.SALARY)
THEN SIGNAL SQLSTATE '75000'
SET MESSAGE_TEXT = 'Salary increase > 10%'
END IF;
END
Similar topics
» Multiple Format Logical file and Multiple logical files
» Now V7R1 - Default parameter values are supported for both SQL and external stored procedures
» Multiple Format LF
» JOBQ attached to multiple subsystems.
» Seton some Indicators at single statement
» Now V7R1 - Default parameter values are supported for both SQL and external stored procedures
» Multiple Format LF
» JOBQ attached to multiple subsystems.
» Seton some Indicators at single statement
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|