mssql sqlserver 触发器汇总专题


摘要:
下文讲述触发器的相关知识,如下所示:
实验环境:sql server 2008 R2



1.触发器的概念

触发器是sqlserver引擎中附加在数据库或数据表上的一种触发事件,是一种保证数据完整性的方法,加强表数据约束、加强业务规则、数据完整性。
触发器是一种无参数输入的特殊存储过程,触发器不可以由用户主动触发,由sql引擎根据相应的规则触发相应的事件。
常见的表触发器分为:
DML触发器、DDL触发器和登录触发器
DML(Data Manipulation Language)触发器
DML触发器是一种附加在指定表或视图上的代码块,
当数据表上执行相应操作时,将触发相应事件,如下所示:
insert触发器:当向表中插入数据时触发;
delete触发器:当对表数据删除时触发;
update触发器:当对表中数据修改时触发。
————–DML应用场景——————-
级联更改
防止恶意或者错误增删改操作,
强制约束时
—————DDL触发器——————–
DDL(数据定义语言,Data Definition Language)触发器
当数据库中相关定义信息被操作时,触发相应事件
例:对表 create drop alter 的相关操作
————–login触发器——————
此触发器主要sqlserver被登陆时,触发器相应的事件,达到监控sqlserver登录的相关安全或日志信息

2.触发器与存储过程的区别:

触发器和存储过程的最大区别在于:执行方式不同
触发器不能通过调用执行,只能由程序内部触发
存储过程: 可以由显式执行,也可以由触发器调用执行

3.触发器的优点

1.触发器根据定义好的事件自动触发执行,不会出现漏执行
2.可以达到修改一个表数据,同时实现级联修改
3.触发器可以对表数据进行强制约束

4.触发器的功能

1.限制表之间的数据完整性
2.表之间的级联操作
3.返回相关自定义错误信息

5.触发器的原理

例:DML触发器
触发器被激活运行时:
系统自动在内存中创建deleted表或inserted表;
此表只读,禁止修改,触发器执行完成后,自动删除。
inserted表:
临时保存了插入或更新后的记录行(此表可能会多行记录)  
deleted表:
临时保存了删除或更新前的记录行(此表可能会多行记录)
———————————————————–
inserted表和deleted表生成场景 
update操作时会产生:inserted表 deleted表
insert操作时会产生:inserted表
delete操作时会产生:deleted表


6.触发器举例

---触发器语法---
CREATE TRIGGER trigger_name
 ON table_name
 [WITH ENCRYPTION]
  FOR | AFTER | INSTEAD OF [DELETE, INSERT, UPDATE]
 AS
  T-SQL语句
GO
--with encryption 是否对触发器进行加密
--after|instead of 表触发器类型
--delete,insert,update触发器所对应事件
------------------------------------------------------
--------------------例--------------------------------
------------------------------------------------------
--创建测试表
create table [maomao365.com](
    keyId int identity(1,1) primary key,
    info varchar(80),
    infoExtend char(30)
)
 
--创建insert触发器
create trigger trig_insert
on [maomao365.com]
after insert
as
begin
    if object_id(N'maomao_sum',N'U') is null--判断maomao_sum表是否存在
        create table maomao_sum(maomaoAll int default(0));--统计表行数
    declare @qty int;
    select @qty= count(*)from [maomao365.com];
    if not exists (select * from maomao_sum)--判断表中是否有记录
        insert into maomao_sum values(0);
    update maomao_sum  set maomaoAll =@qty; --把更新后总数插入到maomao_sum 表中
end
 
--测试触发器trig_insert-->功能是向maomao365插入数据的同时级联插入到maomaoAll 表中,更新qty
--因为是后触发器,所以先插入数据后,才触发触发器trig_insert;
insert into  [maomao365.com](info,infoExtend)values(N'sql教程1','maomao365');
select maomaoAll  from maomao_sum;   
insert into  [maomao365.com](info,infoExtend)values(N'sql教程2','maomao365');         
select maomaoAll   from maomao_sum;
insert into  [maomao365.com](info,infoExtend)values(N'sql教程3','maomao365');           
select maomaoAll   from maomao_sum;


---创建insert_stop,禁止用户向maomao_sum表中插入数据
create trigger insert_stop
on maomao_sum
after insert
as
begin
    RAISERROR('此表禁止插入记录,操作被终止',1,1)
    rollback transaction
end
 
--触发触发器insert_stop
insert into maomao_sum(maomaoAll) values(88);
----系统将输出相应的错误信息

----2.delete触发器示例
     当一张表创建了delete触发器,当我们对表执行delete操作后,会触发delete触发器,在delete触发器中,我们可以通过获取deleted表数据,获取删除的相应记录。  
create trigger tr_del
on [maomao365.com]
after delete
as
begin
    select keyId,info,infoExtend
    from deleted
end;
 
--执行一一条delete语句触发tr_del触发器
delete from [maomao365.com]where keyId=1;


---3.UPDATE触发器
    当我们对表执行update操作后,将会触发update触发器,update触发器中可以通过获取
     deleted表中的内容,查看修改前的数据
     inserted表中的内容,则对应修改后的数据 
 
create trigger tr_updateInfo
on [maomao365.com]
after update
as
begin 
    select keyId,info,infoExtend from deleted
    select keyId,info,infoExtend from inserted
end
 
--创建完成,执行一条update语句触发trig_update触发器
update [maomao365.com]
   set info='test20181207',infoExtend='blog教程'
   where keyID=2;

---instead of 触发器
     ---以上讲述的触发器为after触发器。
     ---after触发器为执行sql代码后的触发器再进行相关的运行操作。
     ---instead of触发器,是首先将待操作数据放入相应的insterted表和deleted表,然后当触发器执行完毕后,再执行对基础表的数据操作。 
 
create trigger tr_insteadOf
on [maomao365.com]
instead of insert
as
begin
    declare @qty int;
    select @qty=(select keyId from inserted)
if(@keyId >88)
    select '系统数据不能超过88行'
end
go

当对表插入数据 [maomao365.com]时,表keyId大于 88时,则禁止对数据插入操作。
...