SQL Trigger 建立

SQL SERVER 的触发器建立过程

--*********************************************************************************************
-- ABOUT CREATE, USE AND ATTENTIONS OF SQL TRIGGER,
-- 关于触发器对象的创建,使用和一些注意事项
--*********************************************************************************************


--set nocount on --系统结果消息的输出: 关闭,
--set nocount off --系统结果消息的输出: 开启（默认设置）
--(系统结果消息如: x 行受影响),关闭不必要的系统消息输出可以提高系统运行速度,节省系统资源


--SQL对变量赋值,可以用SET方法或SELECT方法
--[1]如果不涉及到数据表的查询,或对单一变量赋值推荐使用SET,减少查询可以省系统资源; 
--[2]如果涉及到数据表查询或对多个变量赋值操作则用SELECT;
--*********************************************************************************************


use TESTDB01
go


--*********************************************************************************************
-- CREATE TABLE
--*********************************************************************************************
if not exists(select * from sysobjects where upper([xtype])='U' and [name]='Table01') 
begin
create table Table01(
[ID] int identity(1,1),
[DepartmentID] char(4) not null primary key,
[DepartmentName] varchar(50) null
)
end 


if not exists(select * from sysobjects where upper([xtype])='U' and [name]='Table02')
begin
create table Table02(
[ID] int identity(2,1),
[DepartmentID] char(4) null,
[workerID] char(4) not null primary key,
[workerName] varchar(20) null
)
end 
go


--*********************************************************************************************
-- CREATE TRIGGER
-- 触发器功能:当对表进行增加/更新/删除时使数据库自动完成特定的复杂的功能;
-------------------
-- 触发器存在的判断:  触发器的信息保存在本库的系统表 sysobjects 中,判断有无的方法有较好如下的三种:
-- [1],If object_id('触发器名') is not null ...  (推荐用)
-- [2],If exists(select * from sysobjects where name='触发器名' and xtype='TR') ... (推荐用)
-- [3],If exists(select * from sysobjects where id=object_id('触发器名') and objectproperty(id,N'istrigger')=1) ... 
-------------------
-- 注意[1]:  必须在创建触发器的前面和后面都加上GO语句,否则出现:"CREATE TRIGGER必须是查询语句的第一条"的错误;
-- GO语句具有作用域分段作用,GO之前和之后是两个不同的作用域,当GO之后开始开始执行CREATE TRIGGER可以被
-- 认为是第一条语句,执行完,用GO语句提交之(创建的触发器),在此后面被认为又是另一段作用域了;
-------------------
-- 注意[2](关键:触发器工作原理):  
-- INSERT触发器执行时,将插入到指定表中的数据(元组)复制一份放入(保存到)特殊的逻辑表(inserted)中,触发器检查该表决定下一步处理;
-- DELETE触发器执行时,从指定的表中删除指定元组,同时将删除的元组放入(保存到)特殊的逻辑表(deleted)中,触发器检查该表决定下一步处理;
-- UPDATE触发器执行时,把将要被更新的原数据(原元组)移入特殊逻辑表DELETED表中,再将更新后的新数据(也即将插入的新值)的一个备份
--     送入特殊表INSERTED表中,然后UPDATE触发器检查这两表,并决定下一步如何处理;
--*********************************************************************************************
--如果存在名为 Trig01 的触发器,则删除之
if exists(select * from sysobjects where upper([name])='TRIG01'and [xtype]='TR')
drop trigger Trig01
go


--为表 Table01 创建 DELETE 触发器
create trigger Trig01 on Table01 for Delete as
begin
declare @depID as char(4)
select @depID=[DepartmentID] from [deleted] --从逻辑删除表中取回部门ID号
print 'Output data: '+convert(varchar(4),@depID) --输出变量的内容
select * from [deleted] --显示特殊逻辑表 DELETED 中的内容
delete from Table02 where [DepartmentID]=@depID --(select [DepartmentID] from Table01)
end 
go 




----------------------------------------------------------------------------------------------
--如果存在名为 Trig02 的触发器,则删除之
if exists(select * from sysobjects where upper([name])='TRIG02' and [xtype]='TR')
drop trigger Trig02
go




--为表 Table01 创建 UPDATE 触发器
create trigger Trig02 on Table01 for Update as
begin 
--据UPDATE触发器的工作原理可知:先从DELETED表中取得旧部门ID,然后从INSERTED表中取得新部门ID,再对
--需要跟随变动的表Table02用指定的新值和指定的条件进行更新操作
declare @oldDepID char(4)
select @oldDepID=[DepartmentID] from [deleted] --从逻辑DELETED表中取出部门ID号
print 'Output old data: '+convert(char(4),@oldDepID) --输出变量(旧数据),实际可不输出
select * from [deleted] --输入DELETED表中的内容,实际可不输出
declare @newDepID char(4)
select @newDepID=[DepartmentID] from [inserted] --从逻辑表INSERTED中取出
print 'Output new data: '+convert(char(4),@newDepID) --输出变量(旧数据),实际可不输出
select * from [inserted] --输出INSERTED表的内容,实际可不输出
--注意更新的是表Table02,新的部门ID号从INSERTED表中得,旧的部门ID号从DELETED表中得
update table02 set [DepartmentID]=@newDepID where [departmentID]=@oldDepID
end
go


--*********************************************************************************************
-- INSERT DATA TO TABLE
--*********************************************************************************************
if ((select count(*) from Table01)>0) --每次清空数据后再增加
delete from Table01


insert Table01 ([DepartmentID],[DepartmentName]) values ('A001','Office')
insert Table01 ([DepartmentID],[DepartmentName]) values ('A002','Engineer')
insert Table01 ([DepartmentID],[DepartmentName]) values ('A003','Product')
insert Table01 ([DepartmentID],[DepartmentName]) values ('A004','Material')




if ((select count(*) from Table02)>0) --每次清空数据后再增加
delete from Table02


insert Table02 ([DepartmentID],[workerID],[workerName]) values ('A001','W001','Zhang shan')
insert Table02 ([DepartmentID],[workerID],[workerName]) values ('A002','W002','Li si')
insert Table02 ([DepartmentID],[workerID],[workerName]) values ('A003','W003','Zhang ming')
insert Table02 ([DepartmentID],[workerID],[workerName]) values ('A004','W004','Wang wu')
insert Table02 ([DepartmentID],[workerID],[workerName]) values ('A004','W005','Lao liu')


select * from Table01
select * from Table02
go


--*********************************************************************************************
-- 要看到触发器的效果,需要对表Table01进行删除/更新操作才可以看到,如下:
-- delete from table01 where [departmentID]='A004'
-- update table01 set [departmentID]='B003' where [departmentID]='A003'
--*********************************************************************************************


-- 用完删除相关的例子表,如果没有时本程序可以生成
--drop table Table01

--drop table Table02