1、创建结果记录表:Create table result (type varchar(10),num int);
2、创建表及插入数据,执行如下SQL命令:
CREATE TABLE FUT_CFQ_BEFORE_T(A INT PRIMARY KEY, B VARCHAR(10));
INSERT INTO FUT_CFQ_BEFORE_T VALUES(1,'AAA');
INSERT INTO FUT_CFQ_BEFORE_T VALUES(2,'BBB');
INSERT INTO FUT_CFQ_BEFORE_T VALUES(3,'BBB');
3、创建BEFORE语句级触发器,执行如下SQL命令:
CREATE OR REPLACE TRIGGER FUT_CFQ_BEFORE
BEFORE INSERT ON FUT_CFQ_BEFORE_T
BEGIN
Insert into result select 'BEFORE',count(*) from FUT_CFQ_BEFORE_T;
END;
4、检验触发,执行如下SQL命令:INSERT INTO FUT_CFQ_BEFORE_T VALUES(3,'BBB');此时插入的时候会报违反键的唯一性约束性,说明没问题
5、查询测试结果表信息,执行如下SQL命令:Select num from result where type='BEFORE';
6、再次插入,执行如下SQL命令:INSERT INTO FUT_CFQ_BEFORE_T VALUES(4,'BBB');
7、再次查询结果,执行sql如下:Select num from result where type='BEFORE';
8、此时一个简单的触发器就出发成功了;
9、如何创建INSTEAD OF触发器;
10、创建表、视图,建基于视图操作的INSTEAD OF触发器,执行如下SQL命令:CREATE TABLE FUT_CFQ_INSTEAD_T (A INT PRIMARY KEY, B VARCHAR(10));
CREATE VIEW FUT_CFQ_INSTEAD_V AS SELECT A FROM FUT_CFQ_INSTEAD_T;
INSERT INTO FUT_CFQ_INSTEAD_T VALUES(1,'AAA');
INSERT INTO FUT_CFQ_INSTEAD_T VALUES(2,'BBB');
INSERT INTO FUT_CFQ_INSTEAD_T VALUES(3,'BBB');
CREATE OR REPLACE TRIGGER FUT_CFQ_INSTEAD
INSTEAD OF INSERT ON FUT_CFQ_INSTEAD_V
BEGIN
INSERT INTO FUT_CFQ_INSTEAD_T VALUES(5,'CC');
END;
11:、验证触发,执行如下SQL命令:
INSERT INTO FUT_CFQ_INSTEAD_V VALUES(4);
SELECT * FROM FUT_CFQ_INSTEAD_V;
12、此时INSTEAD OF触发器已经触发。