什么是触发器？
最佳答案：
触发器是一种特殊的存储过程，它在试图更改触发器所保护的数据时自动执行。
它被定义为在对表或视图发出 UPDATE、INSERT 或 DELETE 语句时自动执行，在有数据修改时自动强制执行其业务规则。
触发器可以扩展 SQL Server 约束、默认值和规则的完整性检查逻辑，但只要约束和默认值提供了全部所需的功能，就应使用约束和默认值。
*/
-- 触发器的特点
/*
与表相关联
触发器定义在特定的表上，这个表称为触发器表。
自动激活触发器
当对表中的数据执行 INSERT、UPDATE 或 DELETE 操作时，如果对表上的这个特定操作定义了触发器，该触发器自动执行，这是不可撤销的。
不能直接调用
与存储过程不同，触发器不能被直接调用，也不能传递或接受参数。
作为事务的一部分
触发器与激活触发器的语句一起做为对一个单一的事务来对待，可以从触发器中的任何位置回滚。
当使用触发器时，应该考虑以下事实和知道原则：
即使不存在显式的 BEGIN TRANSACTION 语句，在触发器的定义中也可以包括 ROLLBACK TRANSACTION 语句
如果遇到一个 ROLLBACK TRANSACTION 语句，则整个事务回滚。如果在触发器脚本中跟随在 ROLLBACK TRANSACTION 语句后还有别的语句，
这些语句将继续被执行。需要的话，可以使用 RETURN 语句来防止这些语句的执行。
如果包括 ROLLBACK TRANSACTION 语句的触发器在一个用户定义的事务中被激活，这个 ROLLBACK TRANSACTION 语句撤销整个事务。
在这个用户定义的事务的批处理语句中，激活该触发器的语句之后的语句将不再执行。
*/
-- 举例
/*
use Test
GO
create table dbo.t_record(idx int identity(1,1),col_1 varchar(20))
insert t_record (col_1) values ('Row 01')
insert t_record (col_1) values ('Row 02')
insert t_record (col_1) values ('Row 03')
*/
create trigger tr_record_delete on dbo.t_record
for delete
as
/*
作用：当删除表 t_record 中的记录时,若一次删除 1 条以上,则取消删除
*/
if (select count(*) from deleted)>1
begin
raiserror('You cannot delete more than one record at a time',16,1)
rollback tran
end
Go
select * from t_record
delete t_record
select * from t_record
delete t_record where idx=1
-- 触发器的使用
/*
触发器最好用于保持低级的数据完整性,而不是返回查询结果。
触发器的主要好处是可以包含负责的处理逻辑。
触发器能对数据库中相关的表进行级联修改，强制比 CHECK 约束更复杂的数据完整性。与 CHECK 约束不同，触发器可以引用其他表中的列。
定义自定义的错误信息。
维护非规范化数据。级联是指保持主键与外键之间的关系，非规范化数据是指派生的，冗余的数据值。
比较修改前后数据的状态。绝大多数触发器提供了访问由 INSERT、UPDATE 或 DELETE 语句引起的数据变化前后状态的能力。
这样，就允许在触发器中引用由修改语句所影响的行。
*/
-- 使用触发器时的考虑
/*
触发器是后反应的（Reactive），约束是前反应的（Proactice）
在触发器定义的表中执行 DML 语句后，触发器执行。而约束是在 DML 语句执行前进行检查。
首先检查约束
如果在触发器表上存在约束，则约束在触发器之前进行检查。如果违反约束，触发器就不执行。
表对任何动作可以有多个触发器
允许在一个表上嵌套几个触发器。一个表可以定义多个触发器，每个触发器可以为一个或多个动作定义。
表的所有者能够指定最先和最后触发的触发器
当一个表上有多个触发器时，表的拥有者可以使用 sp_settriggerorder 系统存储过程来指定最先和最后激活的触发器。
其余的触发器的顺序不能被设置。
用户必须具有执行触发器定义的所有语句的权限
触发器创建者必须拥有在所有受影响的表上执行触发器所定义的所有语句的权限。
如果触发器中的任何部分的 T-SQL 语句的权限被拒绝，整个事务将被回滚。
表的属主不能在视图或临时表上创建 AFTER 触发器，但是触发器可以引用视图和临时表。
*/
-- 8.2 --------------------------------------------------
-- 定义触发器
-- 创建触发器
-- 创建时须定义 触发器的名称、触发器所在的表、触发器所监视的动作
CREATE TRIGGER trigger_name
ON table_name
FOR INSERT|UPDATE|DELETE
AS
......
GO
/*
模拟一个排队取号系统，当号码到达 50 之后，重新回到 1 开始计数。
即表中的 idx 到达 50 后，把表情空，把 IDENTITY 种子设置为 1 。
use Test
Go
create table t_queue(idx int identity(1,1),create_time datetime default(getdate()))
*/
create trigger tr_reset_queue on t_queue
for insert
as
if @@identity>50
begin
truncate table t_queue
declare @now datetime
set @now=getdate()
insert t_queue (create_time) values (@now)
end
Go
declare @now datetime
set @now=getdate()
insert t_queue (create_time) values (@now)
select * from t_queue order by idx desc
-- 修改触发器： 加密触发器、禁用触发器
-- 加密触发器
alter trigger tr_reset_queue on t_queue
with encryption
for insert
as
if @@identity>100
begin
truncate table t_queue
declare @now datetime
set @now=getdate()
insert t_queue (create_time) values (@now)
end
Go
sp_helptext tr_reset_queue
-- 禁用触发器
alter table t_queue
disable trigger tr_reset_queue
alter table t_queue
disable trigger all
-- 启用触发器
alter table t_queue
enable trigger tr_reset_queue
-- 删除触发器
-- 可以直接删除触发器，也可以通过删除触发器所在的表来删除触发器。
drop trigger tr_reset_queue
或者
drop table t_queue
-- 练习 --------------------------------------------------
-- 1 在 11 人制足球比赛中，球员在严重犯规后可被罚下场。
-- 但是当场上一个球队中的球员被罚到只剩 7 人时，无论再怎么犯规，都不能被罚下场。
/*
use Test
Go
create table dbo.t_RealMadrid_On(Name varchar(50),Num tinyint,Position char(3))
insert t_RealMadrid_On values ('Casillas',1,'GK')
insert t_RealMadrid_On values ('Salgado',2,'RB')
insert t_RealMadrid_On values ('Samuel',19,'CBT')
insert t_RealMadrid_On values ('Pavon',22,'CBT')
insert t_RealMadrid_On values ('Roberto Carlos',3,'LB')
insert t_RealMadrid_On values ('Figo',10,'RMF')
insert t_RealMadrid_On values ('Backham',23,'CMF')
insert t_RealMadrid_On values ('Ivan Helguera',6,'DMF')
insert t_RealMadrid_On values ('Zidane',5,'OMF')
insert t_RealMadrid_On values ('Ronaldo',9,'CF')
insert t_RealMadrid_On values ('Raul',7,'CF')
select * from t_RealMadrid_On
*/
-- 答案 --------------------------------------------------
-- 1
create trigger tr_player_delete
on t_RealMadrid_On
for delete
as
if (select count(*) from t_RealMadrid_On)<7
begin
rollback tran
raiserror('At Least 7 Players should Be On',16,1)
end
GO
delete t_RealMadrid_On where Num in (select max(Num) from t_RealMadrid_On)
select * from t_RealMadrid_On
-- 2 使用触发器完成以下功能：当删除 t_Product 表时，检查 t_OrderHist
-- 中是否包含该产品的订购记录，如果没有，可以删除产品，否则回滚并报错。
use Test
Go
create table dbo.t_Product(p_id int,p_name varchar(50),stock int)
insert t_Product select 1,'SONY CD-R 52X',25
insert t_Product select 2,'SONY DVD+R 16X',0
insert t_Product select 3,'SONY DVD+R 8X',20
insert t_Product select 4,'MITSUBISHI DVD+R 16X',10
Go
create table dbo.t_OrderHist(o_id int,p_id int,quantity int,orderdate smalldatetime default(getdate()))
insert t_OrderHist (o_id,p_id,quantity) select 10522,10,7
insert t_OrderHist (o_id,p_id,quantity) select 10523,2,9
insert t_OrderHist (o_id,p_id,quantity) select 10522,41,24
insert t_OrderHist (o_id,p_id,quantity) select 10522,7,5
GO
select * from t_Product
select * from t_OrderHist
create trigger tr_product_delete
on t_Product
for delete
as
declare @p_id int
select @p_id = p_id from deleted
if @p_id in (select p_id from t_OrderHist )
begin
raiserror('t_OrderHist中是否包含该产品的订购记录不允许删除',16,1)
rollback tran
end
GO
查看更多相关问题 >>