create trigger trg_info1 on info1 for insert,update,delete as begin delete a from info2 a,deleted b where a.xx=b.xx insert into info2 select * from inserted end go
CREATE TRIGGER tri_INFO1toINFO2 ON info1 FOR insert,update,delete AS if not exists(select 1 from deleted)--insert begin insert into info2 select * from inserted; end else if not exists(select 1 from inserted)--delete begin delete info2 from deleted d where info2.id=d.id; --这里的ID是指关键字 end else --update begin ……; --最好能具体到更新某个字段,否则话只能删除原来的那条记录,再插入一条更新的记录 end
IF OBJECT_ID('info1')IS NOT NULL DROP TABLE info1 GO CREATE TABLE info1(id INT ,[NAME] Varchar(10)) IF OBJECT_ID('info2')IS NOT NULL DROP TABLE info2 GO CREATE TABLE info2(id INT ,[NAME] Varchar(10)) IF OBJECT_ID('TRI_TEST')IS NOT NULL DROP TRIGGER TRI_TEST GO CREATE TRIGGER TRI_TEST ON INFO1 FOR INSERT,UPDATE,DELETE AS TRUNCATE TABLE INFO2 INSERT INFO2 SELECT * FROM INFO1 GO INSERT INFO1 SELECT 1,'A' SELECT * FROM INFO2 UPDATE INFO1 SET ID=2 WHERE ID=1 SELECT * FROM INFO2 DELETE INFO1 WHERE ID=2 SELECT * FROM INFO2 /* (影響 1 個資料列)id NAME ----------- ---------- 1 A(影響 1 個資料列) (影響 1 個資料列)id NAME ----------- ---------- 2 A(影響 1 個資料列) (影響 1 個資料列)id NAME ----------- ---------- (影響 0 個資料列)*/
1 新增時 create trigger insert_table on info1 for insert as begin insert into info2 select * From inserted end2 修改時 create trigger update_table on info1 for update as begin delete info2 from info2, inserted where info2.id=inserted.id insert into info2 select * From inserted end3 刪除時create trigger delete_table on info1 for delete as begin delete info2 from info2,deleted where info2.id=deleted.id end
for insert,update,delete
as
begin
delete a from info2 a,deleted b where a.xx=b.xx insert into info2 select * from inserted
end
go
ON info1
FOR insert,update,delete
AS
if not exists(select 1 from deleted)--insert
begin
insert into info2 select * from inserted;
end
else if not exists(select 1 from inserted)--delete
begin
delete info2 from deleted d where info2.id=d.id; --这里的ID是指关键字
end
else --update
begin
……; --最好能具体到更新某个字段,否则话只能删除原来的那条记录,再插入一条更新的记录
end
GO
CREATE TABLE info1(id INT ,[NAME] Varchar(10))
IF OBJECT_ID('info2')IS NOT NULL DROP TABLE info2
GO
CREATE TABLE info2(id INT ,[NAME] Varchar(10))
IF OBJECT_ID('TRI_TEST')IS NOT NULL DROP TRIGGER TRI_TEST
GO
CREATE TRIGGER TRI_TEST ON INFO1
FOR INSERT,UPDATE,DELETE
AS
TRUNCATE TABLE INFO2
INSERT INFO2 SELECT * FROM INFO1
GO
INSERT INFO1 SELECT 1,'A'
SELECT * FROM INFO2
UPDATE INFO1 SET ID=2 WHERE ID=1
SELECT * FROM INFO2
DELETE INFO1 WHERE ID=2
SELECT * FROM INFO2
/*
(影響 1 個資料列)id NAME
----------- ----------
1 A(影響 1 個資料列)
(影響 1 個資料列)id NAME
----------- ----------
2 A(影響 1 個資料列)
(影響 1 個資料列)id NAME
----------- ---------- (影響 0 個資料列)*/
for insert
as
begin
insert into info2
select * From inserted
end2 修改時 create trigger update_table on info1
for update
as
begin
delete info2
from info2, inserted
where info2.id=inserted.id
insert into info2
select * From inserted
end3 刪除時create trigger delete_table on info1
for delete
as
begin
delete info2
from info2,deleted
where info2.id=deleted.id
end