ansaurus

Question

simple way to know the updates in table

Answer 1

+1 A:

You might want to use a Sql Trigger to create a log of all the updates that occur on certain tables and fields.

SCMcDonnell 2009-09-02 20:56:26

Answer 2

+2 A:

You need to create a trigger

CREATE TRIGGER reminder2
ON Sales.Customer
AFTER INSERT, UPDATE, DELETE 
AS
   EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'AdventureWorks Administrator',
        @recipients = '[email protected]',
        @body = 'Don''t forget to print a report for the sales force.',
        @subject = 'Reminder';

However, using the sp_send_dbmail is not appropriate from within a stored procedure as it will slow down the update of a row. Meaning that everytime an update is done to the row, it will have to wait until the email is sent.

Instead, you should use another table to store the action on the row, have a batch job or a service scan the table and send the email itself.

For example:

CREATE TRIGGER SendEmailOnUpdate
    ON Author
    AFTER UPDATE
    AS
       INSERT INTO Notification(AuthorId) VALUES(updated.AuthorId);

The create a Windows Service that scan the table Notification and take one row at a time and send an email against the data it contains.

See MSDN for more information about triggers.

Pierre-Alain Vigeant 2009-09-02 20:57:15

I would not run the email from the trigger unless the email had to be realtime. A better process is to use the trigger to send records to another table. Then have a job set up every five or ten minutes which composes and sends an email. That way, data entry isn't broken if the email server goes down. That way you can also send one email that summarizes many changes if you only need something like one notification a day. Other wise managers might get miffed at receiving 1000 individual emails.

HLGEM 2009-09-02 21:26:42

-1 for sending the email inside the trigger. As HLGEM commented, the trigger should insert the required data into another table, and have a separate job send out the emails.

Crappy Coding Guy 2009-09-02 21:39:43

Answer 3

A:

You may want to use the UPDATE() function (lookup in books on-line) within the trigger to test just for changes to the field you are interested in (i.e. Author)

2009-09-02 22:06:02

Answer 4

A:

Actually, using sp_send_dbmail inside a trigger isn't so bad. Unlike the MAPI xp_sendmail days, sp_send_dbmail just queues up the email (essentially writing a record into a table). It will not cause the trigger to wait for the email to be sent. While SQL BOL says the sp "sends an email message", the result of a successful call is the message "Mail queued."

I am still not sure I would call it from a trigger, but I'd now consider it with dbmail.

data jockey 2009-09-02 23:50:17

ansaurus

tags:

views:

answers:

simple way to know the updates in table

related questions