偶看新闻王小贱加盟:触发器
来源：百度文库 编辑：偶看新闻 时间：2024/04/30 00:10:27
说明 数据库中有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
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