代码
/*【例】建立一个触发器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