Help with creation of Trigger to capture failed INSERTS

  • wak_no1

    SSCrazy

    Points: 2400

    Hello.

    I want to create a trigger on a table on INSERT. So when an INSERT attempts to happen on the table, a SELECT is first run and if the SELECT returns rows, the INSERT is cancelled but the transaction ls logged to another table.

    The code that has the SELECT in, is as follows


    BEGIN TRAN
    IF (EXISTS (SELECT *
    FROM DBO.WIDGET_COUPON AS A, DBO.WIDGET_ACCOUNT_TRANSACTION AS B 
    WHERE A.ACCOUNTID = B.ACCOUNTID
    AND A.COUPONID = B.COUPONID
    AND PREVBALANCE = 4 AND BALANCE = -2
    AND ISSUEDLOCALDATE> '3-JUL-2018'))
    BEGIN
        SELECT @@TRANCOUNT,'UNABLE TO UPDATE WIDGET_COUPON WITH THESE COUPON DETAILS - CHECK DBO.WIDGET_COUPON_ISSUE'
        ROLLBACK
    END

    The above works as expected.

    I'm now trying to develop a trigger on the table in question:


    USE [DB]
    GO

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER TRIGGER [dbo].[TRIG_WIDGET_ADDITIONAL_COUPON_ISSUE] 
    ON [dbo].[WIDGET_COUPON] 
    AFTER INSERT
    AS

        DECLARE @inserted TABLE ([couponID] [bigint] NOT NULL,[organizationID] [bigint] NOT NULL, [orgLevelID] [bigint] NULL, [couponTypeID] [bigint] NULL, [accountID] [bigint] NULL,    [promotionID] [bigint] NULL,[customerID] [bigint] NULL,[hashCode] [int] NULL,[couponCode] [nvarchar](32) NULL DEFAULT ((0)),[serialNumber] [nvarchar](50) NOT NULL,[createdLocalDateTime] [datetime] NOT NULL,[createdHostDateTime] [datetime] NOT NULL,[createdLocationID] [int] NULL,[issuedLocalDateTime] [datetime] NULL,[issuedHostDateTime] [datetime] NULL,[issuedState] [tinyint] NOT NULL,[allowMultipleRedemptions] [bit] NOT NULL DEFAULT ((0)),[validUntilDate] [smalldatetime] NULL,[redemptionPrecedence] [tinyint] NOT NULL,[redemptionInstructions] [tinyint] NOT NULL DEFAULT ((1)),[issuedLocationID] [int] NULL,[reissueLocalDateTime] [datetime] NULL,[reissueHostDateTime] [datetime] NULL,[reissueLocationID] [int] NULL,[reissuedBy] [nvarchar](32) NULL,[sourceType] [int] NULL,[issuedEmplNum] [int] NULL,[issuedLocalDate] [datetime] NULL,[bulkIssueID] [int] NULL,[validFromDate] [datetime] NULL,[alternateID] [nvarchar](50)NULL, [couponSegmentID] [bigint] NULL,[etLastCouponExpSentDate] [smalldatetime] NULL)
    INSERT INTO @inserted
    SELECT *
    FROM inserted

    BEGIN TRAN
    IF (EXISTS (SELECT *
    FROM DBO.WIDGET_COUPON AS A, DBO.WIDGET_ACCOUNT_TRANSACTION AS B --if the condition of this select returns rows
    WHERE A.ACCOUNTID = B.ACCOUNTID
    AND A.COUPONID = B.COUPONID
    AND PREVBALANCE = 4 AND BALANCE = -2
    AND ISSUEDLOCALDATE> '3-JUL-2018')
    AND @@TRANCOUNT > 0)
    BEGIN
        SELECT @@TRANCOUNT,'UNABLE TO UPDATE WIDGET_COUPON WITH THESE COUPON DETAILS - CHECK DBO.WIDGET_COUPON_ISSUE'
        ROLLBACK
    END
     
    INSERT INTO [DBO].[WIDGET_COUPON_ISSUE]
    SELECT * FROM @inserted;

    GO
    EXEC sp_settriggerorder @triggername=N'[dbo].[TRIG_WIDGET_ADDITIONAL_COUPON_ISSUE]', @order=N'First', @stmttype=N'INSERT'

    Now whenever a INSERT is attempted on the table in question, no transactions are successful and thus everything gets logged to the WIDGET_COUPON_ISSUE table. It's as if the SELECT that I have defined doesn't get correctly.

    I'm sure the answer is staring me directly in the face. Could someone suggest anything that will help resolve my issue please?

    Thanks.

  • John Mitchell-245523

    SSC Guru

    Points: 148259

    What is your question?

    Actually, I have a question.  Why not just create a unique index on ACCOUNTID and COUPONID, filtered on PREVBALANCE = 4 AND BALANCE = -2 AND ISSUEDLOCALDATE> '3-JUL-2018'?

    John

  • wak_no1

    SSCrazy

    Points: 2400

    John Mitchell-245523 - Thursday, August 16, 2018 4:25 AM

    What is your question?

    Actually, I have a question.  Why not just create a unique index on ACCOUNTID and COUPONID, filtered on PREVBALANCE = 4 AND BALANCE = -2 AND ISSUEDLOCALDATE> '3-JUL-2018'?

    John

    So sorry, just updated the topic with the actual question:)

  • John Mitchell-245523

    SSC Guru

    Points: 148259

    The problem appears to be that you're not using the @inserted table.  (By the way, you don't need to insert that into a table, just use Inserted directly.)

    But, as I said before, don't use a trigger for this - it's horribly inefficient, since it involves rolling back transactions, whereas a unique index would prevent invalid inserts from being made in the first place.

    John

  • wak_no1

    SSCrazy

    Points: 2400

    John Mitchell-245523 - Thursday, August 16, 2018 4:50 AM

    The problem appears to be that you're not using the @inserted table.  (By the way, you don't need to insert that into a table, just use Inserted directly.)

    But, as I said before, don't use a trigger for this - it's horribly inefficient, since it involves rolling back transactions, whereas a unique index would prevent invalid inserts from being made in the first place.

    John

    You mean in the IF statement?

    I like the idea but the reason its in a trigger is because we want capture the the failed inserts

  • John Mitchell-245523

    SSC Guru

    Points: 148259

    wak_no1 - Thursday, August 16, 2018 4:58 AM

    You mean in the IF statement?

    Yes.  At the moment, it will evaluate to true if there are any rows in WIDGET_COUPON that match any rows in WIDGET_ACCOUNT_TRANSACTION and satisfy the balance and date requirements.

    John

  • wak_no1

    SSCrazy

    Points: 2400

    John Mitchell-245523 - Thursday, August 16, 2018 5:05 AM

    wak_no1 - Thursday, August 16, 2018 4:58 AM

    You mean in the IF statement?

    Yes.  At the moment, it will evaluate to true if there are any rows in WIDGET_COUPON that match any rows in WIDGET_ACCOUNT_TRANSACTION and satisfy the balance and date requirements.

    John

    Ah I see, I've replaced the SELECT * FROM DBO.WIDGET_COUPON AS A with the inserted table. This now appears to insert the data into the table, thanks.

    However, I'm faced with a new issue with passed INSERTs updating the table that's supposed to capture the rolled back transactions, as well as the main table.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply