表a
id a b c
1 d f d
2 x y z
表b
id aa bb cc
想得到这样一个效果
update a set b='e' where id =1 时
先把 1 d f d insert into b
id a b c
1 d f d
2 x y z
表b
id aa bb cc
想得到这样一个效果
update a set b='e' where id =1 时
先把 1 d f d insert into b
FOR UPDATE
AS
insert into b(id,aa,bb,cc)
select id,a,b,c from inserted这样插入表b的是insert之后的值 我想要在插入b之前的值
CREATE TRIGGER g ON [dbo].[a]
FOR UPDATE
AS
insert into b(id,aa,bb,cc)
select id,a,b,c from inserted这样插入表b的是insert into a之后的值 我想要在insert into a之前的值insert into b
create trigger t_insert on a
after update
as
insert into b(id, aa, bb, cc)
select id ,a,b,c
from deleted
如果我想得到这样一个结果:
update a set c='e' where id =1时只对表b的c列插入值,而其他列都是空能不能做到
after 是指触发器会在对表进行了操作(insert,update,delete)之后触发。
触发器默认是after
create trigger t_insert
on a for update
as
insert into b(id, aa, bb, cc)
select id ,a,b,c
from deleted
插入的操作是在update之后执行的,update操作分两部,一是删除旧的记录并将记录存储到delete表中,二是插入新的记录同时也存储在inserted表中
b表其他列插入空值比如:
update a set c='e' where id =1时只对表b的c列插入值,而其他列都是空能不能做到
after update
as
insert into b(id, aa, bb, cc)
select id ,a,b,c
from deleted
go不是满足比要求吗?
after=for
关键是要访问deleted,而不是inserted
b表其他列插入空值比如:
update a set c='e' where id =1时只对表b的c列插入值,而其他列都是空能不能做到
after update
as
if (COLUMNS_UPDATED() & 14)=14
insert into b(id, aa, bb, cc)
select id ,a,b,c
from deletedif (COLUMNS_UPDATED() & 14)=12
insert into b(id, bb, cc)
select id ,b,c
from deletedif (COLUMNS_UPDATED() & 14)=10
insert into b(id, aa, cc)
select id ,a,c
from deletedif (COLUMNS_UPDATED() & 14)=8
insert into b(id, cc)
select id ,c
from deletedif (COLUMNS_UPDATED() & 14)=6
insert into b(id, aa, bb)
select id ,a,b
from deletedif (COLUMNS_UPDATED() & 14)=4
insert into b(id, bb)
select id ,a,b,c
from deletedif (COLUMNS_UPDATED() & 14)=2
insert into b(id, aa, )
select id ,a
from deletedgo
------------------------------
(COLUMNS_UPDATED() & 14)=14 是什么意思?
--第2 3 4列被修改,即a,b,c三字段被修改
谢谢 pengda1i(冒牌大力 V0.3)
我再问下:
(COLUMNS_UPDATED() & 14)=14
括号里的14和括号外的14分别是什么意思?