SQL Server 触发器

SQLServer触发器创建、删除、修改、查看

SQL Server 触发器

SQL Server 触发器

SQL Server 中 RAISERROR 的用法

after触发器和instead of 触发器的区别

 

我理解的触发器：其实也就是你在进行 insert,update或者 delete操作的时候，可以在逻辑里面干一些其他的事情。比如你在更新一个表的某条数据的时候，你就触发了你之前已经定义好了的一个after update 类型的触发器。这里触发器里面你可以“将另外一个表与之关联的数据也跟新了”或者干些别的事情。等等，你要你想干的事，都可以。

 

 

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

二: SQL Server为每个触发器都创建了两个专用表:Inserted表和Deleted表。两个表由系统来维护﹐它们存在于内存中而不是在数据库中。这两个表的结构总是与被该触发器作用的表的结构相同。触发器执行 完成后﹐与该触发器相关的这两个表也被删除。 

Deleted表存放由于执行Delete或Update语句而要从表中删除的所有行。 
Inserted表存放由于执行Insert或Update语句而要向表中插入的所有行。


SQL Server 包括三种常规类型的触发器：DML 触发器、DDL 触发器和登录触发器。

本文主要讲述DML触发器，DML触发器有两种：AFTER，INSTEAD OF触发器，同时DML 触发器使用 deleted 和 inserted 逻辑（概念）表。 它们在结构上类似于定义了触发器的表，即对其尝试执行了用户操作的表。 在 deleted 和 inserted 表保存了可能会被用户更改的行的旧值或新值。

• 对于INSERT 操作，inserted保留新增的记录，deleted无记录
• 对于DELETE 操作，inserted无记录，deleted保留被删除的记录
• 对于UPDATE操作，inserted保留修改后的记录，deleted保留修改前的记录

关键在于Inserted表
触发器语句中使用了两种特殊的表：deleted 表和 inserted 表。
Deleted 表用于存储 DELETE 和 UPDATE 语句所影响的行的复本。在执行 DELETE 或 UPDATE 语句时，行从触发器表中删除，并传输到 deleted 表中。Deleted 表和触发器表通常没有相同的行。

Inserted 表用于存储 INSERT 和 UPDATE 语句所影响的行的副本。在一个插入或更新事务处理中，新建行被同时添加到 inserted 表和触发器表中。Inserted 表中的行是触发器表中新行的副本。

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

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

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

 

其中AFTER 类型触发器要求只有执行某一操作（INSERT UPDATE DELETE） 之后，触发器才被触发，且只能在表上定义。可以为针对表的同一操作定义多个触发器。对于AFTER 触发器，可以定义哪一个触发器被最先触发，哪一个被最后触发，通常使用系统过程sp_settriggerorder 来完成此任务。 

INSTEAD OF 触发器表示并不执行其所定义的操作（INSERT、 UPDATE、 DELETE），而仅是执行触发器本身。既可在表上定义INSTEAD OF 触发器，也可以在视图上定义INSTEAD OF 触发器，但对同一操作只能定义一个INSTEAD OF 触发器。 

 

（1）AFTER触发器：它是在执行INSERT、UPDATE、DELETE语句操作之后执行触发器操作。它主要是用于记录变更后的处理或检查，一旦发生错误，可以用Rollback Transaction语句来回滚本次扣件，不过不能对视图定义AFTER触发器。

（2）INSTEAD OF触发器：它在执行INSERT、UPDATE、DELETE语句操作之前执行触发器本身所定义的操作。而INSTEAD OF触发器是可以定义在视图上的。

<1>

 

--向表“j0261”插入数据时，检查学号是否存在于表“j026”中，如存在则进行插入操作，否则就不插入。

create trigger checkid on j0261  --创建一个名字为checkid的触发器，作用的表是j0261
instead of insert
as
if(not exists(slect * from j026 where 学号=(select 学号 from inserted)))--如果插入j0261的那一条数据里的'学号'不存在j026表里
rollback transaction --将显式事务或隐性事务回滚到事务的起点或事务内的某个保存点
print '要处理记录的学号不存在'
else
insert into j0261 select * from inserted 
print '记录插入成功'

实例：
--常见的触发器有三种：分别应用于Insert , Update , Delete 事件。  

use DataStudent   --指定数据库

create table Student  --创建表
(
	studentID int primary key --学号
	
)
go
create table BorrowRecord  --创建表
(
	[BorrowRecord] int identity(1,1),--自增表示列,流水号
	StudentID int, --学号
	BorrowDate datetime, --借出时间
	ReturnDate datetime --归还时间
	
)
go

--1.如果我更改了学生的学号,我希望他的借书记录仍然与这个学生相关(也就是同时更改借书记录表的学号);          
--2.如果该学生已经毕业，我希望删除他的学号的同时，也删除它的借书记录。

--触发器1
IF(EXISTS (SELECT * FROM  sys.objects WHERE name='Tri_Student_update'))
drop trigger truStudent
go
create Trigger Tri_Student_update on Student
for update --如果仅指定 FOR 关键字，则 AFTER 为默认值。即:for updae可以写成after update。For 是触发器默认的触发点，默认为AFTER，所以最好直接用AFTER，因为即便用For的话，编译器也会自动编译转换变成AFTER！
as
if UPDATE(studentID)  --如果这个studentID字段被更新的话
begin
declare @oldID int,@newsID int
select @oldID=studentID from deleted  --获取没有更新前的studentID
select @newsID=studentID from inserted  --获取更新后的studentID
update BorrowRecord set studentID =@newsID WHERE studentID=@oldID
end


--触发器2
if(exists(select * from sys.objects where name='Tri_student_delete'))
drop Trigger trdStudent
go
create Trigger Tri_student_delete on Student
for delete  --如果仅指定 FOR 关键字，则 AFTER 为默认值。即：for delete可以写成after delete
as
begin
declare @oldID int 
select @oldID=studentID from deleted  --获取没有删除前的studentID
delete from BorrowRecord where StudentID=@oldID 
end



-----------------------------------------------------------------------重点，效果

--执行以下update语句，就会在更新Student表的同时BorrowRecord表里的studentID也会一块得到更新
UPDATE Student SET studentID=3 where studentID=1
go

--执行以下delete语句，就会在删除Student表某行时BorrowRecord表里对应的字段的'行'也会得到删除
delete from Student where studentID=3
go

--select * from Student
--select * from  BorrowRecord
--go

 

 

例子：

 

 

参考资料：http://www.cnblogs.com/hoojo/archive/2011/07/20/2111316.html

创建触发器

 

create trigger Tri_User_Isdefault on T_User
for update
as
if update(Isdefault)
begin 
	set nocount on--不返回计数（即：不返回受 Transact-SQL 语句影响的行数），提高效率
	declare @new bit,@id int
	select @new=Isdefault ,@id=id from inserted --将新的Isdefault值和id值查询出来
	if @new=1
		update T_User set Isdefault=0 where id<>@id
end

 

--当执行这条语句的时候，就会将id=2的那条记录的Isdefault字段的值设为1,同时将T_User表的Id不等于2的记录的Isdefault字段的值设为0
update T_User set Isdefault=1 where id=2