August 16, 2018 at 3:56 am
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.
August 16, 2018 at 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
August 16, 2018 at 4:35 am
John Mitchell-245523 - Thursday, August 16, 2018 4:25 AMWhat 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:)
August 16, 2018 at 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
August 16, 2018 at 4:58 am
John Mitchell-245523 - Thursday, August 16, 2018 4:50 AMThe 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
August 16, 2018 at 5:05 am
wak_no1 - Thursday, August 16, 2018 4:58 AMYou 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
August 16, 2018 at 5:57 am
John Mitchell-245523 - Thursday, August 16, 2018 5:05 AMwak_no1 - Thursday, August 16, 2018 4:58 AMYou 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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply