2008-12-01 21:38
说明 数据库中有data1、data2两个数据表用于测试触发器是否成功!
data1和data2的一模一样id(自动增加)、name (nvarchar(50))、age(int)
data1中增加数据时,如果data2中存在新增的这个name就更新data2
create trigger t1_forinsert on data1
for insert
as
declare @name nvarchar(50)
declare @age int
select @name = name,@age = age from inserted
if exists(select * from data2 where name = @name)
begin
update data2 set age = @age where name = @name
end
当删除data1中的数据时,如果data2中存在这个name,那么把data2中的数据也删除
create trigger t2_deleted on data1
for delete
as
declare @name nvarchar(50)
select @name = name from deleted
if exists(select * from data2 where name = @name)
begin
delete from data2 where name = @name
end
当更新data1的数据时,如果这个name也存在于data2中,那么data2中的数据也相应更新
create trigger t3_forupdate on data1
for update
as
declare @name nvarchar(50)
declare @age int
select @name = name,@age = age from updated
if exists (select * from data2 where name = @name)
begin
update data2 set age = @age where name = @name
end