sql触发器知识

触发器中的Inserted和deleted临时表：

 SQL2000中，inserted表和deleted表用于存放对表中数据行的修改信息。他们是触发器执行时自动创建的，放在内存中，是临时表。当触发器工作完成，它们也被删除。它们是只读表，不能向它们写入内容。

 

inserted表：用来存储INSERT和UPDATE语句所影响的行的副本。意思就是在inserted表中临时保存了被插入或被更新后的记录行。在执行 INSERT 或UPDATE 语句时，新加行被同时添加到inserted表和触发器表中。因此，可以从inserted表检查插入的数据是否满足需求，如不满足则回滚撤消操(cāo)作。

 

deleted表：用来存储DELETE和UPDATE语句所影响行的副本。意思是在delete表中临时保存了被删除或被更新前的记录行。在执行 DELETE 或 UPDATE 语句时，行从触发器表中删除，并传到deleted表中。所以可以从deleted表中检查删除的数据行是否能删除。

 

所以当表中某条记录的某项值发生变化时，变化前的值已经通过系统自动创建的临时表deleted表和inserted表保存了被删除行或插入的记录行的副本。我们可以从这两个表中查询出变化前的值并赋给变量。

我给你个表对应一下这两临时表的功能：

修改操作 inserted表 deleted表 
增加(INSERT)记录时 存放新增的记录 不记录 
删除(DELETE)时 不记录 存放被删除的记录 
修改(UPDATE)时 存放用来更新的记录 存放更新前的记录

更新前的值可以从deleted表中查询出来，直接：select * from deleted 
“*”可以换成你要查询的列名。 
例如：我给个数据备份的，表table2用来做备份表,前提是表table1中有数据被删除。 
/*---检查是否存在触发器---*/ 
if exists(select name from sysobjects where name = tri_delete) 
drop trigger tri_delete 
go 
/*--在表table2上创建delete触发器---*/ 
create trigger tri_delete on table2 
for delete 
as 
print '开始数据备份.....' 
if not exists(select * from sysobjects where name = 'table1') 
select * into table1 from deleted---从deleted表中获取被删除的数据 
else 
insert into table1 select * from deleted 
print '备份成功，备份表中数据为：' 
select * from table1 
go 
/*---测试触发器：删除数据---*/ 
SET NOCOUNT ON ---不显示T—SQL语句影响的行数 
delete table2 
---查看结果 
select * from table2

delete触发器的典型应用是数据备份；update触发器主要用来跟踪数据的变化；下面这个就是把变化前后的项的值写入table2的例子，这个例子我假设了2个表table1,table2各有3列，都是Uid,Uname,sex且全部允许为空：结果是表2得到更改项前后变化值。 
if exists(select name from sysobjects where name = 'tri_test') 
drop trigger tri_test 
go

create trigger tri_test on table1 
for update 
as 
declare @beforesex varchar(8),@aftersex varchar(8) 
select @beforesex = sex from deleted ---变更前的数据 
select @aftersex = sex from inserted ---变更后的数据 
if update(sex) 
begin 
---把改变前后的值写入table2中 
insert into table2 (sex) values (@beforesex) 
insert into table2 (sex) values (@aftersex) 
end 
go

 

触发器的例子：

create table Student(   --学生表

  StudentID int primary key,--学号

  StudentName varchar(20)--学生名称

)

create table BorrowRecord(--学生借书登记表

  BorrowRecord int identity(1,1),--流水号

  StudentID int,--学号

  BorrowDate datetime,--借出时间

  ReturnDate datetime--归还时间

)

create table GradeStudent(

 GradeID int identity(1,1),

 StudentID int 

)

--创建一个触发器（修改学生表中的学生id时同时修改学生借书记录表中的学号）

--1.更新操作

--修改方法一

alter Trigger t_update_Student

  on student

  for Update

  as

  if Update(StudentID)

  begin

     declare @gi int

     select @gi=GradeID from GradeStudent

    Update br

      set br.StudentID = ins.StudentID

      from BorrowRecord br,Deleted del,inserted ins

      where br.StudentID = del.StudentID;

    update gs

      set gs.StudentID = ins.StudentID

      from GradeStudent gs,Deleted del,inserted ins

      where gs.StudentID = del.StudentID

  end

      

--修改方法二

Create Trigger t_update_Student

On Student

for Update

As

 if Update(StudentID)

 begin

   declare @newid varchar(50)

   declare @oldid varchar(50)

 select @oldid = StudentID from deleted     --deleted表保存了被更新前的数据

   select @newid = StudentID from Inserted  --inserted表保存了被更新后的数据

   update BorrowRecord set StudentID=@newid where StudentID=@oldid

 end

 

 --创建一个触发器(删除学生表中的学生id时同时删除学生借书记录中的学号)

 Create Trigger t_del_Student

 On Student

 for delete

 As

  Begin

  declare @sid varchar(50)

  select @sid=StudentID from deleted

  delete from BorrowRecord where StudentID=@sid

  end

  

  --检查是否存在触发器可结合删除创建触发器语句执行

  if exists(select name 

  from sysobjects

  where name = 't_del_Student'--触发器名字

  and type='tr')  --tr表示触发器类型

  begin

  --drop trigger t_del_Student --执行删除操作

  print 'yes'

  end

    else

     begin --执行相应的操作

       print 'no'

     end

     --解释:sysobjects  为系统系统对象表。保存当前数据库的对象，如约束、默认值、日志、规则、存储过程等

     

--删除触发器

  drop trigger t_del_student

--插入数据

 insert into Student(StudentID,StudentName)values

 (1001,'陈一'),

 (1002,'赵二'),

 (1003,'张三'),

 (1004,'李四'),

 (1005,'王五')

 

 insert into BorrowRecord(StudentID,BorrowDate,ReturnDate)values

 (1001,getdate(),getdate()),

 (1002,getdate(),getdate()),

 (1003,getdate(),getdate()),

 (1004,getdate(),getdate()),

 (1005,getdate(),getdate())

 

 insert into GradeStudent(StudentID)values(1001),(1002),(1003),(1004),(1005)

 --查询表数据

 select * from student

 select * from borrowrecord

 select * from gradestudent

 --修改Student表中张三的记录

 update Student set StudentID='100101' where studentid='1001'

 --删除测试

 delete from Student where StudentID='100101'

 

 --查看触发器内容

 exec sp_helptext 't_update_Student'

 --查看触发器属性(看关联到表上的触发器)

 exec sp_helptrigger Student 

 

 --触发器回滚

   --1.如果程序不想用户修改学生名称则可以修改此触发器

   --2.如果用户要修改学生名称（StudentName）则回滚

   create trigger t_roll_Student

   on Student

   for update

   as

   if update(StudentName)

   rollback tran

   

 --禁用、启用、重命名触发器

  --禁用

  alter table Student disable t_del_Student

  --启用

  alter table Student enable t_update_Student

  --重命名(exec sp_rename 原名称,新名称)

  exec sp_rename t_update_Student,t_up_Student 

  

  --触发器增加

  Create Trigger t_add_Student

  on student

  for insert

  as

  begin

    declare @newid varchar(50)

    select @newid=StudentID from inserted --得到插入的ID

    insert into BorrowRecord(StudentID,BorrowDate,ReturnDate)values(@newid,getdate(),getdate())

    end

   

--测试增加触发器

insert into Student(StudentID,StudentName)values(1001,'陈一')