触发器常用案例
http://blog.csdn.net/jinjazz/archive/2008/12/01/3420299.aspx在表2的delete触发器器中判断表2是否存在delete表中word的记录就可以了
http://blog.csdn.net/jinjazz/archive/2008/12/01/3420299.aspx在表2的delete触发器器中判断表2是否存在delete表中word的记录就可以了
go
create table T1(Words varchar(10))
insert T1 select 'A'
insert T1 select 'B'
if object_id('T2')is not null drop table T2
go
create table T2(ID int identity,Words varchar(10))
insert T2 select 'A'
insert T2 select 'A'
insert T2 select 'A'
insert T2 select 'B'
insert T2 select 'B'
if object_id('tri_test')is not null drop trigger tri_test
go
create trigger tri_test on T2
for delete
as
if not exists(select 1 from T2,deleted d where T2.words=d.words)
delete T1 from deleted where T1.words=deleted.words
go
delete T2 where words='A'
/*Words
----------
B*/
delete T2 where id=5
select * from T1
/*Words
----------
B
*/
create trigger t_delete_from_table1 on table2
AFTER delete
as
begin
delete from table1 where words in
(select distinct words from deleted a left join table2 b on a.words = b.words
where b.words is null)
end
AS
if not exists(select 1 from tb2 where words in (select words from deleted))
delete from tb1 where words = (select words from deleted)
GO