--TRY
CREATE TRIGGER Update_table1
ON table1
FOR update
AS
if Update(status)
insert table2 select id,status from deleted d where d.status=1
CREATE TRIGGER Update_table1
ON table1
FOR update
AS
if Update(status)
insert table2 select id,status from deleted d where d.status=1
CREATE TRIGGER Update_table1
ON table1
FOR update
AS
if Update(status)
insert table2 select id,status from inserted i where i.status=1 and table2.id=i.id
ON table1
FOR update
AS
insert into table2 select id,[status] from inserted where [status]=1
ON table1
FOR update
AS
if Update(status)
insert table2 select aaa.id,aaa.status from inserted aaa where aaa.status=1
create table table2(id int,[status] int)
insert into table1 select 1,0
go
CREATE TRIGGER Update_table1
ON table1
FOR update
AS
insert into table2 select id,[status] from inserted where [status]=1
go
update table1 set [status]=1 where id=1
select * from table2
update table1 set [status]=0 where id=1
select * from table2 --不变,还是原来的记录
go
drop table table1,table2
/*
id status
----------- -----------
1 1id status
----------- -----------
1 1*/
CREATE TRIGGER Updatetable1
ON table1
FOR update
AS
if Update(status)
insert table2 select aa.id,aa.status from inserted aa where aa.status=1
ON table1
FOR update
AS
if Update(status)
begin insert into table2
select
id,
[status]
from inserted
where [status]=1
end
如果向table2插入id时,table2中已经有一样的id,则改变table2中status值为table1中的值,怎么做,谢谢.
create table table1(id int identity(1,1),status nvarchar(128))
go
create table table2(id int,status nvarchar(128))
go
insert into table1(status) select 'a' union all select 'b';if object_id(N'tr_table2','TR') is not null
drop trigger tr_table2
go
create trigger tr_table2 on table2
instead of insert
as
declare @rowcount int,@px int ,@id int
if object_id(N'tempdb..#t','U') is not null
drop table #t;
select px=identity(int,1,1),* into #t from inserted
select @rowcount=@@rowcount
create unique clustered index index_#t on #t(px);
if @rowcount =0 return;
if @rowcount=1
begin
if exists(select * from #t a where not exists(select * from table1 b where a.id=b.id))
begin
insert into table2 select id,status from #t
end
else
begin
insert into table2 select a.id,b.status from #t a inner join table1 b on a.id=b.id
end
end
if @rowcount>1
begin
select @px=px,@id=id from (select top 1 * from #t )x
while @@rowcount>0
begin
if not exists(select * from table1 where id=@id)
begin
insert into table2 select id,status from #t where px=@px
end
else
begin
insert into table2 select a.id,b.status from #t a inner join table1 b on a.id=b.id and a.px=@px
end
select @px=px,@id=id from (select top 1 * from #t where px>@px)x
end
end
goselect * from table1--测试
insert into table2 select 1,'b' union all select 2,'a'
select * from table1select * from table21 a
2 b1 a
2 b--其他测试自己测试可以多条单条换着测试
我的前提是table1中status变为1,向table2中插入值id,status,如果
在向table2插入id时,table2中已经有一样的id,则改变table2中status值为table1中的值,不要再插入了,
怎么做,谢谢.
ON table1
FOR update
AS
if Update(status)
insert table2 select id,status from inserted i where i.status=1
CREATE TRIGGER Update_table1
ON table1
FOR update
AS
if Update(status)
insert table2 select id,status from inserted i
where i.status=1 and ID not in (select ID from table2)
insert table2 select id,status from inserted i
where i.status=1 and ID not in (select ID from table2) update table2 .......from insertied i
where i.id in(select ID from table2)
这个咋写????
ON table1
FOR update
AS
if Update(status)
begin
update table2 --先更新
set [status]=i.status
from insertied i
where table2.id=i.id insert table2 --再插入
select id,status
from inserted i
where i.status=1 and ID not in (select ID from table2)
end
go