Trigger Learning and Finishing

1. Basic Guidelines

When creating trigger, follow these guidelines

A. The name of the trigger, the character length of the CREATE trigger statement text cannot exceed 32KB

B. Name of the topic table

C. Time of trigger activation (after execution before and after the modification operation)

D. Trigger events (INSERT, UPDATE, DELETE)

E. Value of the old conversion variable (if present)

F. New conversion Variable value (if present)

G. Old conversion table values (if present)

H. New conversion table value (if present)

I. Granularity (fow each row or for each statement)

J. Trigger actions (including trigger conditions and trigger statements)

K. When the trigger event is update, the trigger column list must be declared if the trigger should be triggered only if a particular column is specified in the UPDATE statement

L. Triggers do not accept parameters

M.. There can be up to 12 triggers on a table, but at the same time, the same event, the same type of trigger can have only one. And there can be no contradiction between the triggers.

N. The more triggers on a table, the greater the performance impact on the DML operations on the table

O. The execution portion of triggers can only be used with DML statements (SELECT, INSERT, UPDATE, DELETE)

P. A trigger cannot contain a transaction control statement (Commit,rollback,savepoint). Because triggers are part of a trigger statement, triggers are committed, rolled back, and the trigger is committed and rolled back.

Q. You cannot declare any long and BLOB variables in the trigger body. The new value, the old value, and the OID cannot be any long and BLOB columns in the table.

R. Order of execution: If multiple triggers are defined on a table (that is, two before INSERT triggers are defined), they will be executed in the order in which they were created. Of course, before triggers are always activated before after triggers without regard to the order in which they were created. Other constraints on the table (such as primary/foreign KEY constraints, uniqueness constraints, and CHECK constraints) are also checked after the trigger after the Before trigger

2. Trigger Trigger order

1. Execute before statement level triggers;

2. For each row that is affected by the statement:

L Execute before row-level triggers

L Execute DML statements

L execute after row-level triggers

3. Execute AFTER statement level triggers

3. Detailed Grammar

no CASCADE before insert on: means the action that is triggered occurs before the data is actually inserted into the table, and the action of the trigger does not cause any other triggers to be activated. For all before triggers, add the keyword no CASCAD, which can contain one or more of the following SQL statements only in the Post trigger after and instead OF triggers

Begin Atomic ... End: BEGIN ATOMIC Specify that the actions in the trigger are either not executed or all executed. If an error occurs during the execution of the trigger action, all actions are rolled back to maintain the integrity of the data. If you enter multiple SQL statements between the begin ATOMIC and the end keyword, you must end each statement with a semicolon (;) or an exclamation point (!). If the SQL contains a single statement, then the begin ATOMIC and ending keywords are not required

MODE db2sql: The specified statement

referencing: clause describes the correlation name, referencing new as n equals the alias of the row or result that specifies the newly affected as N to be referenced in the execution statement

inserting: True if the trigger event is insert, false otherwise.

Updating: [(Column_1,column_2,..., column_x)]: When the trigger event is update, if the column_x column is modified, the value is true or false. Where the column_x is optional.

Deleting: True if the trigger event is delete, or false

For each row: the trigger is activated at each row of the operation, for each statement is activated when each SQL statement is executed;

So if you have an SQL statement that modifies the 10 hop record, the for each row runs 10, and for each statement only once

if inserting THEN: A conditional judgment statement that executes the THEN statement when the INSERT statement is executed

:: The value of the column after the NEW modifier access operation completes 4. Trigger Example

, creating triggers

--Touch when inserting

CREATE TRIGGER Administrator.tri_insert

After INSERT on administrator. A

Referencing NEW as N

For each ROW MODE Db2sql

BEGIN ATOMIC

INSERT into administrator. B (Id,name) VALUES (n.id,n.name);

End

--Test: INSERT into administrator. A VALUES (' 3 ', ' Name ');

Oracle Example: CREATE OR REPLACE TRIGGER tr_del_emp
Before Delete--Specifies that the trigger time is triggered before the delete operation
On Scott.emp
For each row--note that a row-level trigger is created
BEGIN
--Insert the modified data into the logging table del_emp for oversight use.
INSERT into Emp_his (Deptno, empno, ename, Job, Mgr, Sal, Comm, HireDate)
VALUES (: Old.deptno,: Old.empno,: Old.ename,: Old.job,:old.mgr,: old.sal,: Old.comm,: old.hiredate);
End;

--Touch when deleting

CREATE TRIGGER Administrator.tri_delete

After deletes on the administrator. A

referencing old as O

For each ROW MODE Db2sql

BEGIN ATOMIC

INSERT into administrator. B (ID) VALUES (o.id);

End--Test: DELETE from Administrator. A WHERE ID = ' 3 '

--Touch when updated

CREATE OR REPLACE TRIGGER administrator.tri_update

After UPDATE's NAME on administrator. A

Referencing NEW as N old as O

For each ROW MODE Db2sql

BEGIN ATOMIC

INSERT into administrator. B (Id,name) VALUES (o.id,n.name);

End

--Test: UPDATE administrator. A SET NAME = ' N2_name '

--There is information that DB2 9.7 or more versions support after INSERT or DELETE or UPDATE, but the 9.7 version does not support

Example:

CREATE OR REPLACE TRIGGER hired
After INSERT OR DELETE or UPDATE ' SALARY on EMPLOYEE
Referencing NEW as N as O for each ROW
BEGIN
IF inserting THEN

UPDATE company_stats SET nbemp = nbemp + 1;

ELSEIF

deleting THEN UPDATE company_stats SET nbemp = NBEMP-1;

ELSEIF (Updating and (N.salary > 1.1 * o.salary))
THEN SIGNAL SQLSTATE ' 75000 ' SET message_text= ' Salary increase>10% ';
End IF;
End;