create trigger tr_a_insert on a for insert asupdate b set b=b.b+i.a from inserted i,b where i.id=b.idgo
没有看懂 要增加什么,按照什么条件? 把条件写到a Table的触发器中就可以了啊
create trigger tr_a_insert on a for insert asupdate b set b=i.a from inserted i,b where i.id=b.idgo
不知道你说的增加是指新增加一条记录,还是原来的记录增加值(即更新),下面给一个更新的,如果是新增自己修改一下 IF EXISTS (SELECT name FROM sysobjects WHERE name = 'AtoB' AND type = 'TR') DROP TRIGGER AtoB; GO Create Trigger AtoB On a for Update As if Update(A) begin update b set B=(select A from deleted a) where id=a.id; end
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'AtoB' AND type = 'TR') DROP TRIGGER AtoB; GO Create Trigger AtoB On a for Update As if Update(A) begin update b set B=(select A from inserted a) where id=a.id; end 刚才的错了,应该从inserted表取A字段的值
create table A(ID int, a int) create table B(ID int, b int) GO create trigger tri_A on A for insert as begin update B set b=b+I.a from B join (select ID, a=sum(a) from inserted group by ID) I on B.ID=I.ID
insert B select ID, a=sum(a) from inserted where ID not in (select ID from B) group by ID end GOinsert A select 1,2 select * from A select * from B /* ID a ----------- ----------- 1 2(1 行受影响)ID b ----------- ----------- 1 2 (1 行受影响) */insert A select 1,3 select * from A select * from B /* ID a ----------- ----------- 1 2 1 3(2 行受影响)ID b ----------- ----------- 1 5(1 行受影响) */drop table A,B
If Object_id('TableA','U') Is Not null Drop Table TableA Go If Object_id('TableB','U') Is Not null Drop Table TableB Go Create Table TableA(id int Identity(1,1),x int ,Constraint PK_TableA_ID Primary Key(ID Asc)) Create Table TableB(id int Identity(1,1),x int ,Constraint PK_TableB_ID Primary Key(ID Asc)) Go Create Trigger tg_TableA On TableA For Insert,Update As If Exists(Select 1 From Inserted a Inner Join TableB b On b.id=a.id) Update a Set a.x=b.x From TableB a Inner Join Inserted b On b.id=a.id Else Insert Into TableB Select x From Inserted Go --Insert Insert Into TableA Select 2 Insert Into TableA Select 3 --Update Update TableA Set x=5 Where id=2 --Select TableB Select * From TableB/* id x ----------- ----------- 1 2 2 5*/
on a
for insert
asupdate b set
b=b.b+i.a
from inserted i,b
where i.id=b.idgo
要增加什么,按照什么条件?
把条件写到a Table的触发器中就可以了啊
on a
for insert
asupdate b set
b=i.a
from inserted i,b
where i.id=b.idgo
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'AtoB' AND type = 'TR')
DROP TRIGGER AtoB;
GO
Create Trigger AtoB
On a
for Update
As
if Update(A)
begin
update b set B=(select A from deleted a) where id=a.id;
end
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'AtoB' AND type = 'TR')
DROP TRIGGER AtoB;
GO
Create Trigger AtoB
On a
for Update
As
if Update(A)
begin
update b set B=(select A from inserted a) where id=a.id;
end
刚才的错了,应该从inserted表取A字段的值
create table B(ID int, b int)
GO
create trigger tri_A on A
for insert as
begin
update B set b=b+I.a
from B join (select ID, a=sum(a) from inserted group by ID) I on B.ID=I.ID
insert B
select ID, a=sum(a) from inserted
where ID not in (select ID from B)
group by ID
end
GOinsert A select 1,2
select * from A
select * from B
/*
ID a
----------- -----------
1 2(1 行受影响)ID b
----------- -----------
1 2
(1 行受影响)
*/insert A select 1,3
select * from A
select * from B
/*
ID a
----------- -----------
1 2
1 3(2 行受影响)ID b
----------- -----------
1 5(1 行受影响)
*/drop table A,B
Drop Table TableA
Go
If Object_id('TableB','U') Is Not null
Drop Table TableB
Go
Create Table TableA(id int Identity(1,1),x int ,Constraint PK_TableA_ID Primary Key(ID Asc))
Create Table TableB(id int Identity(1,1),x int ,Constraint PK_TableB_ID Primary Key(ID Asc))
Go
Create Trigger tg_TableA On TableA For Insert,Update
As
If Exists(Select 1 From Inserted a Inner Join TableB b On b.id=a.id)
Update a Set a.x=b.x
From TableB a
Inner Join Inserted b On b.id=a.id
Else
Insert Into TableB Select x From Inserted
Go
--Insert
Insert Into TableA Select 2
Insert Into TableA Select 3
--Update
Update TableA Set x=5 Where id=2
--Select TableB
Select * From TableB/*
id x
----------- -----------
1 2
2 5*/