tailieunhanh - SQL Anywhere Studio 9- P6

Tham khảo tài liệu 'sql anywhere studio 9- p6', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả | 286 Chapter 8 Packaging FOR EACH ROW BEGIN MESSAGE STRING Trigger triud_t1 fired. END TO CONSOLE INSERT t1 VALUES 1 first row INSERT t1 VALUES 2 second row UPDATE t1 SET non_key_1 xxx DELETE t1 Here s what the output looks like because this trigger was defined as FOR EACH ROW it was fired once by each INSERT twice by the single UPDATE statement and twice by the DELETE for a total of six times Trigger triud_t1 fired. Trigger triud_t1 fired. Trigger triud_t1 fired. Trigger triud_t1 fired. Trigger triud_t1 fired. Trigger triud_t1 fired. Here s an example of the same trigger modified to execute different code depending on which kind of SQL operation fired the trigger CREATE TRIGGER triud_t1 BEFORE INSERT DELETE UPDATE ON t1 FOR EACH ROW BEGIN CASE WHEN INSERTING THEN MESSAGE Inserting t1. TO CONSOLE WHEN UPDATING THEN MESSAGE Updating t1. TO CONSOLE WHEN DELETING THEN MESSAGE Deleting t1. TO CONSOLE END CASE END INSERT t1 VALUES 1 first row INSERT t1 VALUES 2 second row UPDATE t1 SET non_key_1 xxx DELETE t1 Here s the output for more information about the special trigger predicates INSERTING DELETING and UPDATING see Section Trigger Predicates. Inserting t1. Inserting t1. Updating t1. Updating t1. Deleting t1. Deleting t1. Tip Use IF and CASE statements not IF and CASE expressions when referring to the special trigger predicates INSERTING DELETING and UPDATING in insert and delete triggers. That s because the REFERENCING OLD AS structure is undefined when an INSERT fires the trigger and the NEW AS row structure is undefined when a DELETE fires the trigger. The THEN and ELSE expressions in IF and CASE expressions are always parsed even if they are not evaluated and an undefined row structure will cause an error. The same is not true for IF and CASE statements not only are the THEN and ELSE branches not evaluated if they are not chosen they are not even parsed. And that s why IF and CASE statements work in a situation like this whereas IF and CASE expressions will