触发器。 IF OBJECT_ID ('你的数据库名.reminder1', 'TR') IS NOT NULL DROP TRIGGER 你的数据库名.reminder1 GO CREATE TRIGGER reminder1 ON 你的数据库名.化验室纱组 AFTER INSERT,update,delete update 化验室布组 。。(写具体字段) insert into 化验室布组。。(写具体字段) delete from 化验室布组。。(写具体字段)GO
CREATE TABLE hns纱(a int PRIMARY KEY ,b varchar(20)) CREATE TABLE hns布(a int REFERENCES hns纱(a) ON DELETE CASCADE ON UPDATE CASCADE , b varchar(20)) 上面的用于同时更新和删除,同时增加要另写触发器; 这样作有什么意义?,还不如操作纱表直接复制到布表
参考create table a(id int ,name varchar(10)) create table b(id int ,name varchar(10)) go create trigger trg_a2b on a for insert,update,delete as begin delete from b where id in(select id from deleted) insert into b select * from inserted end goinsert into a select 1,'a' select * from b update a set name='b' where id=1 select * from b delete from a select * from b go drop table a drop table b
CREATE TRIGGER my_insert ON 化验室纱组 FOR INSERT as insert into 化验室布组 select * from inserted goCREATE TRIGGER my_insert ON 化验室纱组 FOR update as update 化验室布组 set col1 = n.col1 , col2 = n.col2 ... from 化验室布组 m, updated n and m.id = n.id goCREATE TRIGGER my_insert ON 化验室纱组 FOR delete as delete 化验室布组 from 化验室纱组 t where id in (select id from deleted) go
if object_id('ta')is not null drop table ta go create table ta(ID int identity,[Name] varchar(10)) if object_id('tb')is not null drop table tb go create table tb(ID int ,[Name] varchar(10)) if object_id('tri_test')is not null drop trigger tri_test go create trigger tri_test on ta for insert ,update ,delete as if not exists(select 1 from inserted ) --del delete tb from deleted d where tb.id=d.id else if not exists(select 1 from deleted) --insert insert tb select * from inserted else update tb set [name]=i.[name] from inserted i where tb.id=i.id --update go insert ta select 'A' insert ta select 'B' select * from tb update ta set [name]='G' where id=2 select * from tb delete ta where id=1 select * from tb /*ID Name ----------- ---------- 1 A 2 BID Name ----------- ---------- 1 A 2 GID Name ----------- ---------- 2 G */
create table a(id int ,name varchar(10)) create table b(id int ,name varchar(10)) go create trigger trg_a2b on a for insert,update,delete as begin delete from b where id in(select id from deleted) insert into b select * from inserted end go
create trigger tri_T on 化验室布组 for insert ,update ,delete as begin delete b from 货验室布级 b join deleted a on b.字段=a.字段 insert into b from 货验室布级 b join inserted a on b.字段=a.字段 end
--try create trigger tri_test on 化验室纱组 for insert ,update ,delete as if not exists(select 1 from inserted ) --del delete 化验室布组 from deleted d where 化验室布组.本厂编号=d.本厂编号 else if not exists(select 1 from deleted) --insert insert 化验室布组(本厂编号,客户,色号) select 本厂编号,客户,色号 from inserted
create table 化验室纱组(本厂编号 int,客户 int,色号 int,纱支 int) create table 化验室布组(本厂编号 int,客户 int,色号 int,布类 int) go create trigger my_trig on 化验室纱组 for insert ,update ,delete as if not exists(select 1 from inserted) delete 化验室布组 from deleted t where 化验室布组.本厂编号 = t.本厂编号 else if not exists(select 1 from deleted) insert into 化验室布组(本厂编号 ,客户 ,色号) select 本厂编号 ,客户 ,色号 from inserted else update 化验室布组 set 客户 = t.客户 , 色号 = t.色号 from inserted t where 化验室布组.本厂编号 = t.本厂编号 go--1、insert 对化验室纱组插入数据,然后查看化验室布组表的数据 insert into 化验室纱组 values(1 , 2 , 3 , 4) insert into 化验室纱组 values(5 , 6 , 7 , 8) go select * from 化验室布组 /* 本厂编号 客户 色号 布类 ----------- ----------- ----------- ----------- 1 2 3 NULL 5 6 7 NULL(所影响的行数为 2 行) */--2、update , 更改化验室纱组表中本厂编号=1的色号=6 update 化验室纱组 set 色号 = 6 where 本厂编号 = 1 go select * from 化验室布组 /* 本厂编号 客户 色号 布类 ----------- ----------- ----------- ----------- 1 2 6 NULL 5 6 7 NULL(所影响的行数为 2 行) */--3、delete 化验室纱组表中本厂编号=1的那条数据 delete from 化验室纱组 where 本厂编号 = 1 go select * from 化验室布组 /* 本厂编号 客户 色号 布类 ----------- ----------- ----------- ----------- 5 6 7 NULL(所影响的行数为 1 行) */drop table 化验室纱组 , 化验室布组
set @Acode =select APrdCode form from deleted --从逻辑表deleted中读取A中删除的APrdCode set @Aw =select form APrdCode from deleted --从逻辑表deleted中读取A中删除的记录的APrdWeight set @Bw =select form APrdCode from B where BPrdCode=@Acode --B中BPrdWeight update table B set BprdCode=@Bw - @Aw where BPrdCode=@Acode --删除后B的BprdCode
DROP TRIGGER 你的数据库名.reminder1
GO
CREATE TRIGGER reminder1
ON 你的数据库名.化验室纱组
AFTER INSERT,update,delete
update 化验室布组 。。(写具体字段)
insert into 化验室布组。。(写具体字段)
delete from 化验室布组。。(写具体字段)GO
CREATE TABLE hns布(a int REFERENCES hns纱(a) ON DELETE CASCADE ON UPDATE CASCADE , b varchar(20))
上面的用于同时更新和删除,同时增加要另写触发器;
这样作有什么意义?,还不如操作纱表直接复制到布表
create table b(id int ,name varchar(10))
go
create trigger trg_a2b
on a
for insert,update,delete
as
begin
delete from b where id in(select id from deleted)
insert into b select * from inserted
end
goinsert into a select 1,'a'
select * from b
update a set name='b' where id=1
select * from b
delete from a
select * from b
go
drop table a
drop table b
CREATE TRIGGER my_insert ON 化验室纱组 FOR INSERT
as
insert into 化验室布组 select * from inserted
goCREATE TRIGGER my_insert ON 化验室纱组 FOR update
as
update 化验室布组 set col1 = n.col1 , col2 = n.col2 ... from 化验室布组 m, updated n and m.id = n.id
goCREATE TRIGGER my_insert ON 化验室纱组 FOR delete
as
delete 化验室布组 from 化验室纱组 t where id in (select id from deleted)
go
go
create table ta(ID int identity,[Name] varchar(10))
if object_id('tb')is not null drop table tb
go
create table tb(ID int ,[Name] varchar(10))
if object_id('tri_test')is not null drop trigger tri_test
go
create trigger tri_test on ta
for insert ,update ,delete
as
if not exists(select 1 from inserted ) --del
delete tb from deleted d where tb.id=d.id
else if not exists(select 1 from deleted) --insert
insert tb select * from inserted
else
update tb set [name]=i.[name] from inserted i where tb.id=i.id --update
go
insert ta select 'A'
insert ta select 'B'
select * from tb
update ta set [name]='G' where id=2
select * from tb
delete ta where id=1
select * from tb
/*ID Name
----------- ----------
1 A
2 BID Name
----------- ----------
1 A
2 GID Name
----------- ----------
2 G
*/
create table b(id int ,name varchar(10))
go
create trigger trg_a2b
on a
for insert,update,delete
as
begin
delete from b where id in(select id from deleted)
insert into b select * from inserted
end
go
这个是可以,不过我想只需要插入某几列,例如化验室纱组的a,b,c和化验室布组的a,b,c,请问该如何修改?
create trigger tri_T on 化验室布组
for insert ,update ,delete
as
begin
delete b from 货验室布级 b join deleted a on b.字段=a.字段
insert into b from 货验室布级 b join inserted a on b.字段=a.字段
end
create trigger tri_test on 化验室纱组
for insert ,update ,delete
as
if not exists(select 1 from inserted ) --del
delete 化验室布组 from deleted d where 化验室布组.本厂编号=d.本厂编号
else if not exists(select 1 from deleted) --insert
insert 化验室布组(本厂编号,客户,色号) select 本厂编号,客户,色号 from inserted
create table 化验室布组(本厂编号 int,客户 int,色号 int,布类 int)
go
create trigger my_trig on 化验室纱组 for insert ,update ,delete
as
if not exists(select 1 from inserted)
delete 化验室布组 from deleted t where 化验室布组.本厂编号 = t.本厂编号
else if not exists(select 1 from deleted)
insert into 化验室布组(本厂编号 ,客户 ,色号) select 本厂编号 ,客户 ,色号 from inserted
else
update 化验室布组 set 客户 = t.客户 , 色号 = t.色号 from inserted t where 化验室布组.本厂编号 = t.本厂编号
go--1、insert 对化验室纱组插入数据,然后查看化验室布组表的数据
insert into 化验室纱组 values(1 , 2 , 3 , 4)
insert into 化验室纱组 values(5 , 6 , 7 , 8)
go
select * from 化验室布组
/*
本厂编号 客户 色号 布类
----------- ----------- ----------- -----------
1 2 3 NULL
5 6 7 NULL(所影响的行数为 2 行)
*/--2、update , 更改化验室纱组表中本厂编号=1的色号=6
update 化验室纱组 set 色号 = 6 where 本厂编号 = 1
go
select * from 化验室布组
/*
本厂编号 客户 色号 布类
----------- ----------- ----------- -----------
1 2 6 NULL
5 6 7 NULL(所影响的行数为 2 行)
*/--3、delete 化验室纱组表中本厂编号=1的那条数据
delete from 化验室纱组 where 本厂编号 = 1
go
select * from 化验室布组
/*
本厂编号 客户 色号 布类
----------- ----------- ----------- -----------
5 6 7 NULL(所影响的行数为 1 行)
*/drop table 化验室纱组 , 化验室布组
set @Aw =select form APrdCode from deleted --从逻辑表deleted中读取A中删除的记录的APrdWeight
set @Bw =select form APrdCode from B where BPrdCode=@Acode --B中BPrdWeight
update table B set BprdCode=@Bw - @Aw where BPrdCode=@Acode --删除后B的BprdCode