SQL Server 触发器

1、概念
触发器是一种特殊的存储过程，它不能被显式地调用，而是在往表中插入记录﹑更新记录或者删除记录时被自动地激活，自动执行的存储过程。 所以触发器可以用来实现对表实施复杂的完整性约束。

2、deleted 表和 inserted 表
触发器语句中使用了两种特殊的表：deleted 表和 inserted 表。
Deleted 表用于存储 DELETE 和 UPDATE 语句所影响的行的复本
Inserted 表用于存储 INSERT 和 UPDATE 语句所影响的行的副本

1.插入操作（Insert）
Inserted表有数据，Deleted表无数据

2.删除操作（Delete）
Inserted表无数据，Deleted表有数据

3.更新操作（Update）
Inserted表有数据（新数据），Deleted表有数据（旧数据）

4.查询所有触发器

select * from Sysobjects where xtype = 'TR'

select name from sysobjects where instrig=(select id from sysobjects where xtype='TR')

select a.name 数据表名
,sysobjects.name as 触发器名
,sysobjects.crdate as 创建时间 
from sysobjects
left join (select *from sysobjects where xtype='U')as a on sysobjects.parent_obj=a.id
where sysobjects.xtype= 'TR'

select object_name(a.parent_obj) as [表名]
,a.name AS [触发器名称]
,(case when b.is_disabled = 0 then '启用' else '禁用' end) as [状态]
,b.create_date as [创建日期]
,b.modify_date as [修改日期]
,c.text as [触发器语句]
from sysobjects a
INNER JOIN sys.triggers b ON b.object_id = a.id
INNER JOIN syscomments c ON c.id = a.id
--where a.xtype = 'tr' and c.text like '%bom_weight_wkx%'
order by [表名]

--select distinct  b.name,b.type from dbo.syscomments a,  dbo.sysobjects b where a.id = b.id AND b.xtype = 'p'  AND a.text like '%ORG_PATIENT%'

3、触发器类型
SQL Server 有3类触发器【触发器是在对应表的下面】
Insert：向数据表插入数据时，调用insert触发器。
Update：更新数据时调用update触发器。
Delete：删除数据时执行Delete触发器。
Sql server中这三类触发器总是在执行操作语句后才被自动调用。

4、工作原理
After（同for）：先执行增、删、改、操作，再激活触发器操作。
Instead of：在执行增、删、改、操作之前，先激活触发器。

5、语法
查看数据库已有触发器

select * from sysobjects where xtype='TR'

查看单个触发器

exec sp_helptext 'bed_insert'

基础语法

create trigger 触发器名
on 表名
[for,after,Instead of]
[delete,insert,update]
as

--语法
Create trigger trigger_name----创建名称
On {table|view}----定义在表或者视图上
[with encryption]----加密元数据
{
{ {for|after|instead of}{[insert][,][update][,][delete]}触发器执行的条件
 [with append]
 [ont for replication]
As----触发器要执行的操作
[ {if update(column)----判断执行的是什么操作
 [{and|or}update(column)][,…n]
 |if(columns_updated(){bitwise_operator}updated_bitmask)----判断是否插入跟新了数据
 {comparison_operator}column[,…n]
}]
Sql_statement[,…n]操作语句
 }
}

--语法
create or REPLACE TRIGGER salary_raiu
AFTER INSERT OR UPDATE OF amount ON salary
FOR EACH ROW
BEGIN
    IF inserting THEN
        dbms_output.put_line(‘插入’)
    ELSIF updating THEN
dbms_output.put_line(‘更新amount列’)
         END IF
END

insert触发器【实战】

--示例1【sys_user表插入触发器】
CREATE trigger [dbo].[trigger_user_insert]
on [dbo].[sys_user]
for insert
as
declare @id int,@user_name varchar(255),@real_name nvarchar(255)
select @id=id,@user_name=user_name,@real_name=real_name from sys_user
--判断【sys_user】表中【id=？】的数据是否存在
if @id not in(select id from sys_user)
begin
 rollback transaction
 print '用户表中不存在的id，取消插入数据'
 return
end
--判断【sys_department】表中【id=2】的数据是否存在
else if 2 not in(select id from sys_department)
begin
  print '部门表中不存在的id，取消插入数据'
  rollback transaction
end
else
begin
  print convert(nvarchar(255),@id) + ',' + @user_name + ',' + @real_name
  insert into [dbo].[sys_user_department] (user_id,department_id,created_time,modify_time,status) values (@id,1,getdate(),getdate(),'A')
end
GO

ALTER TABLE [dbo].[sys_user] ENABLE TRIGGER [trigger_user_insert]
GO


--示例2【sys_user_department插入触发器】
CREATE trigger [dbo].[trigger_user_department_insert]
on [dbo].[sys_user_department]
for insert
as
declare @user_id int,@department_id int
select @user_id=user_id,@department_id=department_id from sys_user_department
--判断【sys_user】表中【id=？】的数据是否存在
if @user_id not in(select id from sys_user)
begin
 print '用户表中不存在的id，取消插入数据'
 rollback transaction
end
--判断【sys_department】表中【id=？】的数据是否存在
else if @department_id not in(select id from sys_department)
begin
  print '部门表中不存在的id，取消插入数据'
  rollback transaction
end
else
begin
  print '插入成功！'
end
GO

insert触发器

create trigger bed_insert 
on base_bed 
for insert
as
select * from base_bed


--示例1
create trigger trigger_insert
on score
for insert
as
declare @xuehao int,@kehao int
select @xuehao=学号,@kehao=课号
from inserted
if @xuehao not in(select 学号 from student)
begin
 rollback transaction
 print'学生表中不存在的学号，取消插入数据'
end
if @kehao not in(select 课号 from course)
begin
 rollback transaction
 print'成绩表中不存在的课号，取消插入数据'
end


--示例2
create trigger tri_Insert 
on Borrow 
after insert
as
    --先要判断该书在不在馆
    if not exists(select bkState from Book, inserted where Book.bkID = inserted.bkID)
    begin
        raiserror('该书不在馆，无法插入！',10,1)
        return
    end

    update Reader set rdBorrowQty = rdBorrowQty + 1
    from Reader, inserted
    where Reader.rdID = inserted.rdID

    update Book set bkState = 0
    from Book, inserted
    where Book.bkID = inserted.bkID

--调用：
insert into Borrow values ('rd2017004','bk2017004',GETDATE(),30,null)


--示例3【限制插入】
create trigger dept_emp_insert
on emp
after insert
as
if(select inserted.deptid from dept,inserted where dept.id = inserted.deptid) = 2
begin
   print 'the emp is full'
   rollback transaction
end

update触发器

create trigger user_update 
on base_bed 
for update
as 
select * from base_bed

delete触发器

create trigger user_delete 
on base_bed 
for delete
as 
select * from base_bed

insert，update，delete触发器

create trigger user_cud
on base_bed 
for insert,update,delete
as
select * from base_bed

*
*
*