sql触发器的用法

sql触发器的用法

触发器：是一种特殊类型的存储过程，不由用户直接调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（Insert，Delete，Update）。创建触发器时会对其进行定义，以便在对特定表或列做特定类型的数据修改时执行。

使用触发器的优点：1.触发器是MySQL服务器上自动提供的

2.触发器可以查询多张表，而且可以包含复杂的SQL语句。

以一个牛腩新闻系统上的一个例子简单介绍触发器的使用：

要求： 删除新闻类别，同时删除新闻类别下的新闻内容和新闻评论，这样的操作涉及到三张表的操作　　　　　　　　　　　　　　　　 （Category、News、Comment），如果用SQL语句是很难实现的。

/*
*如何创建After DML触发器
*如何创建Instead Of DML触发器
*如何创建DDL触发器
*如何修改和删除既有触发器
*如何启用和禁用触发器
*如何限制触发器嵌套、设置触发器顺序和控制递归
*如何查看触发器元素据
*/
--1、DML触发器
--A、After DML触发器是在对表的insert,update和delete修改操作成功完成后执行
--语法：

/*
Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)
CREATE TRIGGER [ schema_name . ]trigger_name
ON { table | view }
[ WITH [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS { sql_statement [ ; ] [ ...n ] | EXTERNAL NAME }

::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]
*/

--使用触发器来跟踪Production.ProductInventory表的行的插入和删除

--跟踪所有的插入、更新和删除操作

create table Production.ProductInventoryAudit
(
ProductID int not null,
LocationID smallint not null,
Shelf nvarchar(10) not null,
Bin tinyint not null,
Quantity smallint not null,
rowguid uniqueidentifier not null,
ModifiedDate datetime not null,
InsOrUPD char(1) not null
)

go

--创建触发器来填充Production.ProductInventoryAudit表

create trigger Production.trg_uid_ProductInventoryAudit
on Production.ProductInventory
after insert,delete
as

set nocount on --屏蔽触发器触发时"受影响行数"消息返回给调用的应用程序

--插入行

insert Production.ProductInventoryAudit
(ProductID,LocationID,Shelf,Bin,Quantity,rowguid,ModifiedDate,InsOrUPD)
select Distinct i.productID,i.LocationID,i.Shelf,i.Bin,i.Quantity,i.rowguid,getdate(),'I'
from inserted i

--删除的行

insert Production.ProductInventoryAudit
(ProductID,LocationID,Shelf,Bin,Quantity,rowguid,ModifiedDate,InsOrUPD)
select Distinct d.productID,d.LocationID,d.Shelf,d.Bin,d.Quantity,d.rowguid,getdate(),'D'
from deleted d
go

--插入一个新行
insert production.ProductInventory
(ProductID,LocationID,Shelf,Bin,Quantity) values(316,6,'A',4,22)

--删除一行
delete Production.ProductInventory where ProductID=316 and LocationID=6

--检测审核表
select ProductID,LocationID,InsOrUPD from Production.ProductInventoryAudit

--select * from production.ProductInventory
--select * from Production.Product
--B、创建Instead of DML触发器
--Instead of触发器的执行替代触发触发器的原始数据修改操作，并对表和视图都允许
--通常用于处理不允许进行数据修改的视图的数据修改操作

--示例：创建一个新表来保存HumanResources.Department表"等待批准pending approval"
--行。这些是需要经理的批准才能加入正式表的新部门。创建一个视图来显示来呢两个表中所有
--"已批准"和"等待批准"的部门，然后会在视图上创建一个Instead of触发器，导致插入的行
--会被转到新的审批表，而不是HumanResources.Department表：

--创建部门"审批"表

create table HumanResources.DepartmentApproval
(
Name nvarchar(50) not null unique,
GroupName nvarchar(50) not null,
ModifiedDate datetime not null default getdate()
)
go

--创建视图来查看已批准的和待批准的部门
create view HumanResources.vw_Department
as
select Name,GroupName,ModifiedDate,'Approved' Status
from HumanResources.Department
union
select Name,GroupName,ModifiedDate,'Pending Approval' Stuatus
from HumanResources.DepartmentApproval

go

--在新视图上创建Instead of触发器
create Trigger HumanResources.trg_vw_Department
on HumanResources.vw_Department
instead of insert
as
set nocount on
insert HumanResources.DepartmentApproval(Name,GroupName)
select i.Name,i.GroupName from inserted i
where i.Name not in(select Name from HumanResources.DepartmentApproval)
go

