SQL Server Trigger IF ( UPDATE (STATUS) IN?

mlmc2os5 于 2023-10-15 发布在其他

关注(0)|答案(2)|浏览(115)

I have an UPDATE trigger on the column STATUS. This column takes int. Instead of firing on every change, I want it to fire only when STATUS changes to 3, 4 or 6.

How can I achieve this? I'm guessing I have to extend this rule?

IF ( UPDATE (STATUS) )

USE [TEST]
GO
/****** Object:  Trigger [dbo].[PROJECT_StatusUpdate]    Script Date: 27-9-2023 16:47:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[PROJECT_StatusUpdate] ON [dbo].[PROJECT]
AFTER UPDATE
AS

IF ( UPDATE (STATUS) )

BEGIN
SET NOCOUNT ON;

INSERT [IT].[dbo].[ProjectStatusLog]
  (ProjectNO, OldStatus, NewStatus, DateChanged)
SELECT
  d.PROJECTNO,
  d.STATUS,
  i.STATUS,
  GETDATE()
FROM inserted i
FULL JOIN deleted d ON d.OBJECTID = i.OBJECTID
END;

sql-server

来源：https://stackoverflow.com/questions/77192477/trigger-if-update-status-in

2条答案

按热度按时间

h79rfbju1#

You don't need to use FULL JOIN , INNER JOIN is fine. Place the required checking condition in the WHERE clause

IF ( UPDATE (STATUS) )
  BEGIN
      INSERT [IT].[dbo].[ProjectStatusLog] 
             (ProjectNO, OldStatus, NewStatus, DateChanged)
      SELECT ProjectNO   = d.PROJECTNO,
             OldStatus   = d.STATUS,
             NewStatus   = i.STATUS,
             DateChanged = GETDATE()
      FROM   inserted i 
             INNER JOIN deleted d ON d.ProjectNO = i.ProjectNO
      WHERE  i.STATUS <> d.STATUS     -- when there is a change in STATUS value
      AND    i.STATUS IN (3, 4, 6);   -- and new value is 3, 4 or 6
  END;

赞(0）回复(0）举报 2023-10-15

bgibtngc2#

Thanks for the help. Got it working.

BEGIN
SET NOCOUNT ON;

      INSERT [IT].[dbo].[ProjectStatusLog] 
             (ProjectNO, OldStatus, NewStatus, DateChanged)
      SELECT ProjectNO   = d.PROJECTNO,
             OldStatus   = d.STATUS,
             NewStatus   = i.STATUS,
             DateChanged = GETDATE()
      FROM   inserted i 
             INNER JOIN deleted d ON d.OBJECTID = i.OBJECTID
      WHERE  i.STATUS <> d.STATUS     -- when there is a change in STATUS value
      AND    ((i.STATUS NOT IN (4, 6)) AND (d.STATUS IN (4, 6)));   -- and new value is not 4, 6, old value is 4, 6 (annulering, teruggestuurd)
END;

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server Trigger IF ( UPDATE (STATUS) IN?

2条答案

相关问题

热门标签

最新问答