在同一个表上有多个触发器的缺点

Question

我在同一个表上有多个触发器使用多个触发器的原因是因为有些字段需要从不同的表中更新，我也有backward date scheduling。有线正在发生当我第一次插入时，只有一个字段从其中一个触发器更新或一个触发器被触发，当我第一次更新行时触发三个触发器，...我必须更新3次以获取所有每次我做第一次，第二次，第三次更新时，更新的字段也需要大约8秒

问题1.在单个表中有多个触发器的缺点是什么？

问题2.我怎样才能加快触发器？

问题3.我该如何调试触发器？

后顺序调度

SHIP BY = CUSTOMER PROMISED DATE-1

A-MOUNT BY =船舶-1

A-粉体= A-MOUNT BY - 1 OR A-粉体也等于船由日期-2

A-FAB BY = A-粉体 - 1 OR A-FAB BY也等于船由日期-3

AC/S BY = A-FAB BY OR AC/S BY也等于按日期发货-4

A-CUT BY = AC/S BY -1或A-CUT BY也等于按日期发货-5

/****** Object: Trigger [dbo].[CALC-PROMISED-DATE-AND-SHIPBY] Script Date: 4/6/2017 2:46:01 PM ******/ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
ALTER TRIGGER [dbo].[CALC-PROMISED-DATE-AND-SHIPBY] 
    ON [dbo].[WORKORDERS] 
    AFTER INSERT, UPDATE 
    AS 
    BEGIN 
    set nocount on 
    IF TRIGGER_NESTLEVEL() > 1 
    RETURN 
    set datefirst 7; 
