Need help with a trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need help with a trigger

Author

Topic

kyleb
Starting Member

7 Posts

Posted - 2012-09-12 : 18:52:17

Hello, I am looking for suggestions including syntax and thank you in advance!

Details:

1) We have program code that we cannot alter that fires an update to our PAYMENTS table by updating a column called CardAuthStatus to equal "NEEDAUTH" when certain criteria are met.

2) Using a trigger, we want to prevent/undo/overwrite that table update to keep CardAuthStatus as a NULL value when this criteria is met (DATE is also a field in the PAYMENTS table):

DATEDIFF(DAY,DATEADD(HOUR,1,CURRENT_TIMESTAMP),DATE)>0

(in other words, if the event is tomorrow I do not want it authorizing the card if it is today, only if the order is placed tomorrow)

3) As an FYI, I believe other PAYMENTS table fields may get updated at the same time that the CARDAUTHSTATUS is getting updated as part of this routine that we cannot alter.

4) Once that field value gets set to "NEEDAUTH" behind it a credit card authorization request fires to our credit card processor, which we are trying to stop by stopping or modifying this update to keep the field value at NULL (which it was before it was changed to "NEEDAUTH"). Only as an FYI, once the credit card auth comes back from the processor the "NEEDAUTH" value changes to something else based on the authorization.

5) Here's the most recent code our developer suggested, but it has syntax issues....

CREATE TRIGGER [dbo].[CardAuthStatus]
ON [dbo].[Payments]
UPDATE
AS
UPDATE inserted SET CARDAUTHSTATUS=NULL WHERE CARDAUTHSTATUS LIKE 'NEEDAUTH' AND DATEDIFF(DAY,DATEADD(HOUR,1,CURRENT_TIMESTAMP),DATE)>0
GO

Suggested syntax is greatly appreciated! Again thank you for taking the time to look at this for me.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 22:30:30

it should be an INSTEAD OF UPDATE trigger


CREATE TRIGGER [dbo].[CardAuthStatus]
ON [dbo].[Payments]
INSTEAD OF UPDATE
AS
BEGIN
UPDATE p
SET p.CardAuthStatus = 'NEEDAUTH'
FROM INSERTED i
INNER JOIN [dbo].[Payments] p
ON p.PK = i.PK
WHERE DATEDIFF(DAY,DATEADD(HOUR,1,CURRENT_TIMESTAMP),DATE)<=0
END

PK is primary key(s) of your table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kyleb
Starting Member

7 Posts

Posted - 2012-09-13 : 00:30:37

Thanks visakh16 for reply.

I assume you meant NULL in the SET statement.

SET p.CardAuthStatus = 'NEEDAUTH' (you meant NULL)

Also. If other fields were updated as part of the UPDATE statement we are trying to alter, do those need to get referenced in some way in your UPDATE statement below?

UPDATE p
SET p.CardAuthStatus = 'NEEDAUTH'
FROM INSERTED i
INNER JOIN [dbo].[Payments] p
ON p.PK = i.PK
WHERE DATEDIFF(DAY,DATEADD(HOUR,1,CURRENT_TIMESTAMP),DATE)<=0
END

Lastly, I am assuming I should reference the "i" table when referencing the date field on this line.

WHERE DATEDIFF(DAY,DATEADD(HOUR,1,CURRENT_TIMESTAMP),DATE)<=0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-13 : 10:32:42

quote:
Originally posted by kyleb

Thanks visakh16 for reply.

I assume you meant NULL in the SET statement.

SET p.CardAuthStatus = 'NEEDAUTH' (you meant NULL)

Also. If other fields were updated as part of the UPDATE statement we are trying to alter, do those need to get referenced in some way in your UPDATE statement below?

UPDATE p
SET p.CardAuthStatus = 'NEEDAUTH'
FROM INSERTED i
INNER JOIN [dbo].[Payments] p
ON p.PK = i.PK
WHERE DATEDIFF(DAY,DATEADD(HOUR,1,CURRENT_TIMESTAMP),DATE)<=0
END

Lastly, I am assuming I should reference the "i" table when referencing the date field on this line.

WHERE DATEDIFF(DAY,DATEADD(HOUR,1,CURRENT_TIMESTAMP),DATE)<=0

Nope I meant NEEDAUTH

see my used condition (its just opposite of yours)

if other fields need to be updates yes you need to refer them in update

Depends on whether you need to compared to existing or new value for Date. If former its p else its i

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kyleb
Starting Member

7 Posts

Posted - 2012-09-13 : 11:59:19

Thanks again. This is what we ended up doing and it's working perfectly now! (the after update trigger is happening before the credit card auth gets run and this saves us from having to worry about dealing with any other fields that are getting updated). If there are performance benefits from doing it before update let me know your thoughts.

ALTER TRIGGER [dbo].[CardAuthStatus]
ON [dbo].[Payments]
AFTER UPDATE
AS
UPDATE PAYMENTS
SET CARDAUTHSTATUS=DELETED.CARDAUTHSTATUS
FROM PAYMENTS INNER JOIN INSERTED ON INSERTED.ID=PAYMENTS.ID
INNER JOIN DELETED ON DELETED.ID=INSERTED.ID
WHERE INSERTED.CARDAUTHSTATUS = 'NEEDAUTH'
AND INSERTED.LOCKED IS NULL
AND DATEDIFF(DAY,DATEADD(HOUR,1,CURRENT_TIMESTAMP),PAYMENTS.DATE)>0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-13 : 12:10:36

ok...but keep in mind that this gets executed after actual update action

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kyleb
Starting Member

7 Posts

Posted - 2012-09-13 : 13:04:09

Got it. From what I can tell the update after is processing before our credit card processor gets its signal so it's working OK right now. Thanks again for your input.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-13 : 13:20:02

ok...then you should be good!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources