DB2 Trigger Simple example

DB2 using version 9.7

Create a, b two tables, a table data update, delete, insert, the A table ID record into the B table

1. CREATE table A (ID varchar (5), name varchar (30));

CREATE TABLE B (ID varchar (5), name varchar (30))

2. Create a Trigger

--touches when inserting

CREATE TRIGGER Administrator.tri_insert

After the 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 ');

--Touch when deleted

CREATE TRIGGER Administrator.tri_delete

After DELETE on 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 updating

CREATE OR REPLACE TRIGGER administrator.tri_update

After UPDATE of the 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 are information that DB2 9.7 or later supports after INSERT or DELETE or UPDATE notation, but version 9.7 does not support

Example:

CREATE OR REPLACE 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 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;

After that, consider using the Oracle compatibility settings in DB2, Db2set Db2_compatibility_vector=ora, which still does not support after INSERT or DELETE or UPDATE notation, There is a difficulty in using the after INSERT or DELETE or UPDATE notation in version 9.7 to successfully

DB2 Trigger Simple example