创建触发器

90 阅读 0 评论 60 点赞

我是靠谱客的博主年轻刺猬，最近开发中收集的这篇文章主要介绍创建触发器，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

create TRIGGER trigger_insert_test1
ON test2
AFTER insert
AS
BEGIN
– SET NOCOUNT ON added to prevent extra result sets from
– interfering with SELECT statements.
SET NOCOUNT ON;
insert into test1 select ‘y’,‘o’ from test2 where a=‘C’ ##如果不加限制条件的话，会复制相同的行数到test1
– Insert statements for trigger here

END
GO

select * from test1
select * from test2

insert into test2 select ‘C’,'5

在xs表上创建no_insert触发器，禁止添加记录
create trigger no_insert
on xs
after insert
as
begin
raiserror(‘xs表不允许插入记录’,1,1);
rollback transaction
end

在xs表上创建一个delete触发器xs_del,要求返回被删除的记录
create trigger xs_del
on xs
after delete
as
begin
select 学号 as 被删除学生记录,姓名 from deleted
end

delete from xs where 学号=123

在xs表上创建一个update触发器xs_update,要求返回被删除的记录
create trigger xs_update
on xs
after update
as
begin
select 姓名 as 更新后的姓名,学号 from inserted
select 姓名 as 更新前的姓名,学号 from deleted
end

在xs表上创建一个instead触发器xs_instead,要求在插入记录时，如果总学分大于30，就拒绝插入，提示‘总学分不符合要求’
create trigger xs_instead
on xs
instead of insert
as
begin declare @stu_credits int;
select @stu_credits=(select 总学分 from inserted)
if @stu_credits>30
select ‘总学分不符合要求’ as 失败原因
end

在xs表上创建safty触发器，拒绝用户对数据库中的表进行删除和更新操作
create trigger safty
on database
for drop_table,after_table
as
begin
print ‘当前数据库禁止更改删除操作’
rollback transaction
end