February 24, 2010 at 6:22 am
I wrote this trigger to check in the orders table whether the the row being inserted has made a successful payment and if not sends them an email, but at the moment the trigger prevents rows from being inserted all together
CREATE TRIGGER [dbo].[trg_Payment_Failure_Alert]
ON [dbo].[orders]
AFTER INSERT
AS
IF (SELECT O.status_id FROM orders O inner join inserted I ON O.order_id = I.order_id WHERE O.credit_package_id in (122,123,124)) = 1
BEGIN
DECLARE @EMAIL VARCHAR(100)
DECLARE @USER_ID INT
SET @USER_ID = (SELECT U.user_id FROM users U inner join inserted I on U.user_id = I.user_id)
SET @EMAIL = (SELECT email FROM users U inner join inserted I on U.user_id = I.user_id WHERE U.payment_fail_email < 1 or U.payment_fail_email is null)
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Default',
@recipients = @EMAIL,
@body = 'Hi
Our bank has let us know that you have been having problems buying your first bid pack, and we apologise for this inconvenience and would like to try and help you.
Please reply to this email with your contact phone number and we will call you back and help you through the purchase process. You don’t need to be in front of a computer; simply have a credit or debit card to hand and we will be able to process everything securely for you.
Thanks for signing up to flutteroo, and we look forwards to speaking to you very soon.
Kind regards
Dave Hobday
Managing Director, Flutteroo
',
@subject = 'Helping with flutteroo payment problems';
UPDATE USERS
SET payment_fail_email = 1
where users.user_id = @USER_ID
END
February 24, 2010 at 6:34 am
Are you getting an error or does it appear to be working?
February 24, 2010 at 6:53 am
Did you mean to join to the inserted table in your queries or do you actually have a table called "inseted"?
Also, you need to be careful that your set operations can only return one row in all situations or this will again error.
I'm not sure this is all that suitable as a trigger anyway. Personally, I wouldn't want to compromise the availability/speed of OLTP operations and this looks like it might be more suited to a scheduled batch job (or be done in the front end application).
February 24, 2010 at 8:11 am
Dave Ballantyne (2/24/2010)
Are you getting an error or does it appear to be working?
You need to answer Dave's question. Based on what I see in the trigger I can see no reason why it would cause an insert to be rolled back without an error message being provided to you.
I would suggest that you rethink the way the trigger is working though. It does not handle a set-based insert. It may be highly unlikely that you would have set-based insert, but if you did it would fail when you SET @EMAIL = query with a subquery returned more than 1 row error.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply