Tài liệu SQL Anywhere Studio 9- P6 docx

FOR EACH ROW

BEGIN

MESSAGE STRING ( 'Trigger triud_t1 fired.' ) TO CONSOLE;

END;

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 3.12.7, “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 refer￾ring 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 fail.

286 Chapter 8: Packaging

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

An UPDATE that specifies new column values that are the same as old column

values will still fire a before row UPDATE trigger; the same is true of an

UPDATE that refers to a column named in the UPDATE OF clause but doesn’t

specify a different value. Also, the row structures contain all the column values

from the old and new rows, even columns excluded from an UPDATE OF list,

and all those other columns can be named in the WHEN clause. Here is an

example of a before row trigger with both an UPDATE OF clause and a WHEN

clause, plus code that changes the final values for all the non-key columns:

CREATE TABLE t1 (

key_1 INTEGER NOT NULL PRIMARY KEY,

non_key_1 VARCHAR ( 100 ) NOT NULL,

non_key_2 VARCHAR ( 100 ) NOT NULL );

CREATE TRIGGER triud_t1

BEFORE UPDATE OF non_key_1

ON t1

REFERENCING OLD AS old_t1

NEW AS new_t1

FOR EACH ROW

WHEN ( old_t1.non_key_2 = 'xxx' )

BEGIN

MESSAGE 'Updating t1...' TO CONSOLE;

MESSAGE STRING ( ' Old row: ',

old_t1.key_1, ', ',

old_t1.non_key_1, ', ',

old_t1.non_key_2 ) TO CONSOLE;

MESSAGE STRING ( ' New row: ',

new_t1.key_1, ', ',

new_t1.non_key_1, ', ',

new_t1.non_key_2 ) TO CONSOLE;

SET new_t1.non_key_1 = 'ccc';

SET new_t1.non_key_2 = 'ddd';

MESSAGE STRING ( ' Final row: ',

new_t1.key_1, ', ',

new_t1.non_key_1, ', ',

new_t1.non_key_2 ) TO CONSOLE;

END;

INSERT t1 VALUES ( 1, 'ppp', 'aaa' );

INSERT t1 VALUES ( 2, 'qqq', 'bbb' );

UPDATE t1 SET non_key_2 = 'xxx' WHERE key_1 = 1;

UPDATE t1 SET non_key_1 = 'zzz' WHERE key_1 = 2;

UPDATE t1 SET non_key_1 = 'yyy';

SELECT * FROM t1 ORDER BY key_1;

The first UPDATE above doesn’t fire the trigger because the SET clause speci￾fies a column that isn’t named in the trigger’s UPDATE OF clause. The second

UPDATE doesn’t fire the trigger because the old value of t1.non_key_2 is 'bbb'

and that doesn’t match the trigger’s WHEN clause. The third update changes

both rows in t1, but only the update to the first row fires the trigger because

that’s the only update that matches both the UPDATE OF and WHEN clauses.

The code inside the trigger then changes both non-key column values and dis￾plays all three versions of the row: old, new, and final. Here’s what that display

looks like:

Updating t1...

Old row: 1, ppp, xxx

Chapter 8: Packaging 287

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

New row: 1, yyy, xxx

Final row: 1, ccc, ddd

Here’s what the final SELECT shows after all the updates are complete:

key_1 non_key_1 non_key_2

===== ========= =========

1 'ccc' 'ddd'

2 'yyy' 'bbb'

Tip: The before row form of CREATE TRIGGER is very popular because it is the

easiest to code. For example, it is possible to modify the new row in a before row

UPDATE trigger without worrying about endless recursion. Updates made in the

other two kinds of trigger must be made directly to the associated table rather

than a row structure; that nested update may recursively fire the same trigger,

requiring extra code to make sure the recursion doesn’t run away.

The syntax for the second form of trigger differs only by one word: The key￾word AFTER specifies that this trigger is fired after the row operation is

complete:

<create_after_row_trigger> ::= CREATE TRIGGER <trigger_name>

AFTER

<fired_by>

[ ORDER <order_number> ]

ON [ <owner_name> "." ] <table_name>

[ <referencing_as_structures> ]

FOR EACH ROW

[ WHEN "(" <boolean_expression> ")" ]

<begin_block>

After row triggers work almost the same way as before row triggers, with three

differences:

An after row UPDATE trigger is not fired for a row where no column val￾ues actually changed in value.

An after row UPDATE OF trigger is not fired for a row where none of the

columns named in the UPDATE OF clause actually changed in value.

It is not possible to modify the values in the REFERENCING NEW AS

structure because it’s too late, the row operation has already been

performed.

The syntax for the third form of trigger uses the keywords AFTER and FOR

EACH STATEMENT to define a trigger that is fired once after the triggering

INSERT, UPDATE, or DELETE statement is finished operating on all the rows

it affects:

<create_after_statement_trigger> ::= CREATE TRIGGER <trigger_name>

AFTER

<fired_by>

[ ORDER <order_number> ]

ON [ <owner_name> "." ] <table_name>

[ <referencing_as_tables> ]

[ FOR EACH STATEMENT ]

<begin_block>

<referencing_as_tables> ::= REFERENCING { <as_table> } <as_table>

<as_table> ::= OLD AS <as_table_name>

| NEW AS <as_table_name>

