CREATE TRIGGER trg_test ON [dbo].[test]
after UPDATE
AS
if update(col3)
begin
insert into test2
select D.col3,I.col3
from deleted D
join inserted I on D.主键=I.主键
end
after UPDATE
AS
if update(col3)
begin
insert into test2
select D.col3,I.col3
from deleted D
join inserted I on D.主键=I.主键
end
after UPDATE
AS
if update(col3)
begin
select [ID]=identity(int,1,1),col3 into #tmp1 from deleted
select [ID]=identity(int,1,1),col3 into #tmp2 from inserted
insert into test2
select D.col3,I.col3
from #tmp1 D
join #tmp2 I on D.ID=I.ID drop table #tmp1,#tmp2
end
因为触发器是批量执行得,不是update一条就执行一条的。
可以写成这样:
CREATE TRIGGER trg_test ON [dbo].[test]
after UPDATE
AS
if update(col3)
begin
insert into test2
select distinct cast(a.col3 as varchar(50)),cast(b.col3 as varchar(50))
from deleted a,inserted b --b与a表关联是9条记录。由distinct消除重复行-》3条
end