UPDATE T1 
    [SHIP BY] = 
       CASE datepart(WEEKDAY, t1.[CALC PROMISED DATE]) 
        WHEN 1 then DateAdd(day, -2, t1.[CALC PROMISED DATE]) 
        WHEN 7 then DateAdd(day, -1, t1.[CALC PROMISED DATE]) 
       ELSE 
        CASE 
         WHEN t1.[RE-COMMIT DATE] =Null THEN ISNULL(T1.[PROMISED DATE],Null) 
         WHEN t1.[RE-COMMIT DATE] is null THEN ISNULL(T1.[PROMISED DATE],Null) 
        ELSE ISNULL(T1.[RE-COMMIT DATE],Null) 
        END 
      END  

      FROM WORKORDERS T1 
       INNER JOIN inserted i ON T1.[WORK ORDER #] = i.[WORK ORDER #] 
       END 

A-MOUNT BY

/****** Object: Trigger [dbo].[MOUNTBY] Script Date: 4/6/2017 2:46:54 PM ******/ 
    SET ANSI_NULLS ON 
    GO 
    SET QUOTED_IDENTIFIER ON 
    GO 
    ALTER TRIGGER [dbo].[MOUNTBY] 
     ON [dbo].[WORKORDERS] 
     AFTER INSERT,UPDATE 
     AS 
     BEGIN 
     IF TRIGGER_NESTLEVEL() > 1 
     RETURN 
     set datefirst 7; 
    UPDATE T1 
     [A-MOUNT BY] = 
     case  datepart(WEEKDAY, DateAdd(day,-1,t1.[SHIP BY])) 
      when 7 then DateAdd(day, -2, t1.[SHIP BY]) 
      when 1 then DateAdd(day, -3, t1.[SHIP BY]) 
      else DateAdd(day, -1, t1.[SHIP BY])--t1.[A-C/S BY]-1 
     END 
FROM WORKORDERS T1 
    INNER JOIN inserted i ON T1.[WORK ORDER #] = i.[WORK ORDER #] 
    END 

A-粉体

/****** Object: Trigger [dbo].[POWDERBY] Script Date: 4/6/2017 2:49:53 PM ******/ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
ALTER TRIGGER [dbo].[POWDERBY] 
    ON [dbo].[WORKORDERS] 
    AFTER INSERT,UPDATE 
    AS 
    BEGIN 
    IF TRIGGER_NESTLEVEL() > 1 
    RETURN 
    set datefirst 7; 
UPDATE T1 
--SET 
SET [A-POWDER BY] = 
case datepart(WEEKDAY, t1.[A-MOUNT BY]-1) 
     when 7 then DateAdd(day, -2, t1.[A-MOUNT BY]) 
    when 1 then DateAdd(day, -3, t1.[A-MOUNT BY]) 
    else t1.[A-MOUNT BY]-1 
END 

FROM WORKORDERS T1 
    INNER JOIN inserted i ON T1.[WORK ORDER #] = i.[WORK ORDER #] 
    END 

A-FAB BY

/****** Object: Trigger [dbo].[FABBY] Script Date: 4/6/2017 2:50:23 PM ******/ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
ALTER TRIGGER [dbo].[FABBY] 
    ON [dbo].[WORKORDERS] 
    AFTER insert, UPDATE 
    AS 
    BEGIN 
    IF TRIGGER_NESTLEVEL() > 1 
    RETURN 
    set datefirst 7; 
UPDATE T1 
SET [A-FAB BY] = case datepart(WEEKDAY, t1.[A-POWDER BY]-1) 
     when 7 then DateAdd(day, -2, t1.[A-POWDER BY]) 
    when 1 then DateAdd(day, -3, t1.[A-POWDER BY]) 
    else t1.[A-POWDER BY]-1 
END 
FROM WORKORDERS T1 
    INNER JOIN inserted i ON T1.[WORK ORDER #] = i.[WORK ORDER #] 
    END 

A-PRINT BY

/****** Object: Trigger [dbo].[PRINTBY] Script Date: 4/6/2017 2:50:50 PM ******/ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
ALTER TRIGGER [dbo].[PRINTBY] 
    ON [dbo].[WORKORDERS] 
    AFTER INSERT, UPDATE 
    AS 
    BEGIN 
    IF TRIGGER_NESTLEVEL() > 1 
    RETURN 
    set datefirst 7; 
UPDATE T1 
SET [A-PRINT BY] = case datepart(WEEKDAY, t1.[A-FAB BY]) 
     when 7 then DateAdd(day, -2, t1.[A-FAB BY]) 
    when 1 then DateAdd(day, -3, t1.[A-FAB BY]) 
    else t1.[A-FAB BY]-1 
END 
FROM WORKORDERS T1 
    INNER JOIN inserted i ON T1.[WORK ORDER #] = i.[WORK ORDER #] 
    END 

A-C/S BY

SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
ALTER TRIGGER [dbo].[C/SBY] 
    ON [dbo].[WORKORDERS] 
    AFTER INSERT,UPDATE 
    AS 
    BEGIN 
    IF TRIGGER_NESTLEVEL() > 1 
    RETURN 
    set datefirst 7; 
UPDATE T1 
--SET 
SET [A-C/S BY] = case datepart(WEEKDAY, t1.[A-PRINT BY]-1) 
     when 7 then DateAdd(day, -2, t1.[A-PRINT BY]) 
    when 1 then DateAdd(day, -3, t1.[A-PRINT BY]) 
    else t1.[A-PRINT BY]-1 
END 
FROM WORKORDERS T1 
    INNER JOIN inserted i ON T1.[WORK ORDER #] = i.[WORK ORDER #] 
    END 

A-削减

SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
ALTER TRIGGER [dbo].[CUTBY] 
    ON [dbo].[WORKORDERS] 
    AFTER INSERT, UPDATE 
    AS 
    BEGIN 
    IF TRIGGER_NESTLEVEL() > 1 
    RETURN 
    set datefirst 7; 
UPDATE T1 
--SET 
SET [A-CUT BY] = 
case  datepart(WEEKDAY, DateAdd(day,-1,t1.[A-C/S BY])) 
    when 7 then DateAdd(day, -2, t1.[A-C/S BY]) 
    when 1 then DateAdd(day, -3, t1.[A-C/S BY]) 
    else t1.[A-C/S BY]-1--t1.[A-C/S BY]-1 
END 
FROM WORKORDERS T1 
    INNER JOIN inserted i ON T1.[WORK ORDER #] = i.[WORK ORDER #] 
    END 

Answer 1

这确实是3个问题集于一身职位。

问题1.在单个表中有多个触发器的缺点是什么？

只要每个触发器都用于一个独特事件，这里没有缺点。通常最好避免在单个表上使用三个更新触发器，因为它很难维护，并且有时消防命令很重要，因此您必须对其进行管理。

问题2.我怎样才能加快触发器？

没有万能的按钮来使触发器更快。每个触发器的查询和逻辑需要独立评估。

问题3.我该如何调试触发器？

用打印语句。触发器绝对不容易调试。

---

然而，这个问题有助于触发器的更大讨论。大多数时间触发器不是解决问题的最佳方法。通常有更好的方法。在这一点上，我一直在使用数据库超过20年，并且只有少数几次才有合理的触发需求。它们对于审计非常有用，但大多数情况下它们是手头任务的错误工具。

---

鉴于新的信息和代码，我创建了一个新的触发器，应该适合你。您不需要每列的触发器，您需要触发插入和更新事件。您可以在单个更新语句中执行所有这些编辑。这里似乎还存在一些主要的逻辑问题，但我并不真正知道你在做什么。我建议放弃所有这些触发器，因为它们命名非常差，并且给出了它们被固定在一个动作中的名称。

我也强烈建议你养成更好名字的习惯。这些列名称只是可怕的。他们不应该有空格，破折号等

所有这些触发器可以简化为这样的事情。

create TRIGGER [dbo].[WORKORDERS_Insert_Update] ON [dbo].[WORKORDERS] 
    AFTER INSERT, UPDATE AS 
BEGIN 
    set nocount on 

    IF TRIGGER_NESTLEVEL() > 1 
     RETURN 

    set datefirst 7; 
    UPDATE T1 
     set [SHIP BY] = 
      CASE datepart(WEEKDAY, t1.[CALC PROMISED DATE]) 
       WHEN 1 then DateAdd(day, -2, t1.[CALC PROMISED DATE]) 
       WHEN 7 then DateAdd(day, -1, t1.[CALC PROMISED DATE]) 
      ELSE T1.[RE-COMMIT DATE] 
        --CASE This entire case expression is pointless. Just put in the column, all the NULL checks in here are going to do the exact same thing. 
        -- --WHEN t1.[RE-COMMIT DATE] = Null THEN ISNULL(T1.[PROMISED DATE],Null) Nothing EVER equals NULL. This will never happen 
        --  --WHEN t1.[RE-COMMIT DATE] is null THEN ISNULL(T1.[PROMISED DATE],Null) ??? Why check for NULL and if it is NULL use an explicit NULL. This makes no sense 
        --ELSE ISNULL(T1.[RE-COMMIT DATE],Null)??? Why check for NULL and if it is NULL use an explicit NULL. This makes no sense 
        --END 
      END  
     , [A-MOUNT BY] = 
      case datepart(WEEKDAY, DateAdd(day,-1,t1.[SHIP BY])) 
       when 7 then DateAdd(day, -2, t1.[SHIP BY]) 
       when 1 then DateAdd(day, -3, t1.[SHIP BY]) 
       else DateAdd(day, -1, t1.[SHIP BY])--t1.[A-C/S BY]-1 
      END 
     , [A-POWDER BY] = 
      case datepart(WEEKDAY, t1.[A-MOUNT BY]-1) 
       when 7 then DateAdd(day, -2, t1.[A-MOUNT BY]) 
       when 1 then DateAdd(day, -3, t1.[A-MOUNT BY]) 
       else t1.[A-MOUNT BY]-1 
      END 
     , [A-FAB BY] = 
      case datepart(WEEKDAY, t1.[A-POWDER BY]-1) 
       when 7 then DateAdd(day, -2, t1.[A-POWDER BY]) 
       when 1 then DateAdd(day, -3, t1.[A-POWDER BY]) 
       else t1.[A-POWDER BY]-1 
      END 
     ,[A-PRINT BY] = 
      case datepart(WEEKDAY, t1.[A-FAB BY]) 
       when 7 then DateAdd(day, -2, t1.[A-FAB BY]) 
       when 1 then DateAdd(day, -3, t1.[A-FAB BY]) 
       else t1.[A-FAB BY]-1 
      END 
     , [A-C/S BY] = 
      case datepart(WEEKDAY, t1.[A-PRINT BY]-1) 
       when 7 then DateAdd(day, -2, t1.[A-PRINT BY]) 
       when 1 then DateAdd(day, -3, t1.[A-PRINT BY]) 
       else t1.[A-PRINT BY]-1 
      END 
     , [A-CUT BY] = 
      case datepart(WEEKDAY, DateAdd(day,-1,t1.[A-C/S BY])) 
       when 7 then DateAdd(day, -2, t1.[A-C/S BY]) 
       when 1 then DateAdd(day, -3, t1.[A-C/S BY]) 
       else t1.[A-C/S BY]-1--t1.[A-C/S BY]-1 
      END 
    FROM WORKORDERS T1 
    INNER JOIN inserted i ON T1.[WORK ORDER #] = i.[WORK ORDER #] 
END