Help With Trigger

[SProc]

Hi there,

I would like to get your help on a problem I am going through.
We have a project that consist of sending customers a message through their cell phones alerting them that their payment is ready for collection. I was asked to write a procedure to perform this.
Here is the way I thought I would do it:

1- A procedure that inserts records related to a specific customer when his payment is ready
2- A trigger that checks the payment table for any update on a specific column

My problem is the trigger (how to write it), and most importantly, how to call the stored procedure from this trigger whenever the condition is fulfilled(paymt ready).

Your help would be greatly appreciated.

Thanks!

 

[SemperFiDownUnda]

your trigger would would check the memory table INSERTED to check the column that indicates payment has been made. If you can Assure that only 1 records will ever get update at a time
IE NOT UPDATE Payments SET paymt_ready = TRUE WHERE CustomerID = 55
and a Customer can have multiple records in the Payment table

then you could do something like this in your TRIGGER

DECLARE @CustID INT
SELECT @CustID = CustID FROM Payments WHERE paymt_ready = TRUE
EXECUTE sproc_NotifyCustomer @CustID

If your trigger could have more than 1 effected row then I suggest just populating a table with customers that have payments ready and have a scheduled process pull the info from there, notify the customer via the SP and delete it off that table.

Doing to much processing in a trigger is bad because, for example, if you have a system that updates 100 records in 1 SQL statement then your stored proceedure would have to notify all 100 users before that record is committed and then what do you do if something goes wrong during that process.

Thus a scheduled proceed is probably a better option and you can schedule it ever 10 minutes or so. I don't think a 10 minute lag would matter as the average time would be 5 minutes.