34,575
社区成员
发帖
与我相关
我的任务
分享
update bjmshelfmj set bjmshelfmj_test1=0
where mjbj_id=23 and moju_id=15
update bjmshelfmj set bjmshelfmj_test1=0 where moju_id in
(1,15)
and mjbj_id=23
create trigger Tri_UpdateMjbjMf
on bjmshelfmj
after update
as
if(select count(*) from bjmshelfmj,inserted as i where
bjmshelfmj.mjbj_id=i.mjbj_id and
bjmshelfmj.mshelf_id=i.mshelf_id and
bjmshelfmj.bjmshelfmj_test1=1 )=0
begin
update bjmshelf set bjmsshelf_test1=0 from inserted
where bjmshelf.mjbj_id=inserted.mjbj_id and bjmshelf.mshelf_id=inserted.mshelf_id
end
--触发器中的if语句:
if(select count(*) from bjmshelfmj,inserted as i where
bjmshelfmj.mjbj_id=i.mjbj_id and
bjmshelfmj.mshelf_id=i.mshelf_id and
bjmshelfmj.bjmshelfmj_test1=1 )=0
update bjmshelfmj set bjmshelfmj_test1=0 where moju_id=
1 and mjbj_id=23
--这条语句不符合if要求,不会执行begin里面的东西
update bjmshelfmj set bjmshelfmj_test1=0 where moju_id=
15 and mjbj_id=23
--这条语句,执行后会触发触发器, 满足if要求会执行begin中的代码
--但是我两个整合在一起写
update bjmshelfmj set bjmshelfmj_test1=0 where moju_id in
(15,1) and mjbj_id=23
--这个时候,这条sql语句,却不能达到分开写的效果
create table tb1
(
id int,
[name] varchar(10)
)
insert tb1
select 1, 'a' union all
select 2, 'b'
create table bak
(
id int,
[name] varchar(10)
)
--SQL:
update tb1
set [name] = 'c'
output
inserted.id,
inserted.name
into bak
where id in (1, 2)
select * from tb1
select * from bak
--trigger:
create trigger Tri_UpdateMjbjMf
on bjmshelfmj
after update
as
if(select count(*) from bjmshelfmj,inserted as i where
bjmshelfmj.mjbj_id=i.mjbj_id and
bjmshelfmj.mshelf_id=i.mshelf_id and
bjmshelfmj.bjmshelfmj_test1=1 )=0
begin
print '0'
update bjmshelf set bjmsshelf_test1=0 from inserted
where bjmshelf.mjbj_id=inserted.mjbj_id and bjmshelf.mshelf_id=inserted.mshelf_id
end
else
print '1'