SQLSERVER2005数据库的触发器建立

先创建一个学生表：

create table student
(
snum varchar(9) primary key,
sname char(8),
ssex char(4),
sage int
)

下面是创建的触发器：

创建删除触发器当删除发生时，将数据插入到备份表中；

create trigger tri_delete

on student

for delete

as

print '备份数据---'

insert into backup_s(snum,sname,ssex,sage)

select snum,sage from deleted

if @@Error =0

? begin

??? print'备份成功,bacup_table中的数据为：'

??? select *from backup_s

??? print'备份成功,backup_s中的数据为：'

??? select *from student

? end;

else

? begin

??? print'备份失败！'

??? rollback

? end

创建插入触发器，当插入的数据中年龄大于15小于60时执行成功，否则不成功；

create trigger tri_insert

on student

for insert

as

declare @isnum varchar(9)

declare @isname char(8)

declare @issex char(4)

declare @isage int

select @isnum=snum,@isname=sname,@issex=ssex,@isage=sage from inserted

if @isage<15

?? begin

??? print '年龄不合法'

??? select * from student

??? rollback

?? end

else if @isage>60

?? begin

??? print '年龄不合法'

??? select *from student

??? rollback

?? end

else

??? begin

??? print '成功'

??? select * from student

??? end

创建更新触发器，当更新的数据中年龄大于15小于60时执行成功，否则不成功；

create trigger tri_update

on student

for update

as

declare @oldsage int

declare @newsage int

select @oldsage=sage from deleted

select @newsage=sage from inserted

if @newsage<15

? begin

??? print '年龄不合法'

??? rollback

? end

else if @newsage>60

? begin

??? print '年龄不合法'

??? rollback

? end

else

??? print '成功'

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!