I'm trying to create a trigger on table MainDataTable that will insert a row to table EPR conditionally.

These are the conditions.

If an insert is made to table MainDataTable and inserts are enabled in table EPRCheck then do the insert into EPR.

If an update is made to table MainDataTable and updates are enabled in table EPRCheck then do the insert into EPR.

What do I need to do to the WHERE clause in the trigger to get this to work, or maybe I need to have two separate triggers for inserts and updates?

This is the EPRCheck table

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'EPRCheck')
BEGIN
DROP TABLE EPRCheck
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE EPRCheck
([TriggerTypeID] [int] NOT NULL, 
[TriggerTypeDesc] [varchar](6) NOT NULL, 
[IsEnabled] [bit] NOT NULL,
CONSTRAINT [PK_EPRCheck_TriggerTypeID] PRIMARY KEY CLUSTERED 
(
    [TriggerTypeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM EPRCheck WHERE TriggerTypeID = 1)
BEGIN
INSERT INTO EPRCheck
VALUES (1, 'Insert', 1)
END
GO

IF NOT EXISTS (SELECT * FROM EPRCheck WHERE TriggerTypeID = 2)
BEGIN
INSERT INTO EPRCheck
VALUES (2, 'Update', 1)
END
GO

IF NOT EXISTS (SELECT * FROM [dbo].[EPRCheck] WHERE TriggerTypeID = 3)
BEGIN
INSERT INTO EPRCheck
VALUES (3, 'Delete', 1)
END
GO

and this is the trigger

IF EXISTS (SELECT 1 FROM sys.triggers WHERE [name] = 'TriggerEPRInsertUpdate')
BEGIN
DROP TRIGGER TriggerEPRInsertUpdate
END
GO

--Trigger Type
--INSERT = 1
--UPDATE = 2
--DELETE = 3

CREATE TRIGGER TriggerEPRInsertUpdate ON MainDataTable
AFTER INSERT, UPDATE
NOT FOR REPLICATION
AS
DECLARE @insertIsEnabled bit, @updateIsEnabled bit
SET @insertIsEnabled = (SELECT IsEnabled FROM EPRCheck WHERE TriggerTypeID = 1)
SET @updateIsEnabled = (SELECT IsEnabled FROM EPRCheck WHERE TriggerTypeID = 2)
IF @insertIsEnabled = 1 OR @updateIsEnabled = 1
BEGIN

DECLARE @triggerTypeID int

IF EXISTS (SELECT 0 FROM inserted)
BEGIN
   IF EXISTS (SELECT 0 FROM deleted)
   BEGIN 
      SELECT @triggerTypeID = 2
   END 
   ELSE
   BEGIN
      SELECT @triggerTypeID = 1
   END
END 
ELSE 
BEGIN
   SELECT @triggerTypeID = 3
END 

IF (@triggerTypeID = 1 AND @insertIsEnabled = 1) OR (@triggerTypeID = 2 AND @updateIsEnabled = 1)
BEGIN

DECLARE @createDateTime datetime 
SET @createDateTime = GETDATE()

SET XACT_ABORT ON;
SET NOCOUNT ON;
BEGIN TRANSACTION

INSERT INTO EPR
SELECT 
NEWID() AS DocumentID,
GETDATE() AS CreateDateTime,
NULL AS ProcessDateTime,
@triggerTypeID AS TriggerTypeID,
1 AS MessageStatus
FROM inserted i
LEFT JOIN deleted d ON i.ID = d.ID

WHERE 
(@insertIsEnabled = 1 AND NOT EXISTS (SELECT i.Status, i.KeyDate, i.OtherDate, i.KeyCode, i.OtherCode 
                            EXCEPT
                            SELECT d.Status, d.KeyDate, d.OtherDate, d.KeyCode, d.OtherCode))
OR
(@updateIsEnabled = 1 AND EXISTS (SELECT i.Status, i.KeyDate, i.OtherDate, i.KeyCode, i.OtherCode 
                            EXCEPT
                            SELECT d.Status, d.KeyDate, d.OtherDate, d.KeyCode, d.OtherCode))

COMMIT TRANSACTION
END
END

CodePudding user response：

Your issue is here:

WHERE  (@insertIsEnabled = 1 AND NOT EXISTS (SELECT i.Status, i.KeyDate, i.OtherDate, i.KeyCode, i.OtherCode 
                        EXCEPT
                        SELECT d.Status, d.KeyDate, d.OtherDate, d.KeyCode, d.OtherCode))

And specifically with the NOT EXISTS. For all inserted events your deleted records will be null, therefore unless your inserted records are all null you'll always have a record returned, therefore the not exist will return false. You can either change EXCEPT to INTERSECT or change to EXISTS - both will mean this works as required:

Example on db<>fiddle

With that being said, I'd still be inclined to slightly rewrite this dealing with your two scenarios separately, and do away with all the IF/ELSE logic at the start too:

CREATE TRIGGER TriggerEPRInsertUpdate ON dbo.MainDataTable
AFTER INSERT, UPDATE
AS
BEGIN
    INSERT INTO EPR (DocumentID, CreatedDateTime, TriggerTypeID, MessageStatus)
    SELECT  DocumentID = NEWID(), CreateDateTime = GETDATE(), TriggerTypeID = epr.TriggerTypeId, MessageStatus = 1
    FROM    inserted AS i
            INNER JOIN EPRCheck AS epr
                ON epr.TriggerTypeDesc = 'Insert' 
                AND epr.IsEnabled = 1
    WHERE   NOT EXISTS (SELECT 1 FROM Deleted AS d WHERE d.ID = i.ID)
    UNION ALL
    SELECT  DocumentID = NEWID(), CreateDateTime = GETDATE(), TriggerTypeID = epr.TriggerTypeId, MessageStatus = 1
    FROM    inserted AS i
            INNER JOIN deleted AS d
                ON i.ID = d.ID
            INNER JOIN EPRCheck AS epr
                ON epr.TriggerTypeDesc = 'Update' 
                AND epr.IsEnabled = 1
    WHERE   NOT EXISTS 
            (   SELECT i.Status, i.KeyDate, i.OtherDate, i.KeyCode, i.OtherCode
                INTERSECT
                SELECT d.Status, d.KeyDate, d.OtherDate, d.KeyCode, d.OtherCode
            );

END

Example on db<>fiddle