Trigger for Table Updates

  • Hi,

    I was wondering if anyone had some advice or example for creating a trigger that fires when a specific table is updated. Every time an entry is added to this table, I would like this trigger to fire.

  • CREATE TRIGGER reminder2

    ON Sales.Customer

    AFTER INSERT, UPDATE, DELETE

    AS

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'AdventureWorks2012 Administrator',

    @recipients = 'danw@Adventure-Works.com',

    @body = 'Don''t forget to print a report for the sales force.',

    @subject = 'Reminder';

    GO

    CREATE TRIGGER (Transact-SQL)

  • Don't know what you want your trigger to do but be careful. Triggers fire once per operation, not once per row. So you need to create set based queries in your triggers. And stay away from sending emails directly from your trigger like the previous example does. You do NOT want your inserts to wait on that. If you want to send emails it is better to put the data in a holding table and have another process pick up the information for emails on a schedule.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • What I need to do is send an email every time an entry is added to a specific table. Any ideas or suggestion on how best to this are appreciated.

  • Sean Lange (3/3/2016)


    Don't know what you want your trigger to do but be careful. Triggers fire once per operation, not once per row. So you need to create set based queries in your triggers. And stay away from sending emails directly from your trigger like the previous example does. You do NOT want your inserts to wait on that. If you want to send emails it is better to put the data in a holding table and have another process pick up the information for emails on a schedule.

    sp_send_dbmail would not cause a wait, right? it's already an asynchronous service broker.

    as long as permissions to send the mail are taken care of(so the trigger doesn't fail with the error user does not have permission to sp_send_dbmail) I'm under the impression , sending an email in a trigger would not cause a delay.

    the old database mail from 2000 days, (whatever that was, I'm blocking it out the memory willfully now), yes, that was synchronous and evil.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Lowell (3/3/2016)


    Sean Lange (3/3/2016)


    Don't know what you want your trigger to do but be careful. Triggers fire once per operation, not once per row. So you need to create set based queries in your triggers. And stay away from sending emails directly from your trigger like the previous example does. You do NOT want your inserts to wait on that. If you want to send emails it is better to put the data in a holding table and have another process pick up the information for emails on a schedule.

    sp_send_dbmail would not cause a wait, right? it's already an asynchronous service broker.

    as long as permissions to send the mail are taken care of(so the trigger doesn't fail with the error user does not have permission to sp_send_dbmail) I'm under the impression , sending an email in a trigger would not cause a delay.

    the old database mail from 2000 days, (whatever that was, I'm blocking it out the memory willfully now), yes, that was synchronous and evil.

    You are probably right Lowell. I don't make a habit of sending emails from triggers. I think if I were doing it I would do it that way just to be sure but it is probably overkill with an asynchronous broker. Thanks for the correction.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Now if I want to grab information from this new row that has been entered, what is the best way before sending the email?

    CREATE TRIGGER reminder2

    ON Sales.Customer

    AFTER INSERT, UPDATE, DELETE

    AS

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'AdventureWorks2012 Administrator',

    @recipients = 'danw@Adventure-Works.com',

    @body = 'Don''t forget to print a report for the sales force.',

    @subject = 'Reminder';

    GO

  • TJT (3/3/2016)


    Now if I want to grab information from this new row that has been entered, what is the best way before sending the email?

    CREATE TRIGGER reminder2

    ON Sales.Customer

    AFTER INSERT, UPDATE, DELETE

    AS

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'AdventureWorks2012 Administrator',

    @recipients = 'danw@Adventure-Works.com',

    @body = 'Don''t forget to print a report for the sales force.',

    @subject = 'Reminder';

    GO

    You missed the point. You will need to look at the inserted or deleted virtual tables to retrieve those values. And then you MUST use a loop. This is why I suggest using a holding table. You can have your insert in the trigger be set based, including the values. Then have another process that uses a cursor to iterate RBAR over the holding table.

    And since you say you want values for new rows you don't want to have your trigger fire for updates or deletes right?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Hi SSCoach, do you have an example of a holding table? Also do you have an example of retrieving data from the " inserted or deleted virtual tables"?

  • TJT (3/3/2016)


    Hi SSCoach, do you have an example of a holding table? Also do you have an example of retrieving data from the " inserted or deleted virtual tables"?

    You cannot support such a thing with the knowledge you currently don't have. You really need to study up on triggers before you even think of using them. Please see the following URL for a start. I recommend you read it all and try things on a test bed before you write production code.

    https://msdn.microsoft.com/en-us/library/ms189799.aspx

    After that, I strongly recommend that you do a search on "triggers in SQL Server" using Google and learn some more. Triggers are not to be handled in a trivial manner. They will eat your face off if you don't program them correctly.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • I'd also recommend reading this [/url], since I wrote it.

  • Jack Corbett (3/4/2016)


    I'd also recommend reading this [/url], since I wrote it.

    And an excellent article for getting started with triggers. A must read!!

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply