DIY博客园

发器是一种特殊类型的存储过程，它不同于之前的我们介绍的存储过程。触发器主要是通过事件进行触发被自动调用执行的。而存储过程可以通过存储过程的名称被调用。
下面介绍三种常用的触发器，insert，update,delete 触发器

 --创建insert插入类型触发器

if (object_id('tgr_classes_insert', 'tr') is not null)

drop trigger tgr_classes_insert

go

create trigger tgr_classes_insert

on classes

for insert --插入触发

as

--定义变量

declare @id int, @name varchar(20), @temp int;

--在inserted表中查询已经插入记录信息

select @id = id, @name = name from inserted;

set @name = @name + convert(varchar, @id);

set @temp = @id / 2;

insert into student values(@name, 18 + @id, @temp, @id);

print '添加学生成功！';

go

--插入数据

insert into classes values('5班', getDate());

--查询数据

select * from classes;

select * from student order by id;

 

--另一种插入触发器 使用inserted

if (object_id('tgr_SH_CONFIRMATION_insert', 'tr') is not null)

drop trigger tgr_SH_CONFIRMATION_insert

go

create trigger tgr_SH_CONFIRMATION_insert

on SH_CONFIRMATION

for insert --插入触发

as

DELETE FROM SH_CONFIRMATION_STO

WHERE INVOICE_NUMBER IN( SELECT INVOICE_NUMBER FROM inserted)

insert into SH_CONFIRMATION_STO

select INVOICE_NUMBER,INPUT_DATETIME,CONFIRMATION_NUMBER from inserted

go 

 --delete删除类型触发器

if (object_id('tgr_classes_delete', 'TR') is not null)

drop trigger tgr_classes_delete

go

create trigger tgr_classes_delete

on classes

for delete --删除触发

as

print '备份数据中……';

if (object_id('classesBackup', 'U') is not null)

--存在classesBackup，直接插入数据

insert into classesBackup select name, createDate from deleted;

else

--不存在classesBackup创建再插入

select * into classesBackup from deleted;

print '备份数据成功！';

go

--

--不显示影响行数

--set nocount on;

delete classes where name = '5班';

--查询数据

select * from classes;

select * from classesBackup; 

 --update更新类型触发器

if (object_id('tgr_classes_update', 'TR') is not null)

drop trigger tgr_classes_update

go

create trigger tgr_classes_update

on classes

for update

as

declare @oldName varchar(20), @newName varchar(20);

--更新前的数据

select @oldName = name from deleted;

if (exists (select * from student where name like '%'+ @oldName + '%'))

begin

--更新后的数据

select @newName = name from inserted;

update student set name = replace(name, @oldName, @newName) where name like '%'+ @oldName + '%';

print '级联修改数据成功！';

end

else

print '无需修改student表！';

go

--查询数据

select * from student order by id;

select * from classes;

update classes set name = '五班' where name = '5班';