有关INSTEAD OF触发器的递归问题

Question

代码如下：CREATE TRIGGER tr_UserData_ofi
  ON UserData
  INSTEAD OF INSERT
AS
  SET NOCOUNT ON  DECLARE @id varchar(20), @table varchar(32)
  DECLARE cu_data CURSOR FAST_FORWARD
    FOR SELECT id FROM inserted  OPEN cu_data
  FETCH NEXT FROM cu_data INTO @id  WHILE @@FETCH_STATUS = 0
  BEGIN
    IF LEN(@id) <= 9
      SET @table = 'UserData'
    ELSE
      SET @table = 'UserData_'    SELECT * INTO #TempRow FROM inserted WHERE id = @id
    EXECUTE ('INSERT ' + @table + ' SELECT * FROM #TempRow')
    DROP TABLE #TempRow    FETCH NEXT FROM cu_data INTO @id
  END  CLOSE cu_data
  DEALLOCATE cu_data
GOINSERT UserData VALUES ('1111111111', '')  -- 成功
INSERT UserData VALUES ('111111111', '')   -- 失败如果id的长度小于10，向UserData添加记录，已经关闭了触发器的递归、嵌套，结果还是提示：“INSTEAD  OF  触发器不支持直接递归。触发器执行失败。”救救我吧

Answer 1

可能是exec的问题CREATE TRIGGER tr_UserData_ofi
  ON UserData
  INSTEAD OF INSERT
AS
  SET NOCOUNT ON  DECLARE @id varchar(20), @table varchar(32)
  DECLARE cu_data CURSOR FAST_FORWARD
    FOR SELECT id FROM inserted  OPEN cu_data
  FETCH NEXT FROM cu_data INTO @id  WHILE @@FETCH_STATUS = 0
  BEGIN
    IF LEN(@id) <= 9
      insert UserData select * from inserted WHERE id = @id
    ELSE
      insert UserData_ select * from inserted WHERE id = @id
    FETCH NEXT FROM cu_data INTO @id
  END  CLOSE cu_data
  DEALLOCATE cu_data
GO

Answer 2

也可以不用游标CREATE TRIGGER tr_UserData_ofi
  ON UserData
  INSTEAD OF INSERT
AS
  SET NOCOUNT ON      insert UserData select * from inserted WHERE LEN(id) <= 9      insert UserData_ select * from inserted WHERE LEN(id) > 9
GO

Answer 3

再解释下为什么exec会影响触发器，因为sql认为exec执行的是另一个连接执行的语句

Answer 4

我需要用@table这个变量，触发起要调用一个存储过程，然后将结果返回@table，这句话我省略了，只能用exec，怎么办呢？

Answer 5

除非你的exec中不涉及插入源表（UserData），否则没有办法实现这样的要求

Answer 6

整个过程是这样的：
LEN(id) <= 9 插入'UserData'表
LEN(id) > 9  插入'UserData_' + SUBSTRING(id, 1, LEN(id) - 10)表
所以用游标判断每条记录

Answer 7

实际上，在INSTEAD OF INSERT触发器只接insert 原表，sql不认定为递归，而通过exec调用插入源表，被认定为递归，递归在INSTEAD OF触发器不允许

Answer 8

我晕，问题解决了，谢谢Haiwer(海阔天空)

Answer 9

嗯，明白了，再次加深了我对触发器的认识，再次感谢Haiwer(海阔天空) :-)

Answer 10

这样：CREATE TRIGGER tr_UserData_ofi
  ON UserData
  INSTEAD OF INSERT
AS
  SET NOCOUNT ON  DECLARE @id varchar(20), @table varchar(32)
  DECLARE cu_data CURSOR FAST_FORWARD
    FOR SELECT id FROM inserted  OPEN cu_data
  FETCH NEXT FROM cu_data INTO @id  WHILE @@FETCH_STATUS = 0
  BEGIN
    IF LEN(@id) <= 9
      insert UserData select * from inserted WHERE id = @id
    ELSE
    begin
      SET @table = 'UserData_'+SUBSTRING(@id, 1, LEN(@id) - 10)      SELECT * INTO #TempRow FROM inserted WHERE id = @id
      EXECUTE ('INSERT ' + @table + ' SELECT * FROM #TempRow')
      DROP TABLE #TempRow
    end
    FETCH NEXT FROM cu_data INTO @id
  END  CLOSE cu_data
  DEALLOCATE cu_data
GO

Answer 11

如果 LEN(id) > 9的时候的
SUBSTRING(id, 1, LEN(id) - 10)可以列举（因为你事先有表应该可以列举的）
可以这样实现，效率高些，否则用临时表很不爽CREATE TRIGGER tr_UserData_ofi
  ON UserData
  INSTEAD OF INSERT
AS
  SET NOCOUNT ON      insert UserData select * from inserted WHERE LEN(id) <= 9      insert UserData_aaa select * from inserted WHERE LEN(id) > 9 and SUBSTRING(id, 1, LEN(id) - 10)='aaa'      insert UserData_bbb select * from inserted WHERE LEN(id) > 9 and SUBSTRING(id, 1, LEN(id) - 10)='bbb'      insert UserData_001 select * from inserted WHERE LEN(id) > 9 and SUBSTRING(id, 1, LEN(id) - 10)='001'...GO

Answer 12

呵呵，我的UserData_????类的表是存储过程根据插入的id创建的，n年后能有数千张表:-)