★★★简单问题，百分相送★★★ （触发器相关）

[puffgeng]

具体内容请参考此贴
http://topic.csdn.net/u/20080509/11/e42f1637-7667-434a-9476-406d8655b115.html

具体问题如下——
我使用的是SQL SERVER 2000服务器，采用了以下触发器
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 = (select id from inserted)--假设ID为关键字
end
end

但是这个触发器有一个致命的缺陷，就是每次只能处理一条insert的记录，如果同时插入多条记录，此触发器则无法处理，请问如何优化此触发器，使其可以处理多条记录同时插入这样的事件？

[arrow_gx]

改一下 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 

[jobine]

len(fld1,4) = 'abcd' ???
是left(fld1,4) = 'abcd' 吧。
另外where id = (select id from inserted)这样可以通过编译？

[中国风]

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  where len(fld1,4) = 'abcd' --加上条件
      delete from tbla where id in (select id from inserted
    end 
end 

[中国风]

CREATE TRIGGER tblA_Insert ON [tblA] FOR INSERT
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

[中国风]

--加条件就可以批量更新了

[arrow_gx]

CREATE TRIGGER tblA_Insert ON [tblA] FOR INSERT 
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 

[点点星灯]

但是这个触发器有一个致命的缺陷，就是每次只能处理一条insert的记录，如果同时插入多条记录，此触发器则无法处理，请问如何优化此触发器，使其可以处理多条记录同时插入这样的事件？


不会有这个问题的存在!

[中国风]

CREATE TRIGGER tblA_Insert ON [tblA]
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

用这样就可以实现楼主的了

[正宗老冉]

批量用游标循环处理.

[pt1314917]

--直接这样？
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 

[zzyyc]

SQL的触发器是表级触发器，DML影响一行或无数行触发动作只触发一次
INSERT INTO Barn_Branch(......)select ....... from tab
语句执行时触发器无效，插入许多行可能导致单个的触发器调用。

[-晴天]

[Quote=引用 11 楼 zzyyc 的回复:]
SQL的触发器是表级触发器，DML影响一行或无数行触发动作只触发一次
INSERT INTO Barn_Branch(......)select ....... from tab
语句执行时触发器无效，插入许多行可能导致单个的触发器调用。
[/Quote]

插入多少条,它也都在 inserted 表里.只要运用inserted表,没问题的.

[-狙击手-]

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

[ws_hgo]

[Quote=引用 6 楼 arrow_gx 的回复:]
SQL code

CREATE TRIGGER tblA_Insert ON [tblA] FOR INSERT 
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 

[/Quote]