TABLE1
id int
a1 int
a2 int
a3 inttable2
table1id int
b2 int
b3 datetimeupdate table1 set a1=fn_aa(id,@type,1),a2=fn_aa(id,@type,2)
我打算在函数中
insert table2(@id,@type,@date) 但是报错:错误的使用INSERT
函数好像只能insert函数中的临时表变量
请大家给个合理的方案我现在打算用触发器在做
在 table1 a1字段,a2字段,a3字段 update 时
判断修改的值如果为1
insert into table2 (表1的ID,类型(修改a1字段类型为1,修改a2字段类型为2,修改a3字段类型为3),getdate())这个触发器该怎么写
id int
a1 int
a2 int
a3 inttable2
table1id int
b2 int
b3 datetimeupdate table1 set a1=fn_aa(id,@type,1),a2=fn_aa(id,@type,2)
我打算在函数中
insert table2(@id,@type,@date) 但是报错:错误的使用INSERT
函数好像只能insert函数中的临时表变量
请大家给个合理的方案我现在打算用触发器在做
在 table1 a1字段,a2字段,a3字段 update 时
判断修改的值如果为1
insert into table2 (表1的ID,类型(修改a1字段类型为1,修改a2字段类型为2,修改a3字段类型为3),getdate())这个触发器该怎么写
CREATE TRIGGER T_TEST
ON TABLE1
FOR INSERT UPDATE
BEGIN
CASE INSERTED WHEN 1
THEN
insert into table2 (表1的ID,类型(修改a1字段类型为1,修改a2字段类型为2,修改a3字段类型为3),getdate())
ELSE
END
END
after update
as
begin
declare @i int
if (select a1 from inserted)<>(select a1 from deleted )
set @i=1
else if (select a2 from inserted)<>(select a2 from deleted )
set @i=2
else if (select a3 from inserted)<>(select a3 from deleted )
set @i=3
insert into table2
select i.id,@i,getdate()
from inserted i
end
ON TABLE1
FOR UPDATEinsert into table2 (table1id,b2,b3)
select i.id,1,getdate()
from inserted i,deleted d
where i.id=d.id
and i.a1=1
and d.a1<>1
union all
select i.id,2,getdate()
from inserted i,deleted d
where i.id=d.id
and i.a2=1
and d.a2<>1
union all
select i.id,3,getdate()
from inserted i,deleted d
where i.id=d.id
and i.a3=1
and d.a3<>1go
after update
as
begin
insert table2
select
i.id,
case when (select a1 from inserted where id=i.id)<>(select a1 from deleted where i.id=id ) then 1
when (select a2 from inserted where id=i.id)<>(select a2 from deleted where id=i.id)then 2
when (select a3 from inserted where id=i.id)<>(select a3 from deleted where id=i.id)then 3 end,
getdate()
from inserted iend
我要根据一定条件修改TABLE1中的记录
a1,a2,a3 已经有值(0或1)
我现在在触发器中要判断,如果三个字段中任何一个字段被修改为1了(原来为1也要执行修改为1的操作),我要在table2记录下修改操作,插入TABLE1中被修改的行ID,修改时间,被修改的是哪一列(列我用类型表示,A1为类型1,A2为类型2,A3为类型3)我看你们都用到了inserted i 那SQLSERVER修改数据实际是先删除一行,然后再插入一行记录吗
如果是这样Haiwer的方法改为
CREATE TRIGGER tr_table1_update
ON TABLE1
FOR UPDATEinsert into table2 (table1id,b2,b3)
select i.id,1,getdate()
from inserted i
where i.a1=1
union all
select i.id,2,getdate()
from inserted i
where i.a2=1
union all
select i.id,3,getdate()
from inserted i
where i.a3=1go
就可以实现?
请问union all 后面的语句不加 insert into table2 (table1id,b2,b3) 也能把值写进去吗?