改一下 delete 的条件 = 改成 in试试看如何CREATE TRIGGER tblA_Insert ON [tblA] FOR INSERT
as
begin
if exists(select 1 from inserted where len(fld1,4) = 'abcd' )
begin
insert into tblb select * from inserted --如果字段不一样,自己对应改.
delete from tbla where id in (select id from inserted)--假设ID为关键字
end
end
as
begin
if exists(select 1 from inserted where len(fld1,4) = 'abcd' )
begin
insert into tblb select * from inserted --如果字段不一样,自己对应改.
delete from tbla where id in (select id from inserted)--假设ID为关键字
end
end
是left(fld1,4) = 'abcd' 吧。
另外where id = (select id from inserted)这样可以通过编译?
as
begin
if exists(select 1 from inserted where len(fld1,4) = 'abcd' )
begin
insert into tblb select * from inserted where len(fld1,4) = 'abcd' --加上条件
delete from tbla where id in (select id from inserted
end
end
as
begin
if exists(select 1 from inserted where left(fld1,4) = 'abcd' )
begin
insert into tblb select * from inserted where left(fld1,4) = 'abcd' --加上条件
delete from tbla where id in (select id from inserted
end
end
as
begin
if exists(select 1 from inserted where left(fld1,4) = 'abcd' )
begin
insert into tblb select * from inserted --如果字段不一样,自己对应改.
delete from tbla where id in (select id from inserted)--假设ID为关键字
end
end
不会有这个问题的存在!
instead of insert
as
begin
insert tblA select * from inserted where fld1 not like 'abcd%'
insert tblB select * from inserted where fld1 like 'abcd%'
end
用这样就可以实现楼主的了
--直接这样?
CREATE TRIGGER tblA_Insert ON [tblA] FOR INSERT
as
begin
insert into tblb select * from inserted where len(fld1,4)='abcd'
delete taba where id=(select id from inserted where len(fld1,4)='abcd')
end
INSERT INTO Barn_Branch(......)select ....... from tab
语句执行时触发器无效,插入许多行可能导致单个的触发器调用。
插入多少条,它也都在 inserted 表里.只要运用inserted表,没问题的.
as
begin
insert into tblb select * from inserted where len(fld1,4)='abcd'
delete taba where id=(select id from inserted where len(fld1,4)='abcd')
end