代码

/*【例】建立一个触发器tr_cj_insert ，
当向cj表中添加数据时，
如果添加的数据与xs表中的数据不匹配
（没有对应的学号），
则将此数据删除，
同时输出“插入的记录不符合学生表中的记录！”
*/
CREATE TRIGGER tr_cj_insert
ON cj
FOR INSERT
AS
BEGIN
  DECLARE @xh char(10)
  Select @xh=Inserted.学号 from Inserted
  If not exists(select 学号
                from xs
                where xs.学号=@xh)
     Delete cj where 学号=@xh
  print '插入的记录不符合学生表中的记录！'
END

alter TRIGGER tr_cj_insert
ON cj
FOR INSERT
AS
BEGIN
  if not exists(select 学号
                from xs
                where 学号=(select 学号 from inserted.学号)
                )
  rollback transaction
  print '插入的记录不符合学生表中的记录！'
END

/*【例】创建触发器tr_xs1_delete，
当删除xs1表中的记录时，自动删除cj1表中对应学号的记录。
*/
select *
into xs1
from xs

select *
into cj1
from cj

select * from xs1

CREATE TRIGGER tr_xs1_delete
ON xs1
after delete
AS
BEGIN
  DECLARE @xh char(10)
  Select @xh=deleted.学号 from deleted
  delete from cj1 where cj1.学号=@xh
END

delete from xs1
where 学号='2008030101'

if exists(select * from sysobjects
          where name='tr_xs1_delete' and type='tr')
   drop trigger tr_xs1_delete
go
CREATE TRIGGER tr_xs1_delete
ON xs1
after delete
AS
BEGIN
    delete from cj1
    where cj1.学号=(select 学号 from deleted.学号)
END

/*【练习】建立一个与xs表结构一样的空表xs2，
当删除表xs1中的记录时，自动将删除掉的记录存放到xs2表中。
*/
select * into xs2 from xs where 6>8

create trigger tr_del
on xs1
after delete
as
begin
  insert into xs2
  select * from deleted
end

delete from xs1
where 学号='2008030102'

select * from xs2