Help with creation of Trigger to capture failed INSERTS

rdbconcepts
rdbconcepts used Ask the Experts™
on
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.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

Open in new window



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'

Open in new window


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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

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

IF (EXISTS (SELECT *
FROM inserted 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 B.PREVBALANCE = 4 AND B.BALANCE = -2
AND A.ISSUEDLOCALDATE > '03-JUL-2018')
AND @@TRANCOUNT > 0)
BEGIN 
    RAISERROR('UNABLE TO UPDATE WIDGET_COUPON WITH THESE COUPON DETAILS - CHECK DBO.WIDGET_COUPON_ISSUE', 16, 1)
	 ROLLBACK /*!this is not normally done inside a trigger!*/
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'

Open in new window

Author

Commented:
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.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
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_TRANSACTION 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

Author

Commented:
I see

I've tried what you have suggested, however, no mater what I do, its still causing a failed match :-(
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
I can't see your data, so I can't tell you what's "not matching".

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial