CREATE TRIGGER trgr_UPDATE_TABLE1 ON B For UPDATE AS IF update ( booknumber) or update(pricew) Update B Set L.booknumber=R.booknumber ,L.pricew=R.pricew from B L inner join inserted R on L.orderid =R.orderid go
不对,改一下 CREATE TRIGGER trgr_UPDATE_TABLE1 ON B For UPDATE AS IF update ( booknumber) or update(pricew) Update B Set booknumber=R.booknumber ,pricew=R.pricew from B L inner join inserted R on L.orderid =R.orderid go
create trigger test on A for inserted as update b set booknumber=booknumber+a.booknumber price=price+a.price from inserted a where b.orderid =a.orderid gocreate trigger test on A for update as if update(booknumber) update b set booknumber=booknumber-a.booknumber+c.booknumber from deleted a,inserted c where a.orderid=b.orderid and b.orderid=c.orderidif update(price) update b set price=price-a.booknumber+c.booknumber from deleted a,inserted c where a.orderid=b.orderid and b.orderid=c.orderidgo
--测试环境 IF EXISTS(SELECT name FROM sysobjects WHERE name = N'tbl_1' AND type = 'U') DROP TABLE tbl_1 GO IF EXISTS(SELECT name FROM sysobjects WHERE name = N'tbl_2' AND type = 'U') DROP TABLE tbl_2 GOCREATE TABLE tbl_1 (orderid varchar(20), booknumber numeric(20,2), price numeric(20,2) ) GO CREATE TABLE tbl_2 (orderid varchar(20), booknumber numeric(20,2), price numeric(20,2), variedty numeric(20) ) GO insert into tbl_1 select '1002' , 3 , 45 union select '1002' , 5 , 60 union select '1001' , 7 , 70insert into tbl_2 select '1002', 8 , 105 , 2 union select '1001' , 7, 70 ,1select * from tbl_1 select * from tbl_2 -- ============================================= -- Create trigger basic template(After trigger) -- ============================================= IF EXISTS (SELECT name FROM sysobjects WHERE name = N'trig_test' AND type = 'TR') DROP TRIGGER tg_test GOCREATE TRIGGER tg_test ON tbl_1 FOR INSERT AS BEGIN if exists(select 1 from tbl_2 where orderid in(select orderid from inserted)) begin update tbl_2 set booknumber=tbl_2.booknumber+i.booknumber,tbl_2.price=tbl_2.price+i.price,variedty=variedty+1 from (select * from inserted) i where tbl_2.orderid=i.orderid end else begin insert into tbl_2 select inserted.*,1 from inserted end END GO--插入记录到tbl_1 insert into tbl_1 select '1002' , 3 , 45 select * from tbl_1 select * from tbl_2 --删除环境 drop table tbl_1 drop table tbl_2 --结果 /* 插入前的结果: orderid booknumber price -------------------- ---------------------- ---------------------- 1001 7.00 70.00 1002 3.00 45.00 1002 5.00 60.00(3 row(s) affected)orderid booknumber price variedty -------------------- ---------------------- ---------------------- ---------------------- 1001 7.00 70.00 1 1002 8.00 105.00 2(2 row(s) affected) 插入后的结果|: (1 row(s) affected) (1 row(s) affected)orderid booknumber price -------------------- ---------------------- ---------------------- 1001 7.00 70.00 1002 3.00 45.00 1002 5.00 60.00 1002 3.00 45.00(4 row(s) affected)orderid booknumber price variedty -------------------- ---------------------- ---------------------- ---------------------- 1001 7.00 70.00 1 1002 11.00 150.00 3(2 row(s) affected)*/
create trigger test on A for inserted as update b set booknumber=booknumber+sum(Select a.booknumber from inserted a where b.orderid =a.orderid) , price=price+sum(Select a.price from inserted a where b.orderid =a.orderid)
头好痛,不想了,楼主自己看看吧﹐下面是更新觸發器﹐但是更新了兩次表2﹐是錯在。 把3個人綜合起來﹐應當是對的了。 create TRIGGER tg_test1 ON tbl_1 FOR update AS BEGIN if update(booknumber) update tbl_2 set booknumber=tbl_2.booknumber+t.s from inserted i, deleted d, (select sum(i1.booknumber-d1.booknumber) s,i1.orderid odid from inserted i1 , deleted d1 where i1.orderid=d1.orderid group by i1.orderid )t where tbl_2.orderid=i.orderid and tbl_2.orderid=d.orderid and tbl_2.orderid=t.odid if update(price) update tbl_2 set price=tbl_2.price+t.s from inserted i, deleted d, (select sum(i1.price-d1.price) s,i1.orderid odid from inserted i1 , deleted d1 where i1.orderid=d1.orderid group by i1.orderid )t where tbl_2.orderid=i.orderid and tbl_2.orderid=d.orderid and tbl_2.orderid=t.odid end go
1、修改 CREATE TRIGGER trgr_UPDATE_TABLE1 ON a For UPDATE AS IF update ( booknumber) or update(pricew) Update B Set booknumber=a1.booknumber ,pricew=a1.pricew from (select orderid , sum(booknumber) as booknumber , sum(price) as price from a where orderid in (select orderid from inserted ) group by orderid ) as a1 where a1.orderid=b.orderid L.orderid =R.orderid go 2、插入 判断B表存在求和,否则插入新记录(累加A的记录条数) 要回家了,明天回复
For UPDATE
AS
IF update ( booknumber) or update(pricew)
Update B Set L.booknumber=R.booknumber
,L.pricew=R.pricew
from B L inner join inserted R on
L.orderid =R.orderid
go
CREATE TRIGGER trgr_UPDATE_TABLE1 ON B
For UPDATE
AS
IF update ( booknumber) or update(pricew)
Update B Set booknumber=R.booknumber
,pricew=R.pricew
from B L inner join inserted R on
L.orderid =R.orderid
go
for inserted
as
update b set booknumber=booknumber+a.booknumber price=price+a.price
from inserted a where b.orderid =a.orderid
gocreate trigger test on A
for update
as
if update(booknumber)
update b set booknumber=booknumber-a.booknumber+c.booknumber
from deleted a,inserted c where a.orderid=b.orderid and b.orderid=c.orderidif update(price)
update b set price=price-a.booknumber+c.booknumber
from deleted a,inserted c where a.orderid=b.orderid and b.orderid=c.orderidgo
IF EXISTS(SELECT name
FROM sysobjects
WHERE name = N'tbl_1'
AND type = 'U')
DROP TABLE tbl_1
GO
IF EXISTS(SELECT name
FROM sysobjects
WHERE name = N'tbl_2'
AND type = 'U')
DROP TABLE tbl_2
GOCREATE TABLE tbl_1
(orderid varchar(20),
booknumber numeric(20,2),
price numeric(20,2)
)
GO
CREATE TABLE tbl_2
(orderid varchar(20),
booknumber numeric(20,2),
price numeric(20,2),
variedty numeric(20)
)
GO
insert into tbl_1
select '1002' , 3 , 45
union select '1002' , 5 , 60
union select '1001' , 7 , 70insert into tbl_2
select '1002', 8 , 105 , 2
union select '1001' , 7, 70 ,1select * from tbl_1
select * from tbl_2
-- =============================================
-- Create trigger basic template(After trigger)
-- =============================================
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'trig_test'
AND type = 'TR')
DROP TRIGGER tg_test
GOCREATE TRIGGER tg_test
ON tbl_1
FOR INSERT
AS
BEGIN
if exists(select 1 from tbl_2 where orderid in(select orderid from inserted))
begin
update tbl_2
set booknumber=tbl_2.booknumber+i.booknumber,tbl_2.price=tbl_2.price+i.price,variedty=variedty+1
from (select * from inserted) i
where tbl_2.orderid=i.orderid
end
else
begin
insert into tbl_2 select inserted.*,1 from inserted
end
END
GO--插入记录到tbl_1
insert into tbl_1
select '1002' , 3 , 45
select * from tbl_1
select * from tbl_2
--删除环境
drop table tbl_1
drop table tbl_2
--结果
/*
插入前的结果:
orderid booknumber price
-------------------- ---------------------- ----------------------
1001 7.00 70.00
1002 3.00 45.00
1002 5.00 60.00(3 row(s) affected)orderid booknumber price variedty
-------------------- ---------------------- ---------------------- ----------------------
1001 7.00 70.00 1
1002 8.00 105.00 2(2 row(s) affected)
插入后的结果|:
(1 row(s) affected)
(1 row(s) affected)orderid booknumber price
-------------------- ---------------------- ----------------------
1001 7.00 70.00
1002 3.00 45.00
1002 5.00 60.00
1002 3.00 45.00(4 row(s) affected)orderid booknumber price variedty
-------------------- ---------------------- ---------------------- ----------------------
1001 7.00 70.00 1
1002 11.00 150.00 3(2 row(s) affected)*/
for inserted
as
update b
set booknumber=booknumber+sum(Select a.booknumber from inserted a
where b.orderid =a.orderid) ,
price=price+sum(Select a.price from inserted a
where b.orderid =a.orderid)
把3個人綜合起來﹐應當是對的了。
create TRIGGER tg_test1
ON tbl_1
FOR update
AS
BEGIN
if update(booknumber)
update tbl_2
set booknumber=tbl_2.booknumber+t.s
from inserted i,
deleted d,
(select sum(i1.booknumber-d1.booknumber) s,i1.orderid odid
from inserted i1 , deleted d1
where i1.orderid=d1.orderid
group by i1.orderid
)t
where tbl_2.orderid=i.orderid and tbl_2.orderid=d.orderid and tbl_2.orderid=t.odid
if update(price)
update tbl_2
set price=tbl_2.price+t.s
from inserted i,
deleted d,
(select sum(i1.price-d1.price) s,i1.orderid odid
from inserted i1 , deleted d1
where i1.orderid=d1.orderid
group by i1.orderid
)t
where tbl_2.orderid=i.orderid and tbl_2.orderid=d.orderid and tbl_2.orderid=t.odid
end
go
CREATE TRIGGER trgr_UPDATE_TABLE1 ON a
For UPDATE
AS
IF update ( booknumber) or update(pricew)
Update B
Set booknumber=a1.booknumber
,pricew=a1.pricew
from (select orderid ,
sum(booknumber) as booknumber ,
sum(price) as price
from a
where orderid in (select orderid from inserted )
group by orderid ) as a1
where a1.orderid=b.orderid L.orderid =R.orderid
go
2、插入
判断B表存在求和,否则插入新记录(累加A的记录条数)
要回家了,明天回复