create trigger trigger_name
on table3
for insert
as
set nocount on
insert
table4
select
a.st,isnull(a.msg,b.msg),b.values
from
(select a.st, b.* from table1 a join inserted b on a.st=b.msg) a
full join
(select a.values, b.* from table2 a join inserted b on a.key=b.key) b
on a.key=b.key
set nocount off
on table3
for insert
as
set nocount on
insert
table4
select
a.st,isnull(a.msg,b.msg),b.values
from
(select a.st, b.* from table1 a join inserted b on a.st=b.msg) a
full join
(select a.values, b.* from table2 a join inserted b on a.key=b.key) b
on a.key=b.key
set nocount off
字段: st key values msg key st msg values
其中 table1,table2 是基础表;
现在想实现的功能是:
当table3中有数据写入的时候匹配table3.[msg]中是否包括 table1.[st]中的值,如果存在则写入table4。 table3.[msg],table1.[st]都是nvarchar,其中table3.[msg]录入的单条纪录,可能会包括table1.[st]中的一条
同时注意下面:当table3中有数据写入的时候匹配table2.[key]中是否存在 table3.[key]中的值,如果有则将table3.[key]对应 table3.[value] 写入 table4。
求教大虾?
create trigger triggername
on table3
for insert
as
begin
insert table4 select a.st,b.msg,c.balues
from inserted i
right join table1 a on charindex(','+a.st+',',','+i.msg+',') > 0
right join table2 b on b.key = i.key
end
go