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

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.
rdbconceptsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
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

rdbconceptsAuthor 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 DBACommented:
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
rdbconceptsAuthor Commented:
I see

I've tried what you have suggested, however, no mater what I do, its still causing a failed match :-(
Scott PletcherSenior DBACommented:
I can't see your data, so I can't tell you what's "not matching".
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.