概述
触发器
Oracle 触发器 :
触发器是特定事件出现的时候，自动执行的代码块。类似于存储过程，但是用户不能直接调用他们。
功能：
1 、 允许 / 限制对表的修改
2 、 自动生成派生列，比如自增字段
3 、 强制数据一致性
4 、 提供审计和日志记录
5 、 防止无效的事务处理
6 、 启用复杂的业务逻辑
开始 :
create trigger biufer_employees_department_id
before insert or update
of department_id
on employees
referencing old as old_value
new as new_value
for each row
when (new_value.department_id<>80 )
begin
:new_value.commission_pct :=0;
end;
/
触发器的组成部分：
1 、 触发器名称
2 、 触发语句
3 、 触发器限制
4 、 触发操作
1 、 触发器名称
create trigger biufer_employees_department_id
命名习惯：
biufer （ before insert update for each row ）
employees 表名
department_id 列名
2 、 触发语句
比如：
表或视图上的 DML 语句； DDL 语句，数据库关闭或启动 ,startup shutdown 等等
before insert or update
of department_id
on employees
referencing old as old_value
new as new_value
for each row
说明：
（ 1 ）、 无论是否规定了 department_id ，对 employees 表进行 insert 的时候
（ 2 ）、 对 employees 表的 department_id 列进行 update 的时候
3 、 触发器限制
when (new_value.department_id<>80 )
限制不是必须的。此例表示如果列 department_id 不等于 80 的时候，触发器就会执行。其中的 new_value 是代表跟新之后的值。
4 、 触发操作
是触发器的主体
begin
:new_value.commission_pct :=0;
end;
主体很简单，就是将更新后的 commission_pct 列置为 0
触发：
insert into employees
(employee_id,last_name,first_name,hire_date,job_id,email,department_id,salary,commission_pct )
values( 12345,’Chen’,’Donny’, sysdate, 12, ‘donny@hotmail.com’,60,10000,.25);
select commission_pct from employees where employee_id=12345;
触发器不会通知用户，便改变了用户的输入值。
触发器类型：
1 、 语句触发器
2 、 行触发器
3 、 INSTEAD OF 触发器
4 、 系统条件触发器
5 、 用户事件触发器
1 、 语句触发器
是在表上或者某些情况下的视图上执行的特定语句或者语句组上的触发器。能够与 INSERT 、 UPDATE 、 DELETE 或者组合上进行关联。但是无论使用什么样的组合，各个语句触发器都只会针对指定语句激活一次。 比如，无论 update 多少行，也只会调用一次 update 语句触发器。
例子：
需要对在表上进行 DML 操作的用户进行安全检查，看是否具有合适的特权。
Create table foo(a number);
Create trigger biud_foo
Before insert or update or delete
On foo
Begin
If user not in (‘DONNY’) then
Raise_application_error(-20001, ‘You don’t have access to modify this table.’);
End if;
End;
/
即使 SYS ， SYSTEM 用户也不能修改 foo 表
[ 试验 ]
对修改表的时间、人物进行日志记录。
1 、 建立试验表
create table employees_copy as select *from hr.employees
2 、 建立日志表
create table employees_log( who varchar2(30), when date);
3 、 在 employees_copy 表上建立语句触发器，在触发器中填充 employees_log 表。
Create or replace trigger biud_employee_copy
Before insert or update or delete
On employees_copy
Begin
Insert into employees_log( Who,when) Values( user, sysdate);
End;
/
4 、 测试
update employees_copy set salary= salary*1.1;
select *from employess_log;
5 、 确定是哪个语句起作用？
即是 INSERT/UPDATE/DELETE 中的哪一个触发了触发器？
可以在触发器中使用 INSERTING / UPDATING / DELETING 条件谓词 ，作判断：
begin
if inserting then
-----
elsif updating then
-----
elsif deleting then
------
end if;
end;
if updating(‘COL1’) or updating(‘COL2’) then
------
end if;
[ 试验 ]
1 、 修改日志表
alter table employees_log add (action varchar2(20));
2 、 修改触发器，以便记录语句类型。
Create or replace trigger biud_employee_copy
Before insert or update or delete
On employees_copy
Declare
L_action employees_log.action%type;
Begin
if inserting then
l_action:=’Insert’;
elsif updating then
l_action:=’Update’;
elsif deleting then
l_action:=’Delete’;
else
raise_application_error(-20001,’You should never ever get this error.’);
Insert into employees_log( Who,action,when) Values( user, l_action,sysdate);
End;
/
3 、 测试
insert into employees_copy( employee_id, last_name, email, hire_date, job_id)
values(12345,’Chen’,’Donny@hotmail’,sysdate,12);
select *from employees_log
update employees_copy set salary=50000 where employee_id = 12345;
2 、 行触发器
是指为受到影响的各个行激活的触发器，定义与语句触发器类似，有以下两个例外：
1 、 定义语句中包含 FOR EACH ROW 子句
2 、 在 BEFORE …… FOR EACH ROW 触发器中，用户可以引用受到影响的行值。
比如：
定义：
create trigger biufer_employees_department_id
before insert or update
of department_id
on employees_copy
referencing old as old_value
new as new_value
for each row
when (new_value.department_id<>80 )
begin
:new_value.commission_pct :=0;
end;
/
Referencing 子句：
执行 DML 语句之前的值的默认名称是 :old , 之后的值是 :new
insert 操作只有 :new
delete 操作只有 :old
update 操作两者都有
referencing 子句只是将 new 和 old 重命名为 new_value 和 old_value ，目的是避免混淆。比如操作一个名为 new 的表时。作用不很大。
[ 试验 ] ：为主健生成自增序列号
drop table foo;
create table foo(id number, data varchar2(20));
create sequence foo_seq;
create or replace trigger bifer_foo_id_pk
before insert on foo
for each row
begin
select foo_seq.nextval into :new.id from dual;
end;
/
insert into foo(data) values(‘donny’);
insert into foo values(5,’Chen’);
select * from foo;
3 、 INSTEAD OF 触发器更新视图
instead of 触发器，可以实现 : 不执行导致 trigger 触发的语句，而只执行触发器 .
INSTEAD OF triggers provide a transparent way of modifying views that cannot be modified directly through DML statements (INSERT, UPDATE, and DELETE). These triggers are called INSTEAD OF triggers because, unlike other types of triggers, Oracle fires the trigger instead of executing the triggering statement. You can write normal INSERT, UPDATE, and DELETE statements against the view and the INSTEAD OF trigger is fired to update the underlying tables appropriately. INSTEAD OF triggers are activated for each row of the view that gets modified.
Create or replace view company_phone_book as
Select first_name||’, ’||last_name name, email, phone_number,employee_id emp_id
From hr.employees;
尝试更新 email 和 name
update hr.company_phone_book set name=’Chen1, Donny1’ where emp_id=100
create or replace trigger update_name_company_phone_book
INSTEAD OF
Update on hr.company_phone_book
Begin
Update hr.employees
Set employee_id=:new.emp_id,
First_name=substr(:new.name, instr(:new.name,’,’)+2),
last_name= substr(:new.name,1,instr(:new.name,’,’)-1),
phone_number=:new.phone_number,
email=:new.email
where employee_id=:old.emp_id;
end;
instead of trigger 是基于视图建立的 , 不能建在表上 , 为什么要建在视图上 , 一般的视图如果其数据来源一个表并且包含该表的主键 , 就可以对视图进行 DML 操作 . 另外一种情况是从多个表查询出来的 . 这样我们就不能对视图进行操作了 , 也就是只能查询 .instead of trigger 可以解决建在多表上视图的更新操作 .
下面我们就来实例操作 :
a. 先建表 , 简单点就三个分别是学生表 , 课程表 , 学生选课表
CREATE TABLE STUDENT
(
CODE VARCHAR2(5),
LNAME VARCHAR2(200)
)
CREATE TABLE COURSE
(
CODE VARCHAR2(5),
CNAME VARCHAR2(30)
)
CREATE TABLE ST_CR
(
STUDENT VARCHAR2(5),
COURSE VARCHAR2(5),
GRADE NUMBER
)
-- 表的约束
ALTER TABLE STUDENT ADD CONSTRAINT STUDENT$PK PRIMARY KEY(CODE);
ALTER TABLE COURSE ADD CONSTRAINT COURSE$PK PRIMARY KEY(CODE);
ALTER TABLE ST_CR ADD CONSTRAINT ST_CR$PK PRIMARY KEY(STUDENT, COURSE);
ALTER TABLE ST_CR ADD CONSTRAINT ST_CR$FK$STUDENT FOREIGN KEY(STUDENT) REFERENCES STUDENT(CODE);
ALTER TABLE ST_CR ADD CONSTRAINT ST_CR$FK$COURSE FOREIGN KEY(COURSE) REFERENCES COURSE(CODE);
b. 基于这三个表的视图
CREATE OR REPLACE VIEW STUDENT_STATUS AS
SELECT S.CODE S_CODE, S.LNAME STUDENT, C.CODE C_CODE, C.CNAME COURSE, SC.GRADE GRADE
FROM STUDENT S, COURSE C, ST_CR SC
WHERE S.CODE = SC.STUDENT
AND C.CODE = SC.COURSE
c. 基于视图的触发器
CREATE OR REPLACE TRIGGER TRI_STCR INSTEAD OF INSERT ON STUDENT_STATUS
FOR EACH ROW
DECLARE
W_ACTION VARCHAR2(1);
BEGIN
IF INSERTING THEN
W_ACTION := 'I';
ELSE
RAISE PROGRAM_ERROR;
END IF;
INSERT INTO STUDENT(CODE, LNAME) VALUES(:NEW.S_CODE,:NEW.STUDENT);
INSERT INTO COURSE(CODE, CNAME) VALUES(:NEW.C_CODE, :NEW.COURSE);
INSERT INTO ST_CR(STUDENT, COURSE, GRADE)
VALUES(:NEW.S_CODE, :NEW.C_CODE, :NEW.GRADE);
END;
d. 对视图执行数据插入
INSERT INTO STUDENT_STATUS(S_CODE, STUDENT, C_CODE, COURSE, GRADE)
VALUES('001','Mike','EN','English',86);
可以看到每个表各有一条数据已经插入 .
4 、 系统事件触发器
系统事件：数据库启动、关闭，服务器错误
create trigger ad_startup
after startup
on database
begin
-- do some stuff
end;
/
5 、 用户事件触发器
用户事件：用户登陆、注销， CREATE / ALTER / DROP / ANALYZE / AUDIT / GRANT / REVOKE / RENAME / TRUNCATE / LOGOFF
例子：记录删除对象
1. 日志表
create table droped_objects(
object_name varchar2(30),
object_type varchar2(30),
dropped_on date);
2 ．触发器
create or replace trigger log_drop_trigger
before drop on donny.schema
begin
insert into droped_objects values(
ora_dict_obj_name, -- 与触发器相关的函数
ora_dict_obj_type,
sysdate);
end;
/
3. 测试
create table drop_me(a number);
create view drop_me_view as select *from drop_me;
drop view drop_me_view;
drop table drop_me;
select *from droped_objects
禁用和启用触发器
alter trigger <trigger_name> disable;
alter trigger <trigger_name> enable;
事务处理：
在触发器中，不能使用 commit / rollback, 因为 ddl 语句具有隐式的 commit ，所以也不允许使用
视图：
dba_triggers
MS SQLServer 触发器 :
SQL Sever 2005 包含的 3 个触发器对象 :
AFTER ，数据定义语言 (DDL) 和 INSTEAD-OF
1． AFTER 触发器是存储程序，它发生于数据操作语句作用之后，例如删除语句等。
2． DDL 是 SQL Server 2005 的新触发器，允许响应数据库引擎中对象定义水平事件 ( 例如 :DROP TABLE 语句 ) 。
3． INSTEAD-OF 触发器是对象，在数据库引擎中可以取代数据操作语句而执行。例如 : 将 INSTEAD-OF INSERT 触发器附加到表，告诉数据库执行此触发器
SQL Server 2005 中 DDL 触发器的实现
SQL SERVER 2005 中，新增加了许多新的特性，其中的 DDL 触发器是个不错的选择，根据资料初步学习如下，现整理之：
在 sql server 2000 中，只能为针对表发出的 DML 语句（ INSERT 、 UPDATE 和 DELETE ）定义 AFTER 触发器。 SQL Server 2005 可以就整个服务器或数据库的某个范围为 DDL 事件定义触发器。可以为单个 DDL 语句（例如， CREATE_TABLE ）或者为一组语句（例如， DDL_DATABASE_LEVEL_EVENTS ）定义 DDL 触发器。在该触发器内部，您可以通过访问 eventdata() 函数获得与激发该触发器的事件有关的数据。该函数返回有关事件的 XML 数据。每个事件的架构都继承了 Server Events 基础架构。
比如，在 SQL SERVER 2005 中，建立一个叫 DDLTrTest 的数据库，并且建立一个叫 mytable 的表和 Usp_Querymytable 的存储过程，如下所示
DROP DATABASE [DDLTRTEST]
GO
CREATE DATABASE DDLTRTEST
GO
USE [DDLTRTEST]
GO
IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[MYTABLE]') AND TYPE IN (N'U'))
DROP TABLE [DBO].[MYTABLE]
GO
CREATE TABLE MYTABLE(ID INT, NAME VARCHAR(100))
GO
INSERT INTO MYTABLE SELECT 1,'A'
INSERT INTO MYTABLE SELECT 2,'B'
INSERT INTO MYTABLE SELECT 3,'C'
INSERT INTO MYTABLE SELECT 4,'D'
INSERT INTO MYTABLE SELECT 5,'E'
INSERT INTO MYTABLE SELECT 6,'F'
GO
USE [DDLTrTest]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_querymytable]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_querymytable]
GO
CREATE PROC USP_QUERYMYTABLE AS SELECT * FROM MYTABLE
GO
接下来定义一个 DDL 触发器如下 :
CREATE TRIGGER STOP_DDL_on_Table_and_PROC
ON DATABASE
FOR
CREATE_TABLE, DROP_TABLE, ALTER_TABLE,
CREATE_PROCEDURE, ALTER_PROCEDURE,DROP_PROCEDURE
AS
SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)')
PRINT 'You are not allowed to CREATE,ALTER and DROP any Tables and Procedures'
ROLLBACK;
接下来，我们尝试如下的操作：
ALTER TABLE MYTABLE ADD X INT
结果如下，出现错误提示
ALTER TABLE MYTABLE ADD X INT
(1 row(s) affected)
You are not allowed to CREATE,ALTER and DROP any Tables and Procedures
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
再执行 DROP 的操作，同样触发警告
DROP TABLE MYTABLE
(1 row(s) affected)
You are not allowed to CREATE,ALTER and DROP any Tables and Procedures
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
因为我们的触发器规定了不能使用 CREATE_TABLE,DROP_TABLE, ALTER_TABLE,CREATE_PROCEDURE,ALTER_PROCEDURE,DROP_PROCEDURE 等操作。
如果我们要关掉这个触发器，可以这样做：
DISABLE TRIGGER STOP_DDL_ON_TABLE_AND_PROC ON DATABASE
当然，我们要对整个服务器采取策略的话，也是很简单的，和上面的方法大致相同只不过将 on database 的参数改为 on server, 比如
CREATE TRIGGER STOP_DDL_on_Table_and_PROC
ON ALL SERVER
FOR
CREATE_DATABASE,ALTER_DATABASE,DROP_DATABASE
AS
PRINT 'You are not allowed to CREATE,ALTER and DROP any Databases'
ROLLBACK;
如何使用 SQL Server 2005 INSTEAD OF 触发器
触发器是类似于存储程序的数据库对象，它响应数据库环境下的某个请求。 SQL Sever 2005 包含 3 个触发器对象： AFTER ，数据定义语言 (DDL) 和 INSTEAD-OF 。
AFTER 触发器是存储程序，它发生于数据操作语句作用之后，例如删除语句等。 DDL 是 SQL Server 2005 的新触发器，允许响应数据库引擎中对象定义水平事件（例如： DROP TABLE 语句）。 INSTEAD-OF 触发器是对象，在数据库引擎中可以取代数据操作语句而执行。例如：将 INSTEAD-OF INSERT 触发器附加到表，告诉数据库执行此触发器。
1. 使用 INSTEAD-OF 触发器的理由
INSTEAD-OF 触发器是 SQL Sever 功能强大的对象，允许开发人员转移数据库引擎完成不同的工作，以满足开发要求。其中的一个例子是在数据库的表中添加 INSTEAD-OF 触发器，当不需要修改表时，可以对表的内容进行回滚。使用此方法时，必须格外小心，因为任何指定的表修改之前， INSTEAD-OF 触发器必须处于激活状态。
使用 INSTEAD-OF 触发器一个更充分理由是视图处理。在视图中添加 INSTEAD-OF 触发器后，则可创建更新的视图。可更新视图允许完整地提取数据库大纲，因此可以用此方法设计系统，而不需要担心 OLTP 数据库大纲的问题，并且取代数据修改一组标准视图集。
2. 范例
为了更好地说明可更新视图概念，我们提供一个示例。在本例中，我们设计一个产品表（记录产品），一个购买表（记录购买）。 Listing A 包含了创建表的脚本，运行此脚本后则得到示例中所要用到的表。运行 Listing B 脚本向表中添加数据。
现在表中已经有数据了，我可以为这些表创建一些有意义的视图。请查看 Listing C 。
这是个典型的产品水平的视图。它联合了数据库中的两个表，使得数据简单化了。但是，对于数据提取，使用视图则没有什么优势。在视图上附上 INSTEAD- OF 触发器后，则允许修改表，但是我不需要直接修改表中的数据。我使用 Listing D 中的代码在 vw_ProductPurchases 视图上创建一个 INSTEAD-OF 触发器。
请注意此 INSTEAD OF 触发器的声明。 SQL Server 创建的默认的触发器为 AFTER 触发器，因此，必须在触发器定义中指定 INSTEAD OF 子句。
触发器的第一条语句是“ check ”语句。本例中我使用此语句检测 INSERTED 表以确保显示 ProductID 字段，并且保证提供显示其他 PurchasePrice 或 ProductPrice 字段。
如果必要的数据通过 INSERT 语句都插入到视图中，则触发器将插入指定的值到数据表。下面即是视图的 INSERT 语句。
INSERT INTO vw_ProductPurchases(ProductID, PurchasePrice) VALUES(1, 700)
INSERT 语句提供了有效的 ProductID 和 PurchasePrice ，这意味着新记录插入到 Purchases 表
3. 结论
INSTEAD-OF 触发器具有强大的功能和灵活性。如果系统不大，使用视图系统提取数据大纲能够极大保护数据库程序。上面的例子很简单，系统中所用到的复杂的触发器需要考虑安全性问题、时间开销和其他限制。
MSSQLServer 触发器介绍：
触发器实际上就是一种特殊类型的存储过程，其特殊性表现在：它是在执行某些特定的 T-SQL 语句时自动的。
11.1 触发器简介
触发器实际上就是一种特殊类型的存储过程，它是在执行某些特定的 T-SQL 语句时自动执行的一种存储过程。在 SQL Server 2005 中，根据 SQL 语句的不同，把触发器分为两类：一类是 DML 触发器，一类是 DLL 触发器。
11.1.1 触发器的概念和作用
在 SQL Server 2005 里，可以用两种方法来保证数据的有效性和完整性：约束（ check ）和触发器（ Trigger ）。 约束是直接设置于数据表内，只能现实一些比较简单的功能操作，如：实现字段有效性和唯一性的检查、自动填入默认值、确保字段数据不重复（即主键）、确保数据表对应的完整性（即外键）等功能。
触发器是针对数据表（库）的特殊的存储过程，当这个表发生了 Insert 、 Update 或 Delete 操作时，会自动激活执行的，可以处理各种复杂的操作。在 SQL Server 2005 中，触发器有了更进一步的功能，在数据表（库）发生 Create 、 Alter 和 Drop 操作时，也会自动激活执行。
触发器常用的一些功能如下：
l 完成比约束更复杂的数据约束：触发器可以实现比约束更为复杂的数据约束
l 检查所做的 SQL 是否允许：触发器可以检查 SQL 所做的操作是否被允许。例如：在产品库存表里，如果要删除一条产品记录，在删除记录时，触发器可以检查该产品库存数量是否为零，如果不为零则取消该删除操作。
l 修改其它数据表里的数据：当一个 SQL 语句对数据表进行操作的时候，触发器可以根据该 SQL 语句的操作情况来对另一个数据表进行操作。例如：一个订单取消的时候，那么触发器可以自动修改产品库存表，在订购量的字段上减去被取消订单的订购数量。
l 调用更多的存储过程：约束的本身是不能调用存储过程的，但是触发器本身就是一种存储过程，而存储过程是可以嵌套使用的，所以触发器也可以调用一个或多过存储过程。
l 发送 SQL Mail ：在 SQL 语句执行完之后，触发器可以判断更改过的记录是否达到一定条件，如果达到这个条件的话，触发器可以自动调用 SQL Mail 来发送邮件。例如：当一个订单交费之后，可以物流人员发送 Email ，通知他尽快发货。
l 返回自定义的错误信息：约束是不能返回信息的，而触发器可以。例如插入一条重复记录时，可以返回一个具体的友好的错误信息给前台应用程序。
l 更改原本要操作的 SQL 语句：触发器可以修改原本要操作的 SQL 语句，例如原本的 SQL 语句是要删除数据表里的记录，但该数据表里的记录是最要记录，不允许删除的，那么触发器可以不执行该语句。
l 防止数据表构结更改或数据表被删除：为了保护已经建好的数据表，触发器可以在接收到 Drop 和 Alter 开头的 SQL 语句里，不进行对数据表的操作。
11.1.2 触发器的种类
在 SQL Server 2005 中，触发器可以分为两大类： DML 触发器和 DDL 触发器
l DML 触发器： DML 触发器是当数据库服务器中发生数据操作语言（ Data Manipulation Language ）事件时执行的存储过程。 DML 触发器又分为两类： After 触发器和 Instead Of 触发器
l DDL 触发器： DDL 触发器是在响应数据定义语言（ Data Definition Language ）事件时执行的存储过程。 DDL 触发器一般用于执行数据库中管理任务。如审核和规范数据库操作、防止数据库表结构被修改等。
11.2 DML 触发器的分类
SQL Server 2005 的 DML 触发器分为两类：
l After 触发器：这类触发器是在记录已经改变完之后（ after ），才会被激活执行，它主要是用于记录变更后的处理或检查，一旦发现错误，也可以用 Rollback Transaction 语句来回滚本次的操作。
l Instead Of 触发器：这类触发器一般是用来取代原本的操作，在记录变更之前发生的，它并不去执行原来 SQL 语句里的操作（ Insert 、 Update 、 Delete ），而去执行触发器本身所定义的操作。
11.3 DML 触发器的工作原理
在 SQL Server 2005 里，为每个 DML 触发器都定义了两个特殊的表，一个是插入表，一个是删除表。这两个表是建在数据库服务器的内存中的，是由系统管理的逻辑表，而不是真正存储在数据库中的物理表。对于这两个表，用户只有读取的权限，没有修改的权限。
这两个表的结构与触发器所在数据表的结构是完全一致的，当触发器的工作完成之后，这两个表也将会从内存中删除。
插入表里存放的是更新前的记录：对于插入记录操作来说，插入表里存放的是要插入的数据；对于更新记录操作来说，插入表里存放的是要更新的记录。
删除表里存放的是更新后的记录：对于更新记录操作来说，删除表里存放的是更新前的记录（更新完后即被删除）；对于删除记录操作来说，删除表里存入的是被删除的旧记录。
下面看一下触发器的工作原理。
11.3.1 After 触发器的工作原理
After 触发器是在记录更变完之后才被激活执行的。以删除记录为例：当 SQL Server 接收到一个要执行删除操作的 SQL 语句时， SQL Server 先将要删除的记录存放在删除表里，然后把数据表里的记录删除，再激活 After 触发器，执行 After 触发器里的 SQL 语句。执行完毕之后， 删除内存中的删除表，退出整个操作。
还是举上面的例子：在产品库存表里，如果要删除一条产品记录，在删除记录时，触发器可以检查该产品库存数量是否为零，如果不为零则取消删除操作。看一下数据库是怎么操作的：
（ 1 ）接收 SQL 语句，将要从产品库存表里删除的产品记录取出来，放在删除表里。
（ 2 ）从产品库存表里删除该产品记录。
（ 3 ）从删除表里读出该产品的库存数量字段，判断是不是为零，如果为零的话，完成操作，从内存里清除删除表；如果不为零的话，用 Rollback Transaction 语句来回滚操作。
11.3.2 Instead Of 触发器的工作原理
Instead Of 触发器与 After 触发器不同。 After 触发器是在 Insert 、 Update 和 Delete 操作完成后才激活的，而 Instead Of 触发器，是在这些操作进行之前就激活了，并且不再去执行原来的 SQL 操作，而去运行触发器本身的 SQL 语句。
11.4 设计 DML 触发器的注意事项及技巧
在了解触发器的种类和工作理由之后，现在可以开始动手来设计触发器了，不过在动手之前，还有一些注意事项必须先了解一下：
11.4.1 设计触发器的限制
在触发器中，有一些 SQL 语句是不能使用的，这些语句包括：
表 11.1 在 DML 触发器中不能使用的语句
| 不能使用的语句 | 语句功能 |
| Alter Database | 修改数据库 |
| Create Database | 新建数据库 |
| Drop Database | 删除数据库 |
| Load Database | 导入数据库 |
| Load Log | 导入日志 |
| Reconfigure | 更新配置选项 |
| Restore Database | 还原数据库 |
| Restore Log | 还原数据库日志 |
另外，在对作为触发操作的目标的表或视图使用了下面的 SQL 语句时，不允许在 DML 触发器里再使用这些语句：
表 11.2 在目标表中使用过的， DML 触发器不能再使用的语句
| 不能使用的语句 | 语句功能 |
| Create Index | 建立索引 |
| Alter Index | 修改索引 |
| Drop Index | 删除索引 |
| DBCC Dbreindex | 重新生成索引 |
| Alter Partition Function | 通过拆分或合并边界值更改分区 |
| Drop Table | 删除数据表 |
| Alter Table | 修改数据表结构 |
11.4.2 如何在触发器取得字段修改前和修改后的数据
上面介绍过， SQL Server 2005 在为每个触发器都定义了两个虚拟表，一个是插入表（ inserted ），一个是删除表（ deleted ），现在把这两个表存放的数据列表说明一下：
表 11.3 插入 / 删除表的功能
| 激活触发器的动作 | Inserted 表 | Deleted 表 |
| Insert | 存放要插入的记录 |
|
| Update | 存放要更新的记录 | 存放更新前的旧记录 |
| Delete |
| 存放要删除的旧记录 |
以上面删除库存产品记录为例，在删除时触发器要判断库存数量是否为零，那么判断就应该这么写：
If (Select 库存数量 From Deleted)>0
Begin
Print ‘ 库存数量大于零时不能删除此记录 ’
Rollback Transaction
End
11.4.3 其他注意事项
l l After 触发器只能用于数据表中， Instead Of 触发器可以用于数据表和视图上，但两种触发器都不可以建立在临时表上。
l l 一个数据表可以有多个触发器，但是一个触发器只能对应一个表。
l l 在同一个数据表中，对每个操作（如 Insert 、 Update 、 Delete ）而言可以建立许多个 After 触发器，但 Instead Of 触发器针对每个操作只有建立一个。
l l 如果针对某个操作即设置了 After 触发器又设置了 Instead Of 触发器，那么 Instead of 触发器一定会激活，而 After 触发器就不一定会激活了。
l l Truncate Table 语句虽然类似于 Delete 语句可以删除记录，但是它不能激活 Delete 类型的触发器。因为 Truncate Table 语句是不记入日志的。
l l WRITETEXT 语句不能触发 Insert 和 Update 型的触发器。
l l 不同的 SQL 语句，可以触发同一个触发器，如 Insert 和 Update 语句都可以激活同一个触发器。
11.5 设计 After 触发器
在了解触发器及其种类、作用、工作原理之后，下面详细讲述一下要怎么去设计及建立触发器。
11.5.1 设计简单的 After 触发器
下面用实例设计一个简单的 After Insert 触发器，这个触发器的作用是：在插入一条记录的时候，发出 “ 又添加了一种产品 ” 的友好提示。
（ 1 ）启动 Management Studio ，登录到指定的服务器上。
（ 2 ）在如图 11.1 所示界面的【对象资源管理器】下选择【数据库】，定位到【 Northwind 】数据库 à 【表】 à 【 dbo. 产品】，并找到【触发器】项。
图 11.1 定位到触发器
（ 3 ）右击【触发器】，在弹出的快捷菜单中选择【新建触发器】选项，此时会自动弹出【查询编辑器】对话框，在【查询编辑器】的编辑区里 SQL Server 已经预写入了一些建立触发器相关的 SQL 语句，如图 11.2 所示。
图 11.2 SQL Server 2005 预写的触发器代码
（ 4 ）修改【查询编辑器】里的代码，将从 “CREATE” 开始到 “GO” 结束的代码改为以下代码：
CREATE TRIGGER 产品 _Insert
ON 产品
AFTER INSERT
AS
BEGIN
print ' 又添加了一种产品 '
END
GO
如果有兴趣的话，也可以去修改一下如图 11.2 中绿色部分的版权信息。
（ 5 ）单击工具栏中的【分析】按钮 ，检查一下是否语法有错，如图 11.3 所示，如果在下面的【结果】对话框中出现 “ 命令已成功完成 ” ，则表示语法没有错误。
图 11.3 检查语法
（ 6 ）语法检查无误后，单击【执行】按钮，生成触发器。
（ 7 ）关掉查询编辑器对话框，刷新一下触发器对话框，可以看到刚才建立的【产品 _Insert 】触发器，如图 11.4 所示。
图 11.4 建好的触发器
建立 After Update 触发器、 After Delete 触发器和建立 After Insert 触发器的步骤一致，不同的地方是把上面的 SQL 语句中的 AFTER INSERT 分别改为 AFTER UPDATE 和 AFTER DELETE 即可，如下所示，有兴趣的读者可以自行测试。
CREATE TRIGGER 产品 _Update
ON 产品
AFTER UPDATE
AS
BEGIN
print ' 有一种产品更改了 '
END
GO
CREATE TRIGGER 产品 _Delete
ON 产品
AFTER DELETE
AS
BEGIN
print ' 又删除了一种产品 '
END
GO
11.5.2 测试触发器功能
建好 After Insert 触发器之后，现在来测试一下触发器是怎么样被激活的。
（ 1 ）在 Management Studio 里新建一个查询，在弹出的【查询编辑器】对话框里输入以下代码：
INSERT INTO 产品 ( 产品名称 ) VALUES (' 大苹果 ')
（ 2 ）单击【执行】按钮，可以看到【消息】对话框里显示出一句提示： “ 又添加了一种产品 ” ，如图 11.5 所示，这说明， After Insert 触发器被激活，并运行成功了。
图 11.5 查看触发器的运行结果
而如果在【查询编辑器】里执行的不是一个 Insert 语句，而是一个 Delete 语句的话， After Insert 触发器将不会被激活。如在【查询编辑器】输入以下语句：
DELETE FROM 产品 WHERE ( 产品名称 = ' 大苹果 ')
单击【执行】按钮，在【消息】对话框里只显示了一句 “(1 行受影 响 )” 的提示，而没有 “ 又添加了一种产品 ” 的提示，如图 11.6 所示。这是因为 Delete 语句是不能激活 After Insert 触发器，所以 After Insert 触发器里的 “print ‘ 又添加了一种产品 ’” 语句并没有执行。
图 11.6 执行删除语句不会激活 After Insert 触发器
11.5.3 建立触发器的 SQL 语句
回顾一下，在 Management Studio 新建一个触发器的时候，它在查询分析对话框给预设了一些 SQL 代码，这些代码其实上就是建立触发器的语法提示。现在来看一下完整的触发器语法代码：
CREATE TRIGGER <Schema_Name, sysname, Schema_Name>.<Trigger_Name, sysname, Trigger_Name>
ON <Schema_Name, sysname, Schema_Name>.<Table_Name, sysname, Table_Name>
AFTER <Data_Modification_Statements, , INSERT,DELETE,UPDATE>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
END
GO
用中文改了一下，以上代码就一目了然了：
CREATE TRIGGER 触发器名
ON 数据表名或视图名
AFTER INSERT 或 DELETE 或 UPDATE
AS
BEGIN
-- 这里是要运行的 SQL 语句
END
GO
现在再对上面的代码进行进一步的说明：
l l CREATE TRIGGER 触发器名：这一句声明 SQL 语句是用来建立一个触发器。其中触发器名在所在的数据库里必须是唯一的。由于触发器是建立中数据表或视图中的，所以有很多人都 以为只要是在不同的数据表中，触发器的名称就可以相同，其实触发器的全名（ Server.Database.Owner.TriggerName ）是必须 唯一的，这与触发器在哪个数据表或视图无关。
l l ON 数据表名或视图名：这是指定触发器所在的数据表或视图，但是请注意，只有 Instead Of 触发器才能建立在视图上。并且，有设置为 With Check Option 的视图也不允许建立 Instead Of 触发器。
l l AFTER INSERT 或 DELETE UPDATE ：这是指定触发器的类型，是 After Insert 触发器，还是 After Delete 触发器，或者是 After Update 触发器。其中 After 可以用 For 来代取，它们的意思都是一样的，代表只有在数据表的操作都已正确完成后才会激活的触发器。 INSERT 、 DELETE 和 UPDATE 至少要指定一个，当然也可以指定多个，若指定多个时，必须用逗号来分开。其顺序可以任意摆放。
l l With Encryption ： With Encryption 是用来加密触发器的，放在 “On 数据表名或视图名 ” 的后面， “For” 的前面。如果使用了这句话，该触发器将会被加密，任何人都看不到触发器的内容了。
例一：以下是一个包含提醒电子邮件的触发器例子，如果订单表里记录有改动的的话（无论增加订单还是修改、删除订单），则给物流人员张三发送电子邮件：
CREATE TRIGGER 订单 _Insert
ON 订单
AFTER INSERT, UPDATE, DELETE
AS
EXEC master..xp_sendmail ' 张三 ',
' 订单有更改，请查询确定 '
GO
例二：在订单明细表里，折扣字段不能大于 0.6 ，如果插入记录时，折扣大于 0.6 的话，回滚操作。
CREATE TRIGGER 订单明细 _Insert
ON 订单明细
AFTER INSERT
AS
BEGIN
if (Select 折扣 from inserted)>0.6
begin
print ' 折扣不能大于 0.6'
Rollback Transaction
end
END
GO
在示例二中运用了两个方法，一个是前面说过的，在 Inserted 表里查询某个字段，还有一个是用 Rollback Transaction 来回滚操作。如果用下面的 SQL 语句来进行 Insert 操作的话，插入记录将会不成功。
INSERT INTO 订单明细 ( 订单 ID, 产品 ID, 单价 , 数量 , 折扣 )
VALUES (11077,1,18,1,0.7)
运行结果如图 11.7 所示：
图 11.7 插入记录不符合触发器里的约束，则回滚操作
11.6 设置 After 触发器的激活顺序
对于同一个操作，如 Insert 、 Update 或 Delete 来说，可以建立多个 After Insert 触发器，在 11.5.1 节中，已经建立了一个名为 “ 产品 _Insert” 的触发器，现在再建立一个 After Insert 触发器，作用也是输出一句有好提示，提示内容为： “ 再一次告诉你，你又添加了一种产品 ” 。
CREATE TRIGGER 产品 _Insert1
ON 产品
AFTER INSERT
AS
BEGIN
print ' 再一次告诉你，你又添加了一种产品 '
END
GO
重新运行一下插入产品的 SQL 语句：
INSERT INTO 产品 ( 产品名称 )
VALUES (' 大苹果 ')
如图 11.8 所示，运行一个 Insert 语句，在【消息】可以看到一共输出了两句话，说明激活两个不同的触发器。
图 11.8 一个语句激活两个触发器
当同一个操作定义的触发器越来越多的时候，触发器被激活的次序就会 变得越来越重要了。在 SQL Server 2005 里，用存储过程【 sp_settriggerorder 】可以为每一个操作各指定一个最先执行的 After 触发器和最后执行的 After 触发器。 sp_settriggerorder 语法如下：
sp_settriggerorder [ @triggername = ] '[ triggerschema. ] triggername'
, [ @order = ] 'value'
, [ @stmttype = ] 'statement_type'
[ , [ @namespace = ] { 'DATABASE' | 'SERVER' | NULL } ]
翻译成中文就是
sp_settriggerorder 触发器名 ,
激活次序 ,
激活触发器的动作
解释如下：
l 触发器名，要用单引号括起来，因为它是一个字符串。
l 激活次序可以为 First 、 Last 和 None ： First 是指第一个要激活的触发器； Last 是指它最后一个要激活的触发器； None 是不指激活序，由程序任意触发。
l 激活触发器的动作可以是： Insert 、 Update 和 Delete 。
上面的例子里，先激活的是【产品 _Insert 】触发器，后激活的是【产品 _Insert1 】触发器。如果把【产品 _Insert1 】触发器设为 First 触发器，把【产品 _Insert 】触发器设为 Last 触发器，那么结果将会完全不一样。设置语句如下：
Exec sp_settriggerorder
' 产品 _Insert1','First','Insert'
go
Exec sp_settriggerorder
' 产品 _Insert',’Last’,'Insert'
Go
重新运行一下插入产品的 SQL 语句：
INSERT INTO 产品 ( 产品名称 )
VALUES (' 大苹果 ')
运行结果如图 11.9 ，与图 11.8 比较一下，是不是激活次序已经发生变化了？
图 11.9 按次序激活的激活器
在设置 After 触发器激活顺序时，还有几点是需要注意的：
l 每个操作最多只能设一个 First 触发器和一个 Last 触发器。
l 如果要取消已经设好的 First 触发器或 Last 触发器，只要把它们设为 None 触发器即可。
l 如果用 Alter 命令修改过触发器内容后，该触发器会自动变成 None 触发器。所以用 Alter 命令也可以用来取消已经设好的 First 触发器或 Last 触发器。
l 只有 After 触发器可以设置激活次序， Instead Of 触发器不可以设置激活次序。
l 激活触发器的动作必须和触发器内部的激活动作一致。举例说明： After Insert 触发器，只能为 Insert 操作设置激活次序，不能为 Delete 操作设置激活次序。以下的设置是错误的：
Exec sp_settriggerorder
' 产品 _Insert1','First',’Update’
go
11.7 触发器的嵌套
当一个触发器执行时，能够触活另一个触发器，这种情况就是触发器的嵌套。在 SQL Server 2005 里，触发器能够嵌套到 32 层。
如果不想对触发器进行嵌套的话，可以通过【允许触发器激活其他触发器】的服务器配置选项来控制。但不管此设置是什么，都可以嵌套 Instead Of 触发器。设置触发器嵌套的选项更改方法为：
（ 1 ）打开 Management Studio ，在【对象资源管理】中，右击服务器名，并选择【属性】选项。
（ 2 ）单击【高级】节点。
（ 3 ）在【杂项】里设置【允许触发器激活其他触发器】为 True 或 False 。如图 11.10 所示：
图 11.10 开启 / 关闭触发器嵌套
现在，在 Northwind 数据库里建一个操作记录表，用来记录所有数据表的操作，无论是对哪个数据表进行了插入、更新或删除，都可以把操作内容和操作时间记录到操作记录表里。下面是建立操作记录表的 SQL 语句：
CREATE TABLE 操作记录表 (
编号 int IDENTITY(1,1) NOT NULL,
操作表名 varchar(50) NOT NULL,
操作语句 varchar(2000) NOT NULL,
操作内容 varchar(2000) NOT NULL,
操作时间 datetime NOT NULL
CONSTRAINT DF_ 操作记录表 _ 操作时间 DEFAULT (getdate()),
CONSTRAINT PK_ 操作记录表 PRIMARY KEY CLUSTERED
(
编号 ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
为了简便起见，在操作记录表里，只建一个 After Insert 触发器，触发器的作用是输入一条语句： “ 数据库又有记录变动了 ” 。在实际应用时，读者可自行修改成所需的代码。
CREATE TRIGGER 操作记录表 _Insert
ON 操作记录表
AFTER INSERT
AS
BEGIN
print ' 数据库又有记录变动了 '
END
GO
作为示例，只在类别表里建立一个 After Insert 触发器，当在类别表里插入一条记录的时候，该触发器向操作记录表里插入一条记录，而在操作记录表里插入记录时，将会触发操作记录表里的【操作记录表 _Insert 】触发器。
CREATE TRIGGER 类别 _Insert
ON 类别
AFTER INSERT
AS
BEGIN
Declare
@ 类别名称 nvarchar(15),
@ 说明 nvarchar(max)
set @ 类别名称 = (Select 类别名称 from inserted)
set @ 说明 = (Select 说明 from inserted)
INSERT INTO 操作记录表 ( 操作表名 , 操作语句 , 操作内容 )
VALUES (' 类别表 ',' 插入记录 ',' 类别名称： '+@ 类别名称 +', 说明： '+@ 说明 )
END
GO
现在运行一下对类别表的插入语句
INSERT INTO 类别 ( 类别名称 , 说明 )
VALUES (' 书籍 ',' 各类图书 ')
运行结果如图 11.11 所示：
图 11.11 触发器嵌套被激活
在【消息】对话框可以看到 “ 数据库又有记录变动了 ” ，这说明，触发器已经被嵌套激活了。如果把【允许触发器激活其他触发器】的选项设为 False ，再看看运行结果：
图 11.12 触发器嵌套没有被激活
如图 11.12 所示，现在没有 “ 数据库又有记录变动了 ” 的提示输出，说明嵌套的触发器没有被激活。
11.8 触发器的递归
触发器的递归是指，一个触发器从其内部又一次激活该触发器。例如一 个 Insert 触发器的内部还有一条对本数据表插入记录的 SQL 语句，那么这个插入语句就有可能再一次激活这个触发器本身。当然，这种递归的触发器内部还 会有判断语句，要一定的情况下才会执行那个 SQL 语句，否则的话，就会变成死循环了。
上面的例子说的是直接递归的触发器，还有一种是间接递归的触发器， 举例说明：当向 A 表插入一条记录时，激活了 A 表的 Insert 触发器， A 表的 Insert 触发器里有一个 SQL 语句是对 B 表进行 Insert 操作的，而在 B 表的 Insert 触发器里也有一句话是对 A 表进行 Insert 操作的。这样就是触发器的间接递归。
一般情况来说， SQL Server 服务器是不允许递归的，如果要打开触发器递归的功能，同样是将【允许触发器激活其他触发器】设为 True ，如图 11.10 所示。
11.9 设计 Instead Of 触发器
Instead Of 触发器与 After 触发器的工作流程是不一样的。 After 触发器是在 SQL Server 服务器接到执行 SQL 语句请求之后，先建立临时的 Inserted 表和 Deleted 表，然后实际更改数据，最后才激活触发器的。而 Instead Of 触发器看起来就简单多了，在 SQL Server 服务器接到执行 SQL 语句请求后，先建立临时的 Inserted 表和 Deleted 表，然后就触发了 Instead Of 触发器，至于那个 SQL 语句是插入数据、更新数据还是删除数据，就一概不管了，把执行权全权交给了 Instead Of 触发器，由它去完成之后的操作。
11.9.1 Instead Of 触发器的使用范围
Instead Of 触发器可以同时在数据表和视图中使用，通常在以下几种情况下，建议使用 Instead Of 触发器：
l l 数据库里的数据禁止修改：例如电信部门的通话记录是不能修改的，一旦修改，则通话费用的计数将不正确。在这个时候，就可以用 Instead Of 触发器来跳过 Update 修改记录的 SQL 语句。
l l 有可能要回滚修改的 SQL 语句：如 11.5.3 节中的例二，用 After 触发器并不是一个最好的方法，如果用 Instead Of 触发器，在判断折扣大于 0.6 时，就中止了更新操作，避免在修改数据之后再回滚操作，减少服务器负担。
l l 在视图中使用触发器：因为 After 触发器不能在视图中使用，如果想在视图中使用触发器，就只能用 Instead Of 触发器。
l l 用自己的方式去修改数据：如不满意 SQL 直接的修改数据的方式，可用 Instead Of 触发器来控制数据的修改方式和流程。
11.9.2 设计简单的 Instead Of 触发器
Instead Of 触发器的语法如下：
CREATE TRIGGER 触发器名
ON 数据表名或视图名
Instead Of INSERT 或 DELETE 或 UPDATE
AS
BEGIN
-- 这里是要运行的 SQL 语句
END
GO
从上面可以看得出， Instead Of 触发器与 After 触发器的语法几乎一致，只是简单地把 After 改为 Instead Of 。前面说过的 11.5.3 节中的例二，用 After 触发器并不是一个最好的方法，如果用 Instead Of 触发器，在判断折扣大于 0.6 时，就中止了更新操作，避免在修改数据之后再回滚操作，减少服务器负担。现将原来的触发器改为 Instead Of 触发器：
CREATE TRIGGER 订单明细 _Insert
ON 订单明细
Instead Of INSERT
AS
BEGIN
SET NOCOUNT ON;
declare
@ 订单 ID int,
@ 产品 ID int,
@ 单价 money,
@ 数量 smallint,
@ 折扣 real
set @ 订单 ID = (select 订单 ID from inserted)
set @ 产品 ID = (select 产品 ID from inserted)
set @ 单价 = (select 单价 from inserted)
set @ 数量 = (select 数量 from inserted)
set @ 折扣 = (select 折扣 from inserted)
if (@ 折扣 )>0.6
print ' 折扣不能大
11.10 查看 DML 触发器
查看已经设计好的 DML 触发器有两种方式，一种是通用 Management Studio 来查看，一种是利用系统存储过程来查看。
11.10.1 在 Management Studio 中查看触发器
在 Management Studio 中查看触发器的步骤：
（ 1 ）启动 Management Studio ，登录到指定的服务器上。
（ 2 ）在如图 11.13 所示界面的【对象资源管理器】下选择【数据库】，定位到要查看触发器的数据表上，并找到【触发器】项。
图 11.13 查看触发器列表
（ 3 ）单击【触发器】，在右边的【摘要】对话框里，可以看到已经建 好的该数据表的触发器列表。如果在点击【触发器】后，右边没有显示【摘要】对话框，可以在单击菜单栏上的【视图】菜单，选择【摘要】选项，打开【摘要】对 话框。如果在【摘要】对话框里没有看到本应存在的触发器列表，可以【摘要】对话框里右击空白处，在弹出的快捷菜单中选择【刷新】选项，刷新对话框后即可看 到触发器列表。
（ 4 ）双击要查看的触发器名， Management Studio 自动弹出一个【查询编辑器】对话框，对话框里显示的是该触发器的内容，如图 11.14 所示：
图 11.14 查看触发器内容
11.10.2 用系统存储过程查看触发器
SQL Server 2005 里已经建好了两个系统存储过程，可以用这两个系统存储过程来查看触发器的情况：
11.10.2 .1 sp_help ：
系统存储过程 “sp_help” 可以了解如触发器名称、类型、创建时间等基本信息，其语法格式为：
sp_help ‘ 触发器名 ’
举例：
sp_help ' 产品 _Insert'
运行结果如图 11.15 所示，可以看到触发器 “ 产品 _insert” 的基本情况。
图 11.15 查看触发器的基本情况
11.10.2 .2 sp_helptext ：
系统存储过程 “sp_helptext” 可以查看触发器的文本信息，其语法格式为：
sp_helptext ‘ 触发器名 ’
举例：
sp_helptext ' 产品 _Insert'
运行结果如图 11.16 所示，可以看到触发器 “ 产品 _insert” 的具体文本内容。
图 11.16 查看触发器的基本情况
于 0.6'
else
INSERT INTO 订单明细
( 订单 ID, 产品 ID, 单价 , 数量 , 折扣 )
VALUES
(@ 订单 ID,@ 产品 ID,@ 单价 ,@ 数量 ,@ 折扣 )
END
GO
上面的触发器里写入了一句 “SET NOCOUNT ON” ，这一句的作用是，屏蔽在触发器里 Insert 语句执行完之后返回的所影响行数的消息。
11.11 修改 DML 触发器
在 Management Studio 中修改触发器之前，必须要先查看触发器的内容，通过 11.10.1 节的第（ 1 ）步到第（ 4 ）步，细心的读者可以已经发现，如图 11.14 所 示，在【查询编辑器】对话框里显示的就是用来修改触发器的代码。编辑完代码之后，单击【执行】按钮运行即可。修改触发器的语法如下：
ALTER TRIGGER 触发器名
ON 数据表名或视图名
AFTER INSERT 或 DELETE 或 UPDATE
AS
BEGIN
-- 这里是要运行的 SQL 语句
END
GO
如果只要修改触发器的名称的话，也可以使用存储过程 “sp_rename” 。其语法如下：
sp_rename ‘ 旧触发器名 ’,’ 新触发器名 ’
值得一提的是修改触发器名称有可能会使某些脚本或存储过程运行出错。
11.12 删除 DML 触发器
在 Management Studio 中删除触发器，必须要先查到触发器列表，通过 11.10.1 节的第（ 1 ）步到第（ 3 ）步，可以查看到数据表下的所有触发器列表，右击其中一个 触发器，在弹出快捷菜单中选择【删除】选项，此时将会弹出【删除对象】对话框，在该对话框中单击【确定】按钮，删除操作完成。用以下 SQL 语句也对可删除 触发器：
Drop Trigger 触发器名
注意：如果一个数据表被删除，那么 SQL Server 会自动将与该表相关的触发器删除。
11.13 禁用与启用 DML 触发器
禁用触发器与删除触发器不同，禁用触发器时，仍会为数据表定义该触发器，只是在执行 Insert 、 Update 或 Delete 语句时，除非重新启用触发器，否则不会执行触发器中的操作。
在 Management Studio 中禁用或启用触发器，也必须要先查到触发器列表，触发器列表里，右击其中一个触发器，在弹出快捷菜单中选择【禁用】选项，即可禁用该触发器。启用触发器与上类似，只是在弹出快捷菜单中选择【启用】选项即可。
用以下 Alter Table 语句也禁用或启用触发器，其语法如下：
Alter table 数据表名
Disable 或 Enable trigger 触发器名或 ALL
用 Disable 可以禁用触发器，用 Enable 可以启用触发器；如果要禁用或启用所有触发器，用 “ALL” 来代替触发器名。
11.14 2005 新增功能： DDL 触发器
DDL 触发器是 SQL Server 2005 新增的一个触发器类型，是一种特殊的触发器，它在响应数据定义语言（ DDL ）语句时触发。一般用于数据库中执行管理任务。
与 DML 触发器一样， DDL 触发器也是通过事件来激活，并执行其中 的 SQL 语句的。但与 DML 触发器不同， DML 触发器是响应 Insert 、 Update 或 Delete 语句而激活的， DDL 触发器是响应 Create 、 Alter 或 Drop 开头的语句而激活的。一般来说，在以下几种情况下可以使用 DDL 触发器：
l l 数据库里的库架构或数据表架构很重要，不允许被修改。
l l 防止数据库或数据表被误操作删除。
l l 在修改某个数据表结构的同时修改另一个数据表的相应的结构。
l l 要记录对数据库结构操作的事件。
11.15 2005 新增功能：设计 DDL 触发器
只要注意到 DDL 触发器和 DML 触发器的区别，设计 DDL 触发器与设计 DML 触发器也很类似，下面详细讲述一下要怎么去设计一个 DDL 触发器。
11.15.1 建立 DDL 触发器的语句
建立 DDL 触发器的语法代码如下：
CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH <ddl_trigger_option> [ ,...n ] ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement [ ; ] [ ...n ] | EXTERNAL NAME < method specifier > [ ; ] }
用中文取代一下英文可以看得更明白：
CREATE TRIGGER 触发器名
ON ALL SERVER 或 DATABASE
FOR 或 AFTER
激活 DDL 触发器的事件
AS
要执行的 SQL 语句
其中：
l ON 后面的 All Server 是将 DDL 触发器作用到整个当前的服务器上。如果指定了这个参数，在当前服务器上的任何一个数据库都能激活该触发器。
l ON 后面的 Database 是将 DDL 触发器作用到当前数据库，只能在这个数据库上激活该触发器。
l For 或 After 是同一个意思，指定的是 After 触发器， DDL 触发器不能指定的 Stead Of 触发器。
l 激活 DDL 触发器的事件包括两种，在 DDL 触发器作用在当前数据库情况下可以使用以下事件：
| CREATE_APPLICATION_ROLE | ALTER_APPLICATION_ROLE | DROP_APPLICATION_ROLE |
| CREATE_ASSEMBLY | ALTER_ASSEMBLY | DROP_ASSEMBLY |
| ALTER_AUTHORIZATION _DATABASE |
|
|
| CREATE_CERTIFICATE | ALTER_CERTIFICATE | DROP_CERTIFICATE |
| CREATE_CONTRACT | DROP_CONTRACT |
|
| GRANT_DATABASE | DENY_DATABASE | REVOKE_DATABASE |
| CREATE_EVENT_NOTIFICATION | DROP_EVENT_NOTIFICATION |
|
| CREATE_FUNCTION | ALTER_FUNCTION | DROP_FUNCTION |
| CREATE_INDEX | ALTER_INDEX | DROP_INDEX |
| CREATE_MESSAGE_TYPE | ALTER_MESSAGE_TYPE | DROP_MESSAGE_TYPE |
| CREATE_PARTITION_FUNCTION | ALTER_PARTITION_FUNCTION | DROP_PARTITION_FUNCTION |
| CREATE_PARTITION_SCHEME | ALTER_PARTITION_SCHEME | DROP_PARTITION_SCHEME |
| CREATE_PROCEDURE | ALTER_PROCEDURE | DROP_PROCEDURE |
| CREATE_QUEUE | ALTER_QUEUE | DROP_QUEUE |
| CREATE_REMOTE_SERVICE _BINDING | ALTER_REMOTE_SERVICE _BINDING | DROP_REMOTE_SERVICE _BINDING |
| CREATE_ROLE | ALTER_ROLE | DROP_ROLE |
| CREATE_ROUTE | ALTER_ROUTE | DROP_ROUTE |
| CREATE_SCHEMA | ALTER_SCHEMA | DROP_SCHEMA |
| CREATE_SERVICE | ALTER_SERVICE | DROP_SERVICE |
| CREATE_STATISTICS | DROP_STATISTICS | UPDATE_STATISTICS |
| CREATE_SYNONYM | DROP_SYNONYM | CREATE_TABLE |
| ALTER_TABLE | DROP_TABLE |
|
| CREATE_TRIGGER | ALTER_TRIGGER | DROP_TRIGGER |
| CREATE_TYPE | DROP_TYPE |
|
| CREATE_USER | ALTER_USER | DROP_USER |
| CREATE_VIEW | ALTER_VIEW | DROP_VIEW |
| CREATE_XML_SCHEMA _COLLECTION | ALTER_XML_SCHEMA _COLLECTION | DROP_XML_SCHEMA _COLLECTION |
在 DDL 触发器作用在当前服务器情况下，可以使用以下事件：
| ALTER_AUTHORIZATION_SERVER |
|
|
| CREATE_DATABASE | ALTER_DATABASE | DROP_DATABASE |
| CREATE_ENDPOINT | DROP_ENDPOINT |
|
| CREATE_LOGIN | ALTER_LOGIN | DROP_LOGIN |
| GRANT_SERVER | DENY_SERVER | REVOKE_SERVER |
例三，建立一个 DDL 触发器，用于保护数据库中的数据表不被修改，不被删除。具体操作步骤如下：
（ 1 ）启动 Management Studio ，登录到指定的服务器上。
（ 2 ）在如图 11.1 所示界面的【对象资源管理器】下选择【数据库】，定位到【 Northwind 】数据库上。
（ 3 ）单击【新建查询】按钮，在弹出的【查询编辑器】的编辑区里输入以下代码：
CREATE TRIGGER 禁止对数据表操作
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
PRINT ' 对不起，您不能对数据表进行操作 '
ROLLBACK ;
（ 4 ）单击【执行】按钮，生成触发器。
例四，建立一个 DDL 触发器，用于保护当前 SQL Server 服务器里所有数据库不能被删除。具体代码如下：
CREATE TRIGGER 不允许删除数据库
ON all server
FOR DROP_DATABASE
AS
PRINT ' 对不起，您不能删除数据库 '
ROLLBACK ;
GO
例五，建立一个 DDL 触发器，用来记录数据库修改状态。具体操作步骤如下：
（ 1 ）建立一个用于记录数据库修改状态的表：
CREATE TABLE 日志记录表 (
编号 int IDENTITY(1,1) NOT NULL,
事件 varchar(5000) NULL,
所用语句 varchar(5000) NULL,
操作者 varchar(50) NULL,
发生时间 datetime NULL,
CONSTRAINT PK_ 日志记录表 PRIMARY KEY CLUSTERED
(
编号 ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
（ 2 ）建立 DDL 触发器：
CREATE TRIGGER 记录日志
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @log XML
SET @log = EVENTDATA()
INSERT 日志记录表
( 事件 , 所用语句 , 操作者 , 发生时间 )
VALUES
(
@log.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@log.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'),
CONVERT(nvarchar(100), CURRENT_USER),
GETDATE()
) ;
GO
其中 Eventdata 是个数据库函数，它的作用是以 XML 格式返回有关服务器或数据库事件的信息。 @log.value 是返回 log 这个 XML 结点的值，结点的位置是括号里的第一个参数。
11.15.2 测试触发器功能
现在测试一下在上一章节中建立好的三个触发器的功能。下面所有的测试都是在【查询编辑器】对话框里进行的，要打开【查询编辑器】对话框，只要单击 Management Studio 里【新建查询】按钮即可。
测试例三：例三是保证【 Northwind 】数据库里不能删除表和修改表，在【查询编辑器】对话框里输入一个删除表的 SQL 语句：
Drop table 操作记录表
运行结果如图 11.17 所示：
图 11.17 不允许删除表格
测试例四：例四是保证当前服务器里的所有数据库不能被删除，在【查询编辑器】对话框里输入一个删除数据库的 SQL 语句：
Drop DataBase test
运行结果如图 11.18 所示：
图 11.18 不允许删除数据库
测试例五：例五是记录对【 Northwind 】所进行的操作，在【查询编辑器】对话框里输入一条添加数据表和一条删除数据表的 SQL 语句，然后再用 Select 语句查看【目志记录表】数据表里所有的记录：
CREATE TABLE 测试表 (
编号 int IDENTITY(1,1) NOT NULL,
测试内容 varchar(50) NOT NULL)
GO
Drop table 测试表
GO
select * from 日志记录表
GO
运行时不要忘了，前面曾经建立过一个不能删除数据表的触发器，要先把它禁用或删除。运行结果如图 11.19 所示：
图 11.19 记录对数据库的操作
11.16 2005 新增功能：查看与修改 DDL 触发器
DDL 触发器有两种，一种是作用在当前 SQL Server 服务器上的，一种是作用在当前数据库中的。这两种 DDL 触发器在 Management Studio 中所在的位置是不同的。
l 作用在当前 SQL Server 服务器上的 DDL 触发器所在位置是：【对象资源管理器】，选择所在 SQL Server 服务器，定位到【服务器对象】 à 【触发器】，在【摘要】对话框里就可以看到所有的作用在当前 SQL Server 服务器上的 DDL 触发器。
l 作用在当前数据库中的 DDL 触发器所在位置是：【对象资源管理器】，选择所在 SQL Server 服务器，【数据库】，所在数据库，定位到【可编程性】 à 【数据库触发器】，在摘要对话框里就可以看到所有的当前数据库中的 DDL 触发器。
右击触发器，在弹出的快捷菜单中选择【编写数据库触发器脚本为】 à 【 CREATE 到】 à 【新查询编辑器对话框】，然后在新打开的【查询编辑器】对话框里可以看到该触发器的内容。
在 Management Studio 如果要修改 DDL 触发器内容，就只能先删除该触发器，再重新建立一个 DDL 触发器。
虽然在 Management Studio 中没有直接提供修改 DDL 触发器的对话框，但在【查询编辑器】对话框里依然可以用 SQL 语句来进行修改。下面给出几个对 DDL 触发器操作常用 的 SQL 代码，由于对 DDL 触发器的操作和对 DML 触发器的操作类似，因此不再详细说明用法。
l l 创建 DDL 触发器
CREATE TRIGGER (Transact-SQL)
l l 删除 DDL 触发器
DROP TRIGGER (Transact-SQL)
l l 修改 DDL 触发器
ALTER TRIGGER (Transact-SQL)
l l 重命名 DDL 触发器
sp_rename (Transact-SQL)
l l 禁用 DDL 触发器
DISABLE TRIGGER (Transact-SQL)
l l 启用 DDL 触发器
ENABLE TRIGGER (Transact-SQL)
l l 删除 DDL 触发器
DROP TRIGGER (Transact-SQL)
11.17 触发器的应用技巧
触发器的使用范围很广，使用的频率也很高，触发器的应用技巧也层出不穷，下面介绍一些在触发器里常用的技巧，希望可以做到抛砖引玉之功效。
11.17.1 如何知道触发器修改了多少条记录
需要注意的是，一种操作类型（ Insert 、 Update 或 Delete ）虽然可以激活多 个触发器，但是每个操作类型在一次操作时，对一个触发器只激活一次。例如，运行一个 Update 语句，有可能一次更新了十条记录，但是对于 After Update 这个触发器，只激活一次，而不是十次。但是在 Inserted 表和 Deleted 表里会有十条记录，这个时候，只要利用 @@Rowcount 这个系统变量就可以得知更新了多少条记录。例如：
CREATE TRIGGER 订单明细删除 _test
ON 订单明细
AFTER DELETE
AS
BEGIN
print ' 您此次删除了 ' + Cast(@@rowcount as varchar) + ' 条记录 '
END
GO
Delete FROM 订单明细 where 折扣 =0.25
GO
Delete FROM 订单明细 where 订单 ID='123456789'
GO
这里先是建立了一个名为 “ 订单明细删除 _test” 的触发器，作用就是显示删除了多少条记录。之后执行两个 SQL 语句，一个是删除折扣为 0.25 的记录，一个是删除订单 ID 号为 123456789 的记录，这条记录是不存在的。运行结果如图 11.20 所示：
图 11.20 显示删除的记录数
在图 11.20 可以看出，用系统变量 @@rowcount 可以获得删除记录的条数。另外，在图中还可以看出，虽然第二个 SQL 语句删除的记录数为零，但是触发器还是被激活了。因此可以知道，触发器只与激活它的类型有关，与具体操作的记录数无关。
11.17.2 如何知道插入记录的自动编号是多少
在第 11.7 节，触发器的嵌套里，【类别】数据表设计了一个触发 器，当在【类别】数据表里插入一件记录的时候，将会在【操作记录表】里也插入一条记录，用来记录具体的插入操作的，其实这个触发器还可以写得更好，不但可 以记录插入操作所用的 SQL 语句，还可以记录下当时插入记录时候，数据库为这个记录自动生成编号是多少，为以后的操作提供更大的便利。修改该触发器的代码 如下：
ALTER TRIGGER 类别 _Insert
ON 类别
AFTER INSERT
AS
BEGIN
Declare
@ 类别名称 nvarchar(15),
@ 说明 nvarchar(max)
set @ 类别名称 = (Select 类别名称 from inserted)
set @ 说明 = (Select 说明 from inserted)
INSERT INTO 操作记录表 ( 操作表名 , 操作语句 , 操作内容 )
VALUES (' 类别表 ',' 插入记录 ',
' 插入了 ID 号为 '+cast(@@IDENTITY as varchar)+' 的记录：类别名称： '
+@ 类别名称 +', 说明： '+@ 说明 )
END
GO
从上面的代码可以看出，用 @@IDENTITY 可以获得刚插入记录的标识值，在本例中是它的主键值。插入记录后，在【操作记录表】里可以详细查看到插入的记录的编号以及它的内容。
11.17.3 如何知道某个字段是否被修改
在 Update 触发器和 Insert 触发器里，可以用 “Update( 字段名 )” 来判断某个字段是不是被更改，返回的是一个布尔值。例如定单生成后，只能修改折扣的触发器：
CREATE TRIGGER 只允许修改折扣
ON 订单明细
Instead Of UPDATE
AS
BEGIN
SET NOCOUNT ON;
if update( 折扣 )
begin
declare
@ 订单 ID int,
@ 产品 ID int,
@ 折扣 real
set @ 订单 ID = (select 订单 ID from inserted)
set @ 产品 ID = (select 产品 ID from inserted)
set @ 折扣 = (select 折扣 from inserted)
update 订单明细 set 折扣 =@ 折扣
where 订单 ID=@ 订单 ID and 产品 ID=@ 产品 ID
end
else
begin
print ' 只能更改折扣字段 '
end
END
GO
update 订单明细 set 折扣 =0.2
where 订单 ID=10288 and 产品 ID=54
Go
update 订单明细 set 订单 ID=10288
where 订单 ID=10288 and 产品 ID=54
Go
上面的代码，先建立了一个触发器，只有修改了折扣字段的 Update 语句才会被执行。然后写了两个 Update 的 SQL 语句，一个是修改了折扣字段的，一个是没有修改折扣字段的。运行后的结果如图 11.21 所示。第一个 SQL 语句被正确执行，第二个 SQL 语句没有被执行。
图 11.21 用 Update 判断字段是否被修改
11.17.4 如何返回错误信息
虽然上面介绍触发器时，用过很多次 Print 来输出自定义的信息，但是实际上，只有在用【查询编辑器】中运行 SQL 语句才能看得到这些自定义的信息，而其他的前端应用程序都不会显示出这些自定义的信息，包括用 Management Studio 也一样。
读者可以自行测试一下，在 Management Studio 里打开【订单明细】数据表，因为上面建了一个【只允许修改折扣】的触发器，所以只要在不是折扣的字段里修改数据后，再将鼠标聚焦到其他记录上 时，被修改的数据马上就会回滚到修改前的状态，在这个过程中，几乎是看不到什么提示的。如果想要在这个过程中看到提示的话，就要将触发器修改一下，加上 “Raiserror” 语句，具体修改代码如下：
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER TRIGGER 只允许修改折扣
ON 订单明细
Instead Of UPDATE
AS
BEGIN
SET NOCOUNT ON;
if update( 折扣 )
begin
declare
@ 订单 ID int,
@ 产品 ID int,
@ 折扣 real
set @ 订单 ID = (select 订单 ID from inserted)
set @ 产品 ID = (select 产品 ID from inserted)
set @ 折扣 = (select 折扣 from inserted)
update 订单明细 set 折扣 =@ 折扣
where 订单 ID=@ 订单 ID and 产品 ID=@ 产品 ID
end
else
begin
print ' 只能更改折扣字段 '
Raiserror(' 除了折扣字段之外的其他字段信息不能修改 ',16,5)
end
END
修改完触发器之后，再去修改其他非 “ 折扣 ” 字段的内容时，就会弹出错误提示，如图 11.22 所示， Raiserror 的用法可以查看 SQL Server 2005 的帮助。
图 11.22 显示错误信息
11.18 小结
触发器是与数据库和数据表相结合的特殊的存储过程，当数据表有 Insert 、 Update 、 Delete 操作或数据库有 Create 、 Alter 、 Drop 操作的时候，可以激活触发器，并运行其中的 T-SQL 语句。
在 SQL Server 2005 中触发器分为 DML 触发器和 DDL 触发器两种。其中 DML 触发器又分为 After 触发器和 Instead Of 触发器两种。 After 触发器是先修改记录后激活的触发器； Instead Of 触发器是 “ 取代 ” 触发器。 DDL 触发器根据作用范围可以分为作用在数据库的触发器和作用在服务器的触发器两种。 After 触发器只能用于数据表中，而 Instead Of 触发器即可以用在数据表中，也可以用在视图中。
使用 CREATE TRIGGER 语句可以创建触发器，使用 ALTER TRIGGER 语句可以修改触发器，使用 Drop Trigger 语句可以删除触发器。触发器允许嵌套和递归，嵌套最多可以是 32 层。
如何实现 SQLServer 行级触发器
ⅰ Deleted 表和 Inserted 表
SQL SERVER 只有表级和服务器级（相当）的触发器 ，在很多需要使用行级触发器的场合，很多同事都开始怀念使用 Oracle 的日子。但是事实上，我认为 SQL SERVER 这样做有它的理由（或难处），甚至于很多时候我会说，我们不需要精细到以行来触发的操作，因为我们往往要做的是处理哪些数据而不是哪条数据。不过如果您真的很需要，那么 SQL SERVER 提供了相应的手段足以让您实现类似的功能，并且应有更大的灵活性。
对于 SQL SERVER ，一个添修删操作对应于确定的触发器只会作用一次，而不管在这个操作中有多少纪录受到了该次操作的影响，因为它是语句级的。但这并不意味着我们需要去遍历比较整个表才可以得知哪些记录发生了改变。在 DML 触发器开发中，我们可以通过 Deleted 表和 Inserted 表得到激活当前触发器的操作中那些受到影响的记录。
对于这两个表，我们可以这样理解。在一个添修删操作过程中， SQL 引擎生成了这两个临时表 。他把删除掉的数据暂时存储在 Deleted 表内，把要追加的数据存储在 Inserted 表内。而对于修改来说，我们把他理解为删除旧记录和加入新记录两个步骤，那么就是说他在两个表内分别存储了目标记录修改前后的数据。
想要检验这种猜测的合理性，我们不妨用下面的例子试试看。
USE [myTestDataBase]
-- 初始化
IF OBJECT_ID ('tbStudents', 'TABLE') IS NOT NULL
DROP TABLE [tbStudents]
GO
IF OBJECT_ID ('tbStudentsLog', 'TABLE') IS NOT NULL
DROP TABLE [tbStudentsLog]
GO
IF OBJECT_ID ('TR_StudentNameChanged', 'TR') IS NOT NULL
DROP TRIGGER TR_StudentNameChanged
GO
-- 学生表 ( 包含学号和姓名两个字段 )
CREATE TABLE [dbo].[tbStudents](
[StudentID] [nchar](10) COLLATE Japanese_CI_AS NOT NULL,
[StudentName] [nchar](10) COLLATE Japanese_CI_AS NULL,
CONSTRAINT [PK_tbStudents] PRIMARY KEY CLUSTERED ( [StudentID] ASC )
)
GO
-- 学生记录表 ( 包含学号、现在姓名、曾用名三个字段 )
CREATE TABLE [dbo].[tbStudentsLog](
[StudentID] [nchar](10) COLLATE Japanese_CI_AS NOT NULL,
[StudentNewName] [nchar](10) COLLATE Japanese_CI_AS NULL,
[StudentOldName] [nchar](10) COLLATE Japanese_CI_AS NULL,
CONSTRAINT [PK_tbStudentsLog] PRIMARY KEY CLUSTERED ( [StudentID] ASC )
)
GO
-- 定义 [tbStudents] 表被修改时的触发器
CREATE TRIGGER TR_StudentNameChanged
ON [tbStudents]
AFTER UPDATE
AS
-- 将被修改学生记录的修改前后的值存入学生记录
INSERT [tbStudentsLog]
SELECT [Inserted]. [StudentID]
, [Inserted].[ StudentName] AS [StudentNewName]
, [Deleted]. [StudentName] AS [StudentOldName]
FROM [Inserted]
INNER JOIN [Deleted] ON [Deleted].[StudentID] = [Inserted].[StudentID]
WHERE NOT EXISTS(
SELECT *
FROM [tbStudentsLog]
WHERE [tbStudentsLog].[StudentID] = [Inserted].[StudentID])
GO
-- 加入两个学生
INSERT [dbo].[tbStudents] VALUES ('0000000001',' 小臭 ')
INSERT [dbo].[tbStudents] VALUES ('0000000002',' 小美 ')
-- 检查学生和学生纪录
SELECT * FROM [dbo].[tbStudents]
SELECT * FROM [dbo].[tbStudentsLog]
-- 修改一个学生的姓名
UPDATE [dbo].[tbStudents] SET [StudentName] = ' 大红花 ' WHERE [StudentID] = '0000000001'
-- 验证触发器地执行结果
SELECT * FROM [dbo].[tbStudents]
SELECT * FROM [dbo].[tbStudentsLog]
执行的结果：
我们把修改学生的姓名的 SQL 文的 WHERE 子句删除掉，替换成下面的语句再试试看
-- 修改所有学生的姓名
UPDATE [dbo].[tbStudents] SET [StudentName] = ' 全改掉 '
执行后的结果是
ⅱ实现行级触发器
为了实现行触发器，我们需要动用 SQL 游标，但是在触发器内一般的做法是不建议使用游标。因此这里只记录下这个思路，如果您真的要实现它，那么我想不是多么复杂。但是我想，一个程序员一旦应有了这种自主性，那么它往往就不会真的就只在这个触发器内模拟一个行级触发的功能吧。或许有一天我会真的需要它，那时候再来补上这段代码吧。
DROP TRIGGER EVENT_CREATE ;
DROP TRIGGER EVENT_UPDATE ;
DROP TRIGGER EVENT_DELETE ;
CREATE TRIGGER event_create
ON CUSTOMER
FOR INSERT
AS
DECLARE @newKey VARCHAR ( 10 );
DECLARE l_cursor CURSOR FAST_FORWARD FOR SELECT pkey FROM inserted ;
OPEN l_cursor ;
FETCH NEXT FROM l_cursor INTO @newKey ;
WHILE @@fetch_status = 0
BEGIN
INSERT INTO wbia_jdbc_eventstore ( object_key , object_name , object_function , event_priority , event_status , connector_ID )
VALUES ( @newKey , 'SchemaNameCustomerBG' , 'Create' , 1 , 0 , '001' );
FETCH NEXT FROM l_cursor INTO @newKey ;
END ;
CLOSE l_cursor ;
DEALLOCATE l_cursor ;
CREATE TRIGGER event_update
ON CUSTOMER
FOR UPDATE
AS
DECLARE @updatedKey VARCHAR ( 10 );
DECLARE l_cursor CURSOR FAST_FORWARD FOR SELECT pkey FROM inserted ;
OPEN l_cursor ;
FETCH NEXT FROM l_cursor INTO @updatedKey ;
WHILE @@fetch_status = 0
BEGIN
INSERT INTO wbia_jdbc_eventstore ( object_key , object_name , object_function , event_priority , event_status , connector_ID )
VALUES ( @updatedKey , 'SchemaNameCustomerBG' , 'Update' , 1 , 0 , '001' );
FETCH NEXT FROM l_cursor INTO @updatedKey ;
END ;
CLOSE l_cursor ;
DEALLOCATE l_cursor ;
CREATE TRIGGER event_delete
ON CUSTOMER
FOR DELETE
AS
DECLARE @deletedKey VARCHAR ( 10 );
DECLARE l_cursor CURSOR FAST_FORWARD FOR SELECT pkey FROM Deleted ;
OPEN l_cursor ;
FETCH NEXT FROM l_cursor INTO @deletedKey ;
WHILE @@fetch_status = 0
BEGIN
INSERT INTO wbia_jdbc_eventstore ( object_key , object_name , object_function , event_priority , event_status , connector_ID )
VALUES ( @deletedKey , 'SchemaNameCustomerBG' , 'Delete' , 1 , 0 , '001' );
FETCH NEXT FROM l_cursor INTO @deletedKey ;
END ;
CLOSE l_cursor ;
DEALLOCATE l_cursor ;
DB2 触发器 :
The CREATE TRIGGER Statement
Triggers are database objects associated with a table or a view to define operations that should occur automatically upon an INSERT, UPDATE, or DELETE operation (hereafter called the triggering SQL statement) on that table or view. Operations performed by triggers occur within the database engine and are therefore transparent to the application.
There is often application logic (or rules) that should always be enforced across all applications. These rules may exist because data in one table may be related to data in others. If you have many applications that share a set of tables, it can be cumbersome to ensure that all applications follow and enforce these logic rules consistently and properly. To compound the problem, if the logic or rules change, application code changes are required for all affected applications.
Triggers can solve this problem by moving logic from the application level to the database level so that all applications share the same code which enforces these rules. If there is ever a change in the rules, you only need to change the trigger definitions in the database, and all applications will follow the new rules without requiring any additional changes.
Here are some examples of how triggers might be used:
· When inserting, triggers can be used to supply, validate, or manipulate data before allowing an insert operation to occur.
· When updating, triggers can be used to compare the new value (supplied by the UPDATE statement) with the existing value in the table to ensure that the transition from old value to new value follows proper state transitions. For example, a trigger could be created to allow a column value to change from NO to YES only and not vice versa.
· Upon deletion, triggers can be used to automatically insert logging information into another table for audit trail purposes.
On LUW, the INSTEAD OF triggers can be used to insert, update, or delete from views where these operations are otherwise not allowed. The views are not insertable, updatable, or deletable because the columns of the views cannot be automatically mapped to underling table columns. However, if you know your business logic and you know how the changes in views can be mapped to changes in underlying tables, you can put the logic into the body of INSTEAD OF triggers and use INSTEAD OF triggers to work around SQL limits.
Tip
On LUW, use INSTEAD OF triggers to support insert, update, or delete statements through the views, which are otherwise not insertable, updatable, or deletable.
The complete syntax of the CREATE TRIGGER statement is shown in Figure 9.16 .
Figure 9.16. The complete CREATE TRIGGER statement syntax for LUW.
>>-CREATE TRIGGER--trigger-name--+-NO CASCADE BEFORE-+---------->
+-AFTER-------------+
'-INSTEAD OF--------'
>--+-INSERT-------------------------+--ON--+-table-name-+------->
+-DELETE-------------------------+
'-view-name--'
'-UPDATE--+--------------------+-'
|
.-,-----------. |
|
V
| |
'-OF---column-name-+-'
>--+------------------------------------------------------------------+-->
|
.-------------------------------------------------. |
|
V
.-AS-.
| |
'-REFERENCING------------------+-OLD--+----+--correlation-name-+-+-'
|
.-AS-.
|
+-NEW--+----+--correlation-name-+
|
.-AS-.
|
+-OLD_TABLE--+----+--identifier--+
|
.-AS-.
|
'-NEW_TABLE--+----+--identifier--'
>--+-FOR EACH ROW--------------+--MODE DB2SQL------------------->
'-FOR EACH STATEMENT--------'
>--| triggered-action |----------------------------------------><
triggered-action:
|--+-------------------------------------+---------------------->
'--------WHEN--(--search-condition--)-'
>--+---------+----SQL-procedure-statement-----------------------|
'--label--'
Trigger Name
The unqualified trigger name specified in the CREATE TRIGGER statement is limited to 18 characters for LUW and 128 characters for iSeries and zSeries. It must begin with a letter, followed by zero or more letters, digits, or underscore characters (_).
A trigger is said to be qualified if it is referenced by a two-part name that is made up of a schema and a trigger name. The schema name limitation of triggers is the same as that of SQL procedures. The qualified trigger name, including the implicit or explicit schema name, must be unique.
BEFORE, AFTER, or INSTEAD OF
DB2 supports triggers defined on tables on all platforms, and triggers defined on views for LUW only. The view trigger is also known as the INSTEAD OF trigger. There are two types of table triggers: BEFORE and AFTER triggers. When a trigger begins its execution because of a triggering SQL statement, the trigger is said to be activated.
A BEFORE trigger will be activated before any table data is affected by the triggering SQL statement. You would use BEFORE triggers to intercept data provided by the application to validate or supply missing values, for example. If the triggering SQL statement affects more than one row, the BEFORE trigger will be activated for every row that is affected.
A BEFORE trigger is always defined with the NO CASCADE clause . This means that operations performed within this trigger do not activate other triggers in the database . This has the implicit restriction that no INSERT, UPDATE, or DELETE statements are allowed in the body of BEFORE triggers . If you want to perform INSERT, UPDATE, or DELETE statements in a trigger body, you must define them as AFTER triggers.
Tip
NO CASCADE is optional and is the default behavior on iSeries. Always specify NO CASCADE for BEFORE triggers for portability.
AFTER triggers are activated after the triggering SQL statement has executed to completion successfully. In general, you would use AFTER triggers to post-process data. You would also use AFTER triggers if your logic required you to perform any INSERT, UPDATE, or DELETE operations because these statements are not supported in BEFORE triggers. AFTER triggers also offer additional options in how they can behave. With AFTER triggers, you can optionally define them to activate on a per-statement basis rather than on a per-row basis. This topic is discussed further in the upcoming sections in this chapter.
On LUW, an INSTEAD OF trigger is activated after the triggering SQL statement has been issued to the base view. A fundamental difference exists between a table trigger and a view trigger. For a table trigger, regardless of a BEFORE or an AFTER trigger, both the trigger actions and the triggering statements are executed eventually. For a view trigger, also known as an INSTEAD OF trigger, the trigger action is used to replace the triggering statement. In other words, the trigger action is executed instead of the triggering statement. The triggering statement is only attempted on the base view and is only used to fire the INSTEAD OF trigger. The triggering statement will never be executed.
INSERT, DELETE, or UPDATE
Triggers can be defined for INSERT, DELETE, or UPDATE triggering statements . On LUW and zSeries, there is no limit to how many triggers you can define on a table. On iSeries, the limit is 300. However, it is not a good idea to define too many triggers. When more than one trigger is defined for one type of triggering statementfor example, for an INSERT statementthey are fired in the order of their creation.
Tip
Consolidate your triggers into one if the result depends on the trigger firing sequence. You can explicitly control the sequences of the trigger actions within one trigger. The firing sequence can be changed when some triggers involved are rebuilt in a different order.
On iSeries, a DELETE trigger cannot be created on a table that has a referential constraint of ON DELETE SET NULL . Similarly, an UPDATE trigger cannot be created on a table that has a referential constraint of ON UPDATE SET NULL or ON UPDATE SET DEFAULT.
On LUW, you can only define one INSTEAD OF TRigger for each type of triggering statements on each view. In other words, you can only define one INSTEAD OF delete trigger, one INSTEAD OF insert trigger, and one INSTEAD OF update trigger for a view.
Tip
If you need to create more than one INSTEAD OF trigger for one type of triggering statement (INSERT, UPDATE, or DELETE), you can create two identical views with different names.
You should always try to specify the column name list for UPDATE triggers. If the optional column name list is not specified in an UPDATE trigger, every column of the table is implied. Omission of the column-name list implies that the trigger will be activated by the update of any column of the table. The column-name list is not supported in INSTEAD OF triggers.
Tip
Define an optional column-name list for your UPDATE triggers to prevent your triggers being activated unnecessarily.
REFERENCING Clauses
REFERENCING NEW is used to define a qualifier to reference transition values supplied by INSERT and UPDATE statements. REFERENCING OLD is used to define a qualifier to reference transition data that will be discarded by UPDATE and DELETE statements. REFERENCING OLD_TABLE specifies a transition table name that identifies the set of affected rows prior to the triggering SQL operation. REFERENCING NEW_TABLE specifies a transition table name that identifies the affected rows as modified by the triggering SQL operation and by any SET statement in a BEFORE trigger that has already executed. This option can be referred to in AFTER triggers only.
FOR EACH ROW or FOR EACH STATEMENT
Triggers defined with FOR EACH ROW will fire once for each row. Both BEFORE and AFTER TRiggers support activation FOR EACH ROW.
Triggers defined with FOR EACH STATEMENT will fire once for each statement only, no matter how many rows are affected in that statement. Only AFTER triggers can be defined to activate FOR EACH STATEMENT. For update and delete triggers defined with FOR EACH STATEMENT, they will be activated even if no rows are affected by the triggering UPDATE or DELETE statement. For example, if a single delete statement results in 1,000 rows deleted, an AFTER/FOR EACH STATEMENT trigger defined on that table will activate just once at the end of the entire delete operation. On the other hand, if the trigger was defined as AFTER/FOR EACH ROW, it will activate 1,000 times, once for each row affected by the triggering SQL statement.
MODE DB2SQL
MODE DB2SQL is simply a clause that must be included as part of the CREATE TRIGGER syntax. DB2SQL is the only mode currently supported on LUW and zSeries. For iSeries, mode DB2ROW is also supported. Triggers on iSeries defined with mode DB2ROW are activated on each row operation whereas triggers defined with mode DB2SQL are activated after all of the row operations have been activated.
Triggered Action
On LUW, the SQL procedure statements in the body of the triggered action are implemented by inline SQL PL. The previous sections in this chapter on user-defined SQL functions have already explained the inline SQL PL and its limitations. For more information, refer to Appendix B , "Inline SQL PL for DB2 UDB for Linux, UNIX, and Windows ."
On iSeries, you can specify processing options to be used to create the trigger by using the SET OPTION clause. This clause can be specified as part of the triggered action, just before the WHEN clause.
The optional WHEN clause defines the conditions for trigger activation. You could, for example, define the trigger to activate only if certain data existed in another table. For better performance, it is recommended that you specify trigger activation conditions with the WHEN clause if it is reasonable to do so. The WHEN clause is not supported in INSTEAD OF TRiggers.
Tip
Use the WHEN clause to define the trigger activation condition to prevent your triggers being activated the unnecessarily.
Note
On zSeries, labels are not allowed in triggers.
Triggers by Examples
Now after you know the basics of the DB2 triggers, it is the time to look at a few examples which will show you how to use DB2 triggers to enforce your business logic.
A BEFORE Trigger Example
BEFORE TRiggers are activated before the triggering SQL statement executes. By using a BEFORE trigger, you have the opportunity to supply values, validate data, and even reject the triggering SQL statement according to user-defined rules. In this section, a trigger will be created to activate before an INSERT into a table. Using similar code, you can create triggers to activate before UPDATE and DELETE operations.
In the SAMPLE database, a table called cl_sched is used to store data for class schedules. Two columns in cl_schedstarting and endingdefine when the class starts and ends, respectively.
A simple rule might be to assume that a class ends one hour after it begins if the ending time is not provided upon INSERT into this table. The trigger in Figure 9.17 will enforce this.
Figure 9.17. Example of a basic BEFORE INSERT trigger
CREATE TRIGGER default_class_end
NO CASCADE BEFORE INSERT ON cl_sched
--(1)
REFERENCING NEW AS n
--(2)
FOR EACH ROW
--(3)
MODE DB2SQL
WHEN (n.ending IS NULL)
--(4)
SET n.ending = n.starting + 1 HOUR
--(5)
Figure 9.17 shows how to create a trigger called default_class_end which activates before an insert on table cl_sched on Line (1). To intercept and supply a default-ending time, the trigger needs to make reference to values provided by the triggering INSERT statement. The REFERENCING NEW AS n clause on Line (2) associates the transition values provided by the INSERT statement with the qualifier n. The ending time of the INSERT statement can then be checked and/or supplied by referring to n. ending on Lines (4) and (5).
On Line (3), FOR EACH ROW means that this trigger will activate for every row that is inserted. Therefore, if you had executed the following INSERT statement:
INSERT INTO cl_sched (class_code, day, starting)
VALUES ('DB20002', 1, '12:00'), ('DB20003', 3, '9:00')
Note
DB2 on zSeries does not support multiple-row INSERT with the previous syntax. You need to modify the previous INSERT statement to two INSERT statements, one for each row.
The trigger would be activated twice, once for each row inserted, even though only a single INSERT statement was issued.
The trigger is defined to activate only when the ending time is null using the optional WHEN clause on Line (4), which ensures that the trigger only activates if a value has not been supplied.
Finally, if the trigger is activated, the ending time is supplied automatically and is set to one hour after the starting time on Line (5).
To test this trigger, execute the following SQL statement:
INSERT INTO cl_sched (class_code, day, starting)
VALUES ('DB20001', 1, '10:00')
Now, if you select all rows from cl_sched, you'll see that the class has been automatically set to have an ending time of 11:00.
SELECT * FROM cl_sched
CLASS_CODE DAY
STARTING ENDING
---------- ------ -------- --------
DB20001
1 10:00:00 11:00:00
1 record(s) selected.
Similarly, the trigger does not execute if an ending time is supplied:
INSERT INTO cl_sched (class_code, day, starting, ending)
VALUES ('DB20002', 2, '12:00', '15:00')
Now, selecting from the cl_sched will show that the ending time remains at 15:00.
SELECT * FROM cl_sched
CLASS_CODE DAY
STARTING ENDING
---------- ------ -------- --------
DB20001
1 10:00:00 11:00:00
DB20002
2 12:00:00 15:00:00
2 record(s) selected.
An AFTER Trigger Example
As stated earlier, unlike BEFORE triggers, AFTER triggers allow you to use INSERT, UPDATE, or DELETE statements inside the trigger body. This would be useful if you wanted to transparently keep an audit trail of when certain events occurred.
To support the following example, connect to the sample database and execute the following DDL to create a table called audit:
CREATE TABLE AUDIT (event_time TIMESTAMP, desc VARCHAR(100))
Figure 9.18 includes a trigger that can be used to keep an audit trail of salary changes with related information such as date and time of the change, as well as the person who made the change.
Figure 9.18. An example of a basic AFTER UPDATE trigger.
CREATE TRIGGER audit_emp_sal
AFTER UPDATE OF salary ON employee
REFERENCING OLD AS o NEW AS n
--(1)
FOR EACH ROW
MODE DB2SQL
INSERT INTO audit VALUES
--(2)
(CURRENT TIMESTAMP, ' Employee ' || o.empno ||
' salary changed from ' || CHAR(o.salary) || ' to ' ||
CHAR(n.salary) || ' by ' || USER)
Note
If the iSeries sample database is being used, you will need to drop the foreign key named red from the employee table for the example in Figure 9.18 to work.
In Figure 9.18 , the trigger is able to reference to both old and new values because it is defined to activate upon table updates on Line (1). Upon any change in salary for any employee, the trigger will insert into the audit table a record of when the update occurred, what the old and new values are, and who executed the UPDATE statement on Line (2). USER is a DB2 special register that holds the connection ID of the application. Refer to Chapter 2 , "Basic SQL Procedure Structure ," for more information. Also, the WHEN clause in this example has been left out so that this trigger will activate unconditionally.
To test this trigger, update Theodore Spenser's salary because he seems to be underpaid relative to other managers. To see the salaries of current managers, issue the following query:
SELECT empno, firstnme, lastname, salary FROM employee
WHERE job='MANAGER'
EMPNO
FIRSTNME
LASTNAME
SALARY
------ ------------ --------------- -----------
000020 MICHAEL
THOMPSON
41250.00
000030 SALLY
KWAN
38250.00
000050 JOHN
GEYER
40175.00
000060 IRVING
STERN
32250.00
000070 EVA
PULASKI
36170.00
000090 EILEEN
HENDERSON
29750.00
000100 THEODORE
SPENSER
26150.00
7 record(s) selected.
To give Theodore a 15 percent raise, issue the following UPDATE statement:
UPDATE employee e SET salary=salary*1.15 WHERE e.empno= '000100';
Now you can check the status of the employee table to see Theodore's new salary.
SELECT empno, firstnme, lastname, salary FROM employee e
WHERE e.empno='000100'
EMPNO
FIRSTNME
LASTNAME
SALARY
------ ------------ --------------- -----------
000100 THEODORE
SPENSER
30072.50
1 record(s) selected.
Finally, verify that the salary update has been logged in the AUDIT table:
SELECT * FROM AUDIT
EVENT_TIME
DESC
-------------------------- ----------------------------------------------------
2002-04-21 -21.26.07.665000 employee 000100 salary changed from 0026150.00 to
0030072.50
by DB2ADMIN
1 record(s) selected.
A Complex AFTER Trigger Example
In the examples presented thus far, the trigger code bodies have only contained single SQL statements. In this section, previous examples will be extended to show you how to incorporate more complex logic using the SQL PL elements you've already seen in previous chapters.
Returning to the example in Figure 9.17 , with the default class time suppose that some restrictions have been added with respect to when a class can be scheduled:
· A class cannot end beyond 9 PM.
· A class cannot be scheduled on weekends.
A trigger could be defined to disallow the INSERT, UPDATE, or DELETE on table cl_sched that violates the aforementioned rules and returns a descriptive error to the application.
If you created the trigger from the previous example, drop it before continuing with this example. Note that an ALTER TRIGGER statement does not exist. Therefore, to modify a trigger you must drop and re-create it:
DROP TRIGGER default_class_end
Figure 9.19 contains the trigger code to enforce the new rules.
Figure 9.19. An advanced BEFORE INSERT trigger using SQL PL (for LUW and iSeries only).
CREATE TRIGGER validate_sched
NO CASCADE BEFORE INSERT ON cl_sched
REFERENCING NEW AS n
FOR EACH ROW
MODE DB2SQL
vs: BEGIN ATOMIC
--(1)
-- supply default value for ending time if null
IF (n.ending IS NULL) THEN
--(2)
SET n.ending = n.starting + 1 HOUR;
END IF;
-- ensure that class does not end beyond 9PM
IF (n.ending > '21:00') THEN
--(3)
SIGNAL SQLSTATE '80000'
SET MESSAGE_TEXT='class ending time is beyond 9pm';
--(4)
ELSEIF (n.DAY=1 or n.DAY=7) THEN
--(5)
SIGNAL SQLSTATE '80001' SET MESSAGE_TEXT='class cannot
be scheduled on a weekend';
--(6)
END IF;
END vs
--(7)
Note
The example in Figure 9.19 does not work on zSeries due to a limitation of the statements that can be used in the trigger body. The list of supported SQL statements as well as an alternative example (shown in Figure 9.33 ) is provided in the zSeries considerations section.
Figure 9.33. An advanced BEFORE INSERT trigger using SQL PL in DB2 for zSeries.
CREATE TRIGGER validate_sched_1
NO CASCADE BEFORE INSERT ON cl_sched
REFERENCING NEW AS n
FOR EACH ROW
MODE DB2SQL
-- supply default value for ending time if null
WHEN (n.ending IS NULL)
BEGIN ATOMIC
SET n.ending = n.starting + 1 HOUR;
END
CREATE TRIGGER validate_sched_2
NO CASCADE BEFORE INSERT ON cl_sched
REFERENCING NEW AS n
FOR EACH ROW
MODE DB2SQL
-- ensure that class does not end beyond 9pm
WHEN (n.ending > '21:00')
BEGIN ATOMIC
SIGNAL SQLSTATE '80000' SET MESSAGE_TEXT='class ending time is beyond 9pm';
END
CREATE TRIGGER validate_sched_3
NO CASCADE BEFORE INSERT ON cl_sched
REFERENCING NEW AS n
FOR EACH ROW
MODE DB2SQL
-- supply default value for ending time if null
WHEN (n.DAY=1 or n.DAY=7)
BEGIN ATOMIC
SIGNAL SQLSTATE '80001' SET MESSAGE_TEXT='class cannot be scheduled on a weekend';
END
The trigger works by first supplying a default ending time, if it has not already been provided on Line (2). Then, it ensures that the ending time does not exceed 9PM on Line (3) and has not been scheduled for a weekend on Line (5).
Here are the highlights of the example in Figure 9.19 :
· In order to use multiple statements in the trigger body, the SQL PL statements must be wrapped within an atomic compound statement using BEGIN ATOMIC (1) and END (7).
· Within the atomic compound statement, SQL PL flow control elements like IF at (2), (3), and (5) and SIGNAL at (4) and (6) can be used.
· Note that the maximum length of the error message used with SIGNAL SQLSTATE is 70 characters. If you exceed this limit, the message will be truncated without warning at run time.
To test the trigger, execute the following SQL statements:
1. First, attempt to insert a class where the starting time is 9 PM. Because the ending time is not supplied, 10p.m. will be assumed.
2. INSERT INTO CL_SCHED (class_code, day, starting)
3. VALUES ('DB20005', 5, '21:00')
This insert statement results in the following custom error, as desired:
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0438N Application raised error with diagnostic text: "class ending time is
beyond 9pm". SQLSTATE=80000
4. Next, attempt to insert a class where the day of the week is Sunday (the value of DAY starts from Sunday with a value of 1).
5. INSERT INTO CL_SCHED (class_code, day, starting, ending)
6. VALUES ('DB20005', 1, '13:00', '15:00')
Again, the insert statement results in the following custom error, as expected:
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0438N Application raised error with diagnostic text: "class cannot be
scheduled on a weekend". SQLSTATE=80001
7. Finally, insert a valid value into the class schedule table (Thursday, 1 to 3 PM).
INSERT INTO CL_SCHED (class_code, day, starting, ending)
VALUES ('DB20005', 5, '13:00', '15:00')
By selecting from the cl_sched table, you will see the row that was just inserted. (You may see another row, as shown next, if you attempted the previous example.)
SELECT * FROM cl_sched
CLASS_CODE DAY
STARTING ENDING
---------- ------ -------- --------
DB20001
1 10:00:00 11:00:00
DB20002
2 12:00:00 15:00:00
DB20005
5 13:00:00 15:00:00
3 record(s) selected.
An INSTEAD OF Trigger Example (for LUW Only)
Figure 9.20 displays a view based on table org in the sample database. It gives you the department count by divisions.
Figure 9.20. An example of an un-updatable view.
CREATE VIEW org_by_division
(division, number_of_dept)
AS
SELECT division, count(*)
FROM org
GROUP BY division
A simple SELECT query
SELECT * FROM org_by_division
shows the content of the view as following:
DIVISION
NUMBER_OF_DEPT
---------- --------------
Corporate
1
Eastern
3
Midwest 2
Western
2
4 record(s) selected.
In DB2, if a view contains the GROUP BY clause, the view is not updatable, which means you can not issue an UPDATE statement against the view. Try the following UPDATE statement, which attempts to change the Midwest division name to Southern division:
UPDATE org_by_division SET division='Southern' WHERE division='Midwest'
You will receive the following SQL error indicating that the operation is not supported:
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0150N The target fullselect, view, typed table, materialized query table,
or staging table in the INSERT, DELETE, UPDATE, or MERGE statement is a target
for which the requested operation is not permitted. SQLSTATE=42807
As discussed earlier, an INSTEAD OF trigger can be used to implement the UPDATE operation you want to perform on the view which is not updatable. Figure 9.21 is such an example.
Figure 9.21. An example of an INSTEAD OF trigger.
CREATE TRIGGER upd_org
INSTEAD OF UPDATE
ON org_by_division
REFERENCING OLD AS o NEW AS n
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
IF (o.number_of_dept != n.number_of_dept) THEN
-- (1)
SIGNAL SQLSTATE '80001' SET MESSAGE_TEXT =
'The number of department is not updatable.';
END IF;
UPDATE org
-- (2)
SET division = n.division
WHERE division = o.division;
END
After the trigger is created, when an UPDATE statement is issued against the view the trigger body will be executed instead. If the query is to update the number of department, it will be rejected on Line (1). It is reasonable as you cannot update the counts without adding rows into the base table. If the query is to update the division name, the UPDATE statement on Line (2) will be executed to update all corresponding rows in the org table from the old division name to the new division name.
A simple SELECT query shows the content of the base table:
SELECT * FROM org
DEPTNUMB DEPTNAME
MANAGER DIVISION
LOCATION
-------- -------------- ------- ---------- -------------
10 Head Office
160
Corporate
New York
15 New England
50
Eastern
Boston
20 Mid Atlantic
10
Eastern
Washington
38 South Atlantic 30
Eastern
Atlanta
42 Great Lakes
100
Midwest
Chicago
51 Plains
140
Midwest
Dallas
66 Pacific
270
Western
San Francisco
84 Mountain
290
Western
Denver
8 record(s) selected.
To see the effect of the trigger, issue an UPDATE query that attempts to update the number of department. The customized error message defined in the trigger body is returned:
UPDATE org_by_division SET number_of_dept=2 WHERE division='Corporate'
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0438N Application raised error with diagnostic text: "The number of
department is not updatable.". SQLSTATE=80001
Issue the previously failed UPDATE query again to update the division name:
UPDATE org_by_division SET division='Southern' WHERE division='Midwest'
DB20000I The SQL command completed successfully.
The query was successful because of the INSTEAD OF trigger. All the query did was to activate the trigger. It was the body of the trigger that really went through. Issue the SELECT query again to exam the content of the org table:
SELECT * FROM org
DEPTNUMB DEPTNAME
MANAGER DIVISION
LOCATION
-------- -------------- ------- ---------- -------------
10 Head Office
160
Corporate
New York
15 New England
50
Eastern
Boston
20 Mid Atlantic
10
Eastern
Washington
38 South Atlantic 30
Eastern
Atlanta
42 Great Lakes
100
Southern
Chicago
51 Plains
140
Southern
Dallas
66 Pacific
270
Western
San Francisco
84 Mountain
290
Western
Denver
8 record(s) selected.
Comparing with the previous content, you may notice that all Midwest division rows are renamed into the Southern division.
The IF...ELSE... block in the trigger body in Figure 9.18 cannot be replaced by the UPDATE OF or the WHEN clause because they are not supported in INSTEAD OF triggers.
A Comparison of View Triggers and Table Triggers (LUW only)
The view trigger is a relatively new concept for DBMSs. A fundamental difference exists between the table triggers and the view triggers. Figure 9.22 is used to further illustrate the difference. It consists of a simple base table, a trivial view (which is defined the exactly the same as the base table), a simple table insert trigger, and a view insert trigger.
Figure 9.22. A comparison of a view trigger and a table trigger.
CREATE TABLE t_airport
( airport_code char(3) not null,
airport_name char(50) );
CREATE VIEW v_airport
AS
SELECT * FROM t_airport;
CREATE TRIGGER insert_t_airport
AFTER INSERT
ON t_airport
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
END;
CREATE TRIGGER insert_v_airport
INSTEAD OF INSERT
ON v_airport
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
END;
Both triggers are very similar. They both have an empty trigger body. There is no triggered action defined. Without the triggers, the following INSERT statements through the view or to the table generates the same results:
INSERT INTO t_airport VALUES ('YYZ', 'TORONTO');
INSERT INTO v_airport VALUES ('YYZ', 'TORONTO');
After the triggers are defined, however, the results of the INSERT statements become different. Execute the INSERT statement on the empty table:
INSERT INTO t_airport VALUES ('YYZ', 'TORONTO');
You will see the content of the table t_airport as the following:
AIRPORT_CODE AIRPORT_NAME
------------ --------------------------------------------------
YYZ
TORONTO
1 record(s) selected.
This result is expected. The values in the INSERT statement are all valid. The INSERT statement completed without a problem. Then the AFTER trigger is activated. Because the trigger has no action defined, it did nothing as expected.
What if you execute the INSERT statement through a view now?
INSERT INTO v_airport VALUES ('SFO', 'SAN FRANCISCO');
You can probably expect the same behavior. After all, the two triggers look very similar. Query the table t_airport again, and you should see the following:
AIRPORT_CODE AIRPORT_NAME
------------ --------------------------------------------------
YYZ
TORONTO
1 record(s) selected.
Nothing changed. There is no new row. Where does the record go? Is there anything wrong? Not really. Everything worked as designed. Remember if an INSTEAD OF trigger is defined, the triggering statement will only serve the pupose of activating the trigger. It will not be executed at all. Instead, the trigger body, the triggered actions, will be executed. In this example, the triggered action is not defined, to the trigger did nothing, as designed.
A slight modification of the INSTEAD OF trigger in Figure 9.21 is needed to make it work the same away as the table trigger. The result is shown in Figure 9.23 .
Figue 9.23. The modified INSTEAD OF trigger.
CREATE TRIGGER insert_v_airport
INSTEAD OF INSERT
ON v_airport
REFERENCING NEW AS n
-- (1)
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
INSERT INTO t_airport
-- (2)
VALUES (n.airport_code, n.airport_name);
END
In order to behave the same as the table trigger in Figure 9.22 , the INSERT statement to the base table has to be explicitly implemented on Line (2), and the REFERENCING clause on Line (1) has to be included in the header.
Invoking UDFs and SQL Procedures from Triggers
Both UDFs and SQL procedures can be invoked from triggers. The ability to invoke SQL procedures is particularly useful on LUW if you need to use DB2 SQL PL features that are not yet supported in the inline SQL PL subset.
Tip
On LUW, use SQL procedures to work around inline SQL PL limitations in triggers.
An example of how triggers invoke functions and procedures is illustrated in Figures 9.24 , 9.25 , and 9.26 .
Figure 9.24. An example of a trigger invoking a function and a procedure (for LUW and iSeries).
CREATE TRIGGER insert_employee
NO CASCADE BEFORE INSERT
ON employee
REFERENCING NEW AS n
FOR EACH ROW
MODE DB2SQL
ie: BEGIN ATOMIC
DECLARE v_valid CHAR(1) DEFAULT 'N';
IF (n.empno = '') THEN
SET n.empno = new_empno();
-- (1)
END IF;
CALL validate_dept(n.workdept, v_valid);
-- (2)
IF (v_valid = 'N') THEN
SIGNAL SQLSTATE '80001'
SET MESSAGE_TEXT= 'Incorrect department number'; -- (3)
END IF;
END ie
Figure 9.25. The supporting UDF for the trigger in Figure 9.24 .
CREATE FUNCTION new_empno ( )
RETURNS CHAR(6)
LANGUAGE SQL
SPECIFIC new_empno
ne: BEGIN ATOMIC
DECLARE v_max_empno INT;
DECLARE v_next_empno VARCHAR(12);
SET v_max_empno =
-- (1)
(SELECT INT(MAX(empno)) FROM employee);
SET v_next_empno =
-- (2)
( '000000' || RTRIM(CHAR(v_max_empno + 10)) );
RETURN SUBSTR(v_next_empno, LENGTH(v_next_empno)-5, 6);
-- (3)
END ne
Figure 9.26. The supporting SQL procedure for the trigger in Figure 9.24 .
CREATE PROCEDURE validate_dept
( IN p_deptno VARCHAR(3),
OUT p_valid CHAR(1) )
LANGUAGE SQL
SPECIFIC validate_dept
-- Applies to LUW and iSeries
-- WLM ENVIRONMENT <env>
-- Applies to zSeries
READS SQL DATA
vd: BEGIN
-- Declare variables
DECLARE v_deptno CHAR(3);
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
-- Procedure logic
DECLARE c_dept CURSOR FOR
SELECT deptno FROM department;
OPEN c_dept;
SET p_valid = 'N';
FETCH FROM c_dept INTO v_deptno;
w1: WHILE ( SQLSTATE = '00000' ) DO
IF (v_deptno = p_deptno) THEN
SET p_valid = 'Y';
LEAVE w1;
END IF;
FETCH FROM c_dept INTO v_deptno;
END WHILE w1;
CLOSE c_dept;
END vd
Note
The examples in Figures 9.24 , 9.25 , and 9.26 do not work on zSeries due to some restrictions in UDFs and triggers. Refer to the zSeries considerations section for details.
Figure 9.24 shows a BEFORE INSERT TRigger that performs data validation on the input data. It creates a new employee number if the input employee number is an empty string. It validates the department number, raises an SQL error, and stops the INSERT statement if the department number is not validated. A business decision has been made to implement the logic to produce a new employee number by a function so that the function can be used by other triggers, procedures, and functions if needed. The trigger only invokes the UDF on Line (1).
Another business decision is also made to use a cursor to implement the logic of department number validation. The example only illustrates how to invoke procedures in triggers as a method to use cursors indirectly. The cursor is used in a simple example. In real life, you should only use cursors when necessary; for example, you may consider using cursors because the department table is very large and because the business logic is too complex for a query to handle. For more information, refer to Chapter 5 , "Understanding and Using Cursors and Result Sets ." The trigger only invokes the SQL procedure on Line (2).
The only logic implemented in the trigger is to raise an SQL error if the department number is not valid on Line (3).
The UDF in Figure 9.25 generates a new employee number by adding 10 to the largest employee number that currently exists. The employee number is defined as CHAR(6) in the employee table. However, the contents are numeric strings. Because DB2 SQL PL is a strong typed language, explicit casting is needed. The maximum employee number on Line (1) needs to be cast into an integer for the next employee number calculation. The result is cast back to characters on Line (2). The RTRIM function is used on Line (2) to remove the trailing blanks after the conversion from an integer to a character string. Concatenating leading zeros on Line (2) and taking the rightmost six characters on Line (3) formats the employee number in the proper style.
The body of the UDF can be implemented in one line of the RETURN statement. It is implemented as shown in Figure 9.25 for better readability. In your own project, it is recommended that a numeric column is used as the IDs and that a DB2 SEQUENCE object or a DB2 IDENTITY column is used for auto-incremental IDs. The implementation in Figure 9.25 illustrates using UDFs with triggers. Using maximum value to calculate the next ID may cause concurrent problems in a real-life application. For more information on the SEQUENCE object or the IDENTITY column, refer to Chapter 3 , "Overview of SQL PL Language Elements ."
The validate_dept procedure checks to see whether the input department number is correct. A 'Y' is returned for a department number found in the department table; otherwise, an 'N' is returned. A cursor is used in this simple example to illustrate how to use SQL procedures that implement SQL PL features that are not currently supported in triggers.
If an 'N' is returned to the trigger in Figure 9.24 , the IF...ELSE block on Line (3) will raise an SQL error and pass a customized error message to the application.
The use of a UDF in this example allows the trigger to reuse the new employee number-generating code, which might be shared by many database objects. Implementing SQL procedures allows the trigger to use the cursor indirectly.
To test the trigger, use a new employee record with a valid employee number but an invalid department number as shown in the following code. The shown customized error message indicates that the procedure is invoked properly, because the business logic of department number checking is only implemented in the SQL procedure.
INSERT INTO employee (empno, firstnme, midinit, lastname, workdept, edlevel)
VALUES('000400', 'Firstname',
'T', 'Lastname', 'ABC', 1)
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0438N Application raised error with diagnostic text: "Incorrect department
number". SQLSTATE=80001
Now test again with a valid department number but without an employee number. A new record is successfully created. The employee number is created from the maximum existing employee number as implemented in the UDF.
INSERT INTO employee (empno, firstnme, midinit, lastname, workdept, edlevel)
VALUES('', 'Firstname', 'T',
'Lastname', 'A00', 1)
DB20000I
The SQL command completed successfully.
SELECT empno, firstnme, midinit, lastname, workdept, edlevel FROM employee
EMPNO
FIRSTNME
MIDINIT LASTNAME
WORKDEPT EDLEVEL
------ ------------ ------- --------------- -------- -------
000010 CHRISTINE
I
HAAS
A00
18
...
000340 JASON
R
GOUNOT
E21
16
000350 Firstname
T
Lastname
A00
1
33 record(s) selected.
For other considerations of invoking SQL procedures in UDFs and triggers, refer to the next section of this chapter.
最后
以上就是坚强大碗为你收集整理的数据库触发器Oracle 触发器 : MS SQLServer 触发器 : DB2 触发器 : 的全部内容，希望文章能够帮你解决数据库触发器Oracle 触发器 : MS SQLServer 触发器 : DB2 触发器 : 所遇到的程序开发问题。
如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。
发表评论 取消回复