--向视图插入行
insert HumanResources.vw_Department
(Name,GroupName) values('Print Production','Manufacturing')

--检查视图的内容

select Status,Name
from HumanResources.vw_Department where GroupName='Manufacturing'

--select * from HumanResources.Department
--select * from HumanResources.DepartmentApproval

--C、使用DML触发器和事务

alter trigger Production.trg_uid_ProductInventoryAudit1
on Production.ProductInventory after insert,delete
as

set nocount on
if exists(select Shelf from inserted where Shelf='A')

begin

print 'Shelf ''A'' is closed for new inventory.'
rollback
end

--插入的行
insert Production.ProductInventoryAudit
(ProductID,LocationID,Shelf,Bin,Quantity,rowguid,ModifiedDate,InsOrUPD)
select distinct i.ProductID,i.LocationID,i.Shelf,i.Bin,i.Quantity,i.rowguid,getdate(),'I'
from inserted i

--删除的行

insert Production.ProductInventoryAudit
(
ProductID,LocationID,Shelf,Bin,Quantity,rowguid,ModifiedDate,InsOrUPD
)
select d.ProductID,d.LocationID,d.Shelf,d.Bin,d.Quantity,d.rowguid,getdate(),'D'
from deleted d

if exists
(
select Quantity from deleted where Quantity>0
)
begin
print '你不能删除有确定数量的行！'
rollback
end
go

--使用Shelf'A'插入新行来测试
insert Production.ProductInventory(ProductID,LocationID,Shelf,Bin,Quantity)
values(316,6,'A',4,22)

--使用显式事务演示两个删除

begin transaction
--删除0数量的行
delete Production.ProductInventory where ProductID=853 and LocationID=7

--删除非0数量的行
delete Production.ProductInventory where ProductID=999 and LocationID=60

commit transaction

--因为触发器发起了回滚,所以外部事务也结束了，所以同一事务中行没有被删除

select ProductID,LocationID from Production.ProductInventory
where (ProductID=853 and LocationID=7) or (ProductID=999 and LocationID=60)

--D、查看DML触发器的元数据

--演示查看当前数据库教程中有关触发器的信息

select object_name(parent_id) Table_or_ViewNM,
name TriggerNM,is_instead_of_trigger,is_disabled
from sys.triggers
where parent_class_desc='Object_or_column'
order by object_name(parent_id),name

--显示某个触发器的T-SQL定义，可以查询sys.sql_modules
select o.name,m.definition
from sys.sql_modules m
inner join sys.objects o
on m.object_id=o.object_id where o.type='TR'

--2、DDL触发器

--SQL Server2005引入DDL触发器是对服务器活数据库事件做出响应，而不是表数据
--修改
--语法：
/*
Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger)
CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH [ ,...n ] ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement [ ; ] [ ...n ] | EXTERNAL NAME < method specifier > [ ; ] }

::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]

::=
assembly_name.class_name.method_name
*/
--A、创建审核服务器级别的事件的DDL触发器

--创建DDL触发器
use master
go
--不允许在SQL实例上有新的登录

create trigger srv_trg_RestricNewLogins on all server
for create_login
as
print '禁止创建新登录'
rollback
go

--试图增加新的SQL登录：
create Login Joes with password='A235921'
go
--B、创建审核数据库级别的事件的DDL触发器
--创建审核表
create table dbo.ChangeAttempt
(
EventData xml not null,
AttemptDate datetime not null default getdate(),
DBUser char(50) not null
)
go
--创建一个数据库DDL触发器来跟踪索引操作，插入事件数据到新创建的表中：

create trigger db_trg_RestrictIndexChanges
on database
for create_index,alter_index,drop_index
as
set nocount on
--EVENTDATA()函数以XML格式返回服务器和数据事件信息
insert dbo.ChangeAttempt(EventData,DBUser) values(EVENTDATA(),User)
go

--在数据库中建立真实的索引

create nonclustered index in_ChangeAttempt_DBUser on dbo.ChangeAttempt(DBUser)
go
select * from dbo.ChangeAttempt

--C、查看DDL触发器元数据

--显示当前数据库中的DDL触发器
select name TriggerNM,is_disabled
from sys.triggers
where parent_class_desc='DATABASE'
order by object_name(parent_id),name

--查询服务器级别触发器数据

select name,s.type_desc SQL_or_CLR,
is_disabled,e.type_desc FiringEvents
From sys.server_triggers s
inner join sys.server_trigger_events e on
s.object_id=e.object_id

