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,sname,ssex,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 '成功'