<as_table_name> ::= <identifier> naming a read-only temporary table

288 Chapter 8: Packaging

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Here’s a list of characteristics that make an after statement trigger different from

an after row trigger:

The REFERENCING OLD AS and NEW AS clauses define multi-row

temporary tables as opposed to single-row structures.

The REFERENCING OLD AS temporary table contains the rows affected

by the statement that caused the trigger to fire, as they existed in the data￾base before the triggering statement executed.

The REFERENCING NEW AS temporary table contains the rows affected

by the statement that caused the trigger to fire, as they exist in the database

after the triggering statement finished but before the trigger itself began

executing.

The REFERENCING NEW AS temporary table itself is read-only,

although it can be used in a join in an UPDATE statement inside the trigger.

The WHEN clause is not allowed in an after statement trigger.

The REFERENCING OLD AS and NEW AS temporary tables can be

empty if the triggering statement doesn’t actually affect any rows in the

table. An after statement trigger is always fired if the other criteria are met;

e.g., an UPDATE OF trigger is fired if the UPDATE statement contains a

SET clause that specifies at least one of the columns named in the trigger’s

UPDATE OF clause, even if the UPDATE statement’s WHERE clause

didn’t match any rows.

The REFERENCING OLD AS and NEW AS temporary tables in an after

statement UPDATE or UPDATE OF trigger won’t contain any rows where

the column values didn’t actually change. This means the temporary tables

can be empty or can contain fewer rows than the UPDATE statement’s

WHERE clause matched.

The rules for when an after statement trigger is fired, and if so, how many rows

appear in the REFERENCING OLD AS and NEW AS temporary tables, are

rather complex. Following are two tables that summarize the rules, and include

the before row and after row triggers as well. Each table entry answers two

questions: “Is this trigger fired, yes or no?” and “For an after statement trigger,

how many rows appear in the REFERENCING temporary tables?” For simplic￾ity, the tables assume an UPDATE statement that matches either one or zero

rows.

The first table is for an ordinary UPDATE trigger, one that doesn’t use the

special UPDATE OF clause. Whether or not this class of trigger is fired depends

on whether or not the WHERE clause matches any rows, and whether or not the

SET clause specifies any column values that are different.

UPDATE Trigger Fired?

WHERE clause matches row: yes yes no

SET clause specifies value: different same n/a

========== =========== ===========

BEFORE UPDATE ROW yes yes no

AFTER UPDATE ROW yes no no

AFTER UPDATE STATEMENT yes, 1 row yes, 0 rows yes, 0 rows

The second table is for a trigger with an UPDATE OF clause. Whether or not

this class of trigger is fired depends on whether or not the WHERE clause

matches any rows, whether or not the SET clause names any columns also

Chapter 8: Packaging 289

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

named in the UPDATE OF clause, and whether or not the SET clause specifies

any column values that are different.

UPDATE OF Trigger Fired?

WHERE clause matches row: yes yes yes no no

SET clause matches UPDATE OF: yes yes no yes no

SET clause specifies value: different same - - -

========== =========== ==== =========== ====

BEFORE UPDATE OF ROW yes yes no no no

AFTER UPDATE OF ROW yes no no no no

AFTER UPDATE OF STATEMENT yes, 1 row yes, 0 rows no yes, 0 rows no

Following is an example of an after statement trigger that is fired by an

UPDATE statement that matches two rows. The trigger BEGIN block includes

cursor FOR loops and MESSAGE statements to display the entire contents of

the REFERENCING OLD AS and NEW AS temporary tables.

This trigger also contains an UPDATE statement that overrides the changes

made by the triggering UPDATE statement by directly updating the table again.

This will fire the trigger recursively, so the trigger takes the following two steps

to prevent runaway recursion. First, the UPDATE statement inside the trigger

includes a WHERE clause that won’t match any rows that have already been

changed by a previous trigger execution. Second, the first statement in the trig￾ger BEGIN block is an IF that checks how many rows are in the

REFERENCING OLD AS temporary table. If that temporary table is empty

(which will happen if it is fired by an UPDATE that doesn’t match any rows),

the LEAVE statement terminates the trigger before it has a chance to fire itself

again.

CREATE TABLE t1 (

key_1 INTEGER NOT NULL PRIMARY KEY,

non_key_1 VARCHAR ( 100 ) NOT NULL,

non_key_2 VARCHAR ( 100 ) NOT NULL );

CREATE TRIGGER tru_t1

AFTER UPDATE OF non_key_1

ON t1

REFERENCING OLD AS old_t1

NEW AS new_t1

FOR EACH STATEMENT

this_trigger:

BEGIN

MESSAGE 'Updating t1...' TO CONSOLE;

IF NOT EXISTS ( SELECT * FROM old_t1 ) THEN

MESSAGE '...no rows updated.' TO CONSOLE;

LEAVE this_trigger;

END IF;

FOR f1 AS c1 NO SCROLL CURSOR FOR

SELECT old_t1.key_1 AS @key_1,

old_t1.non_key_1 AS @non_key_1,

old_t1.non_key_2 AS @non_key_2

FROM old_t1

ORDER BY old_t1.key_1

DO

MESSAGE STRING ( ' Old row: ',

@key_1, ', ',

@non_key_1, ', ',

@non_key_2 ) TO CONSOLE;

290 Chapter 8: Packaging

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.