--查询数据库范围的DDL触发器的T-SQL定义

select t.name,m.Definition
from sys.triggers as t
inner join sys.sql_modules m on t.object_id=m.object_id
where t.parent_class_desc='database'

--要显示服务期范围内的DDL触发器

select t.name,m.definition
from sys.server_sql_modules m
inner join sys.server_triggers t on
m.object_id=t.object_id

--3、管理触发器

--修改触发器

--修改触发器，这次不会限制用户创建新的登录名，而是允许登录名事件，之后是一条警告和对审核表进行insert
alter trigger srv_trg_RestricNewLogins
on all server
for create_login
as
set nocount on
print '你创建登录将被监视'
insert AdventureWorks.dbo.ChangeAttempt
(EventData,DBUser) values(EVENTDATA(),user)

go

--启用和禁止触发器

create trigger HumanResources.trg_Department
on HumanResources.Department
after insert
as
print N'触发器被激活'
go

disable trigger HumanResources.trg_Department
on HumanResources.Department
--因为触发器被禁止了，所以下面的Insert执行后不会返回打印消息

insert HumanResources.Department(name,GroupName) values('Construction','Building Services')

go

--enable trigger命令启用触发器
enable trigger HumanResources.trg_Department
on HumanResources.Department

--再次插入
insert HumanResources.Department
(Name,GroupName)
values('Cleaning1','Building Services')

--限制触发器嵌套

/*
当触发器触发之后执行的动作会触发另一个触发器，那个触发器然后又触
发另外一个触发器的时候就发生了触发器嵌套
SQL Server 2005最大嵌套级别是32层
*/

--禁止和启用触发器嵌套

use master
go

--禁止嵌套
exec sp_configure 'nested triggers',0
reconfigure with override --由于服务器选项包含当前的配置和运行的配置，此命令用于更新运行时值以使之立即生效
go

--启用嵌套

exec sp_configure 'nested trigger',1
reconfigure with override
go

--控制触发器递归

/*
如果触发器触发后执行的行为会导致相同的表触发器再次触发，那么这种触发器嵌套就被认为是递归的。当触发器的触发影响
其他表的时候，如果触发器还会影响原始表，引起原来的触发器再次触发，也会发生递归
*/

--启用和禁止递归触发器

alter database AdventureWorks --是否允许数据库内递归触发器
set recursive_Triggers on

--查看数据库设置

select is_recursive_triggers_on
from sys.databases

where name='AdventureWorks'

--防止递归
alter database AdventureWorks
set recursive_triggers off

--查看数据库设置
select is_recursive_triggers_on
from sys.databases
where name='AdventureWorks'

--设置触发器触发次序

--创建一个测试表并为之增加3个DML的Insert触发器,然后使用sp_settrigger来定义触发器次序

create table dbo.TestTriggerOrder
(
TestID int not null
)
go

create trigger dbo.trg_i_TestTriggerOrder
on dbo.TestTriggerOrder
after insert
as
print N'我将被第一个触发'
go

create trigger dbo.trg_i_TestTriggerOrder2
on dbo.TestTriggerOrder
after insert
as
print N'我将最后被触发'
go
create trigger dbo.trg_i_TestTriggerOrder3
on dbo.TestTriggerOrder
after insert
as
print N'我将不是第一个也不是最后一个被触发'
go

exec sp_settriggerorder 'trg_i_TestTriggerOrder','First','INSERT'
exec sp_settriggerorder 'trg_i_TestTriggerOrder2','Last','INSERT'

insert dbo.TestTriggerOrder values(2);
go

--删除触发器

--删除DML触发器
drop trigger dbo.trg_i_TestTriggerOrder
--删除多个触发器

Drop trigger dbo.trg_i_TestTriggerOrder2,dbo.trg_i_TestTriggerOrder3

--删除DDL触发器

Drop trigger db_trg_RestrictIndexChanges

实例

USE [NewsSystem]GO/****** Object:
Trigger [dbo].[trigCategoryDelete]
Script Date: 06/24/2011 19:31:14 ******/
SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================--
Author:李颖杰
-- Create date: 2011-06-22-- Description:
删除类别触发器--
=============================================
ALTER TRIGGER [dbo].[trigCategoryDelete]
ON [dbo].[Category]
instead of DELETEAS BEGIN
declare @caId int select @caId=id from deleted
--删除评论
delete comment where newsId in(select newsId from news where caId=@caId)
--删除新闻 delete news where caId=@caId
--删除类别 delete category where id=@caId
SET NOCOUNT ON;
end