rdbconcepts
asked on
Help with creation of Trigger to capture failed INSERTS
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
The above works as expected.
I'm now trying to develop a trigger on the table in question:
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.
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.TABLE1 AS A, DBO.TABLE2 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 TABLE 1 WITH THESE DETAILS - CHECK TABLE_ISSUE'
ROLLBACK
END
The above works as expected.
I'm now trying to develop a trigger on the table in question:
USE [DB]
GO
/****** Object: Trigger [dbo].[TRIG_WIDGET_ADDITIONAL_COUPON_ISSUE] Script Date: 08/16/2018 09:41:50 ******/
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.
ASKER
Scott,
Thanks.
I actually realised I should have selecting from the inserted table inside the IF EXISTS section, so corrected it accordingly.
Am I correct in thinking that after an INSERT occurs on the table and the IF EXISTS condition is met (so it returns rows), the transaction should get inserted into the _issue table and then rolled back? This is what I'm trying to achieve but I don't think the trigger is behaving like this as when I run a test INSERT, it INSERTs successfully even if the IF EXISTS returns rows.
Thanks.
I actually realised I should have selecting from the inserted table inside the IF EXISTS section, so corrected it accordingly.
Am I correct in thinking that after an INSERT occurs on the table and the IF EXISTS condition is met (so it returns rows), the transaction should get inserted into the _issue table and then rolled back? This is what I'm trying to achieve but I don't think the trigger is behaving like this as when I run a test INSERT, it INSERTs successfully even if the IF EXISTS returns rows.
Actually the @@TRANCOUNT > 0 check could cause a failed match. I can't really figure out why such a check would be needed there. You *would* need to do a check before issuing the ROLLBACK, but not as part of checking for existence of a row. Something more like below. Although, again, it's considered poor practice to do a ROLLBACK in a trigger, if you can't be sure you can get all the callers of this proc to do it, you might have to issue a ROLLBACK in the trigger.
IF EXISTS (SELECT * --if the condition of this select returns rows
FROM inserted AS A, DBO.WIDGET_ACCOUNT_TRANSAC TION AS B
WHERE A.ACCOUNTID = B.ACCOUNTID
AND A.COUPONID = B.COUPONID
AND B.PREVBALANCE = 4 AND B.BALANCE = -2
AND A.ISSUEDLOCALDATE > '03-JUL-2018')
BEGIN
RAISERROR('UNABLE TO UPDATE WIDGET_COUPON WITH THESE COUPON DETAILS - CHECK DBO.WIDGET_COUPON_ISSUE', 16, 1)
IF XACT_STATE <> 0
ROLLBACK /*!this is not normally done inside a trigger!*/
END
IF EXISTS (SELECT * --if the condition of this select returns rows
FROM inserted AS A, DBO.WIDGET_ACCOUNT_TRANSAC
WHERE A.ACCOUNTID = B.ACCOUNTID
AND A.COUPONID = B.COUPONID
AND B.PREVBALANCE = 4 AND B.BALANCE = -2
AND A.ISSUEDLOCALDATE > '03-JUL-2018')
BEGIN
RAISERROR('UNABLE TO UPDATE WIDGET_COUPON WITH THESE COUPON DETAILS - CHECK DBO.WIDGET_COUPON_ISSUE', 16, 1)
IF XACT_STATE <> 0
ROLLBACK /*!this is not normally done inside a trigger!*/
END
ASKER
I see
I've tried what you have suggested, however, no mater what I do, its still causing a failed match :-(
I've tried what you have suggested, however, no mater what I do, its still causing a failed match :-(
I can't see your data, so I can't tell you what's "not matching".
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Open in new window