create trigger tr_A_D on A for delete as update a set pn=pn+b.p4 from table2 a,deleted b where a.p2=b.p2
create trigger trg_delete on 表1 for delete as update a set PN=PN+b.P4 from 表2 a, deleted b where a.P2 = b.P2 and b.P1 = 21 go
create trigger tr_A_D on 表1 for delete as update a set pn=pn+b.p4 from 表2 a,deleted b where a.p2=b.p2
create trigger tr on 表1 for delete as set xact_abort on --开始事务 begin tranupdate 表2 set PN=isnull(A.PN,0)+D.p4 from 表2 A join ( select p2, [p4]=sum(p4) from deleted group by p2 )D on A.p2=D.p2--提交事务 commit tran go
create trigger dbo.tri_addPN on 表1 for delete as declare @id char(10) select @id=p2 from deleted update 表2 set PN=PN+b.P4 where p2=@id
TO:zhangyang555(张阳) 如果像你这样,我就只能更新最后一条记录,而不是多条记录!!
create table [表1] ( p1 int,p2 int,p3 int,p4 int ) create table [表2] ( p2 int,pp int,po int,pn int ) go insert [表1] select 21,2,52,62 union select 21,52,20,20 union select 21,2,14,85 insert [表2] select 52,25,25,10 union select 2,20,10,8 union select 2,20,8,5 go--创建触发器 create trigger tr on [表1] for delete as set xact_abort on --开始事务 begin tranupdate [表2] set PN=isnull(A.PN,0)+D.p4 from [表2] A join ( select p2, [p4]=sum(p4) from deleted group by p2 )D on A.p2=D.p2--提交事务 commit tran go--测试 delete from [表1] where p2=52--查看 select * from [表2]--删除测试环境 drop table [表1],[表2]--结果 /* p2 pp po pn ----------- ----------- ----------- ----------- 2 20 8 5 2 20 10 8 52 25 25 30(3 row(s) affected) */
create table aa(p1 int,p2 int,p3 int,p4 int) create table bb(p2 int,pp int,po int,pn int) insert into aa values(21,2,52,62) insert into aa values(21,52,20,20) insert into aa values(21,02,14,85) insert into bb values(52,25,25,10) insert into bb values(2,20,10,08) insert into bb values(02,20,8,5) CREATE TRIGGER aa1 ON [dbo].[aa] FOR DELETE AS update bb set pn=pn+aa.p4 from bb,aa where bb.p2=aa.p2 go
for delete
as
update a
set pn=pn+b.p4
from table2 a,deleted b
where a.p2=b.p2
on 表1
for delete
as
update
a
set
PN=PN+b.P4
from
表2 a,
deleted b
where
a.P2 = b.P2 and b.P1 = 21
go
create trigger tr_A_D on 表1
for delete
as
update a
set pn=pn+b.p4
from 表2 a,deleted b
where a.p2=b.p2
on 表1
for delete
as
set xact_abort on
--开始事务
begin tranupdate 表2
set PN=isnull(A.PN,0)+D.p4
from 表2 A
join (
select p2,
[p4]=sum(p4)
from deleted
group by p2
)D on A.p2=D.p2--提交事务
commit tran
go
子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。
语句已终止。
on 表1 for delete
as
declare @id char(10)
select @id=p2 from deleted
update 表2 set PN=PN+b.P4 where p2=@id
如果像你这样,我就只能更新最后一条记录,而不是多条记录!!
(
p1 int,p2 int,p3 int,p4 int
)
create table [表2]
(
p2 int,pp int,po int,pn int
)
go
insert [表1]
select 21,2,52,62 union
select 21,52,20,20 union
select 21,2,14,85
insert [表2]
select 52,25,25,10 union
select 2,20,10,8 union
select 2,20,8,5
go--创建触发器
create trigger tr
on [表1]
for delete
as
set xact_abort on
--开始事务
begin tranupdate [表2]
set PN=isnull(A.PN,0)+D.p4
from [表2] A
join (
select p2,
[p4]=sum(p4)
from deleted
group by p2
)D on A.p2=D.p2--提交事务
commit tran
go--测试
delete from [表1] where p2=52--查看
select * from [表2]--删除测试环境
drop table [表1],[表2]--结果
/*
p2 pp po pn
----------- ----------- ----------- -----------
2 20 8 5
2 20 10 8
52 25 25 30(3 row(s) affected)
*/
create table bb(p2 int,pp int,po int,pn int)
insert into aa values(21,2,52,62)
insert into aa values(21,52,20,20)
insert into aa values(21,02,14,85)
insert into bb values(52,25,25,10)
insert into bb values(2,20,10,08)
insert into bb values(02,20,8,5)
CREATE TRIGGER aa1 ON [dbo].[aa]
FOR DELETE
AS
update bb set pn=pn+aa.p4 from bb,aa where bb.p2=aa.p2
go
结果是:(1 row(s) affected)为什么?SQL有问题吧?还是?