Trigger if updating inserting

Although the trigger program can handle multiple events, all the events must be defined to be fired at the same time.

The SQL trigger must be defined as either BEFORE or AFTER for all of the events.

The next example shows how an SQL trigger might be used by a grocery store to track additions, deletions, and changes to a price look-up (PLU) code tracking table when there are changes to the produce that the store sells (the PLU is located on a small sticker on fruits and vegetables sold individually in grocery stores).

In this simple example, a different procedure is called based upon the event that fired the trigger.

For example, the remove physical file trigger (RMVPFTRG) command can be used to remove a multiple event trigger program from a file, but must remove all events that are handled by that trigger program.

For this reason, the TRGEVENT(*ALL) parameter must be used when removing such a trigger program.

One thing to note about the trigger predicates (INSERTING, DELETING, UPDATING) is that they can be used in control statements (similar to the prior examples) or within any SQL statement where a predicate (such as SELECT or UPDATE) can be specified, provided the statement containing the predicate is inside an SQL trigger.IF UPDATING THEN SET n.salary = n.salary * 1.1; -- Give starting salary based on education level.ELSEIF INSERTING THEN SET n.salary = CASE n.edlevel WHEN 18 THEN 50000 WHEN 16 THEN 40000 ELSE 25000 END; END IF; ENDIn this example, note that there is a case where the transition variables passed to the trigger will not be relevant (the old row image for an INSERT statement, as there is no 'old' row).The business logic to handle those events is inside the respective procedure. Multiple event trigger plu_track CREATE TRIGGER plu_track BEFORE INSERT OR UPDATE OR DELETE ON prod_plu REFERENCING NEW AS n OLD AS o FOR EACH ROW BEGIN IF INSERTING THEN CALL prod_plu_ins(n.plu); ELSEIF DELETING THEN CALL prod_plu_del(o.plu); ELSE CALL prod_plu_upd(n.plu); END IF; ENDAs you would expect with the integrated database on IBM i, the existing Control Language (CL) commands can be used with the program object created for a SQL multiple event trigger.These commands have common sense behavior when working with these programs.For cases where the row image passed in the transition variables is not relevant (the other case is the 'new' row for a DELETE), each transition variable in the irrelevant row contains the NULL value.Here is another example where all three events (INSERT, UPDATE, and DELETE) are handled in a single SQL trigger.That syntax allows for the specification of the events that can be handled by the trigger.Usage of the SQL multiple event trigger support requires that DB2 PTF Group SF99701 level 23 be applied on your system (see SF99701 Level 22 enhancements).Also, note that prior to this new support, there was also a QAQQINI option, SQL_MODIFIES_SQL_DATA, which allowed the trigger to contain SQL statements to perform committable work.Because the restriction that this option provided a work around for was removed with the new support, the QAQQINI option will be ignored but tolerated when set to '*NO' , as it is no longer relevant.

Leave a Reply

Your email address will not be published. Required fields are marked *

One thought on “trigger if updating inserting”