Link to home
Start Free TrialLog in
Avatar of rdbconcepts
rdbconceptsFlag for United Kingdom of Great Britain and Northern Ireland

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

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.
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

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

Avatar of rdbconcepts

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.
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
I see

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 TRIAL
Members 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.