使用触发器操作表1(添加,更新,删除) 同步实现表2的操作 收藏if object_id('TABLE_1') is not null drop table TABLE_1 CREATE TABLE TABLE_1 ( ID INT primary key, Name1 nchar(10), Name2 nchar(10) ) if object_id('TABLE_2') is not null drop table TABLE_2 CREATE TABLE TABLE_2 ( ID INT primary key, Name1 nchar(10), Name2 nchar(10) )INSERT INTO Table_1 VALUES(1,'adsd','Chi') INSERT INTO Table_2 VALUES(1,'Lei','Chi') INSERT INTO Table_2 VALUES(2,'Alex','Chi')create trigger tr_info on TABLE_2 for insert,update,delete as begin if exists(select 1 from inserted) and not exists(select 1 from deleted)--insert触发器 begin insert into Table_1 select * from inserted end else if exists(select 1 from inserted) and exists(select 1 from deleted)--update触发器 begin update b1 set b1.Name1=U.Name1,b1.Name2=U.Name2 from Table_1 b1,deleted U where b1.ID=U.ID end else begin delete Table_1 where ID=(select ID from deleted) end end --插入测试INSERT INTO Table_2 VALUES(3,'huguo','Chi') select * from TABLE_1ID Name1 Name2 ----------- ---------- ---------- 1 adsd Chi 2 Alex Chi 3 huguo Chi (3 行受影响)--删除测试 delete from Table_2 where ID=3ID Name1 Name2 ----------- ---------- ---------- 1 adsd Chi 2 Alex Chi (2 行受影响) --更新测试select * from Table_2update Table_2 set Name1='ALex2' where ID=2ID Name1 Name2 ----------- ---------- ---------- 1 adsd Chi 2 ALex2 Chi (2 行受影响)
create trigger tri_delete on student for delete as declare @student_id varchar(10) select @student_id=student_id from deleted if @student_id='admin' begin insert ... end
CREATE TABLE TABLE_1
(
ID INT primary key,
Name1 nchar(10),
Name2 nchar(10)
)
if object_id('TABLE_2') is not null drop table TABLE_2
CREATE TABLE TABLE_2
(
ID INT primary key,
Name1 nchar(10),
Name2 nchar(10)
)INSERT INTO Table_1 VALUES(1,'adsd','Chi')
INSERT INTO Table_2 VALUES(1,'Lei','Chi')
INSERT INTO Table_2 VALUES(2,'Alex','Chi')create trigger tr_info on TABLE_2
for insert,update,delete
as
begin
if exists(select 1 from inserted) and not exists(select 1 from deleted)--insert触发器
begin
insert into Table_1 select * from inserted
end
else if exists(select 1 from inserted) and exists(select 1 from deleted)--update触发器 begin
update b1
set b1.Name1=U.Name1,b1.Name2=U.Name2
from Table_1 b1,deleted U
where b1.ID=U.ID
end
else
begin
delete Table_1 where ID=(select ID from deleted)
end
end
--插入测试INSERT INTO Table_2 VALUES(3,'huguo','Chi')
select * from TABLE_1ID Name1 Name2
----------- ---------- ----------
1 adsd Chi
2 Alex Chi
3 huguo Chi (3 行受影响)--删除测试
delete from Table_2 where ID=3ID Name1 Name2
----------- ---------- ----------
1 adsd Chi
2 Alex Chi (2 行受影响)
--更新测试select * from Table_2update Table_2 set Name1='ALex2' where ID=2ID Name1 Name2
----------- ---------- ----------
1 adsd Chi
2 ALex2 Chi (2 行受影响)
create trigger tri_delete
on student
for delete
as
declare @student_id varchar(10)
select @student_id=student_id from deleted
if @student_id='admin'
begin
insert ...
end
1 biao
2 xiao
表B有相同的字段且为空,准备插入A的数据
当删除A表的一条记录时,怎样写才能把删除的记录插入B呢?
AS
INSERT B SELECT * FROM DELETED表结构要一样