Post reply

Trigger on insert help

  • February 26, 2013 at 10:03 am

    #269130

    Hello All,

    I hope someone can help because I'm lost as to what the problem is...

    I have created a trigger:

    CREATE TRIGGER new_contact

    ON contacts

    FOR INSERT

    AS

    declare @contact_id varchar(11)

    declare @body varchar(2000)

    declare @staff_issued varchar(10)

    SELECT @contact_id = contact_id,

    @staff_issued = staff_issued

    FROM contacts

    SET @body = 'Customer with ID:' + @conatct_id + ' has been created by: ' + @staff_issued

    EXEC msdb.dbo.sp_send_dbmail

    @recipients = 'myemail@email.com',

    @subject = 'New Customer',

    @body = @body

    GO

    and then I run:

    INSERT INTO contacts

    (contact_id, date_issued, staff_issued)

    VALUES ('ABC12345678', GETDATE(), 'ADMIN')

    The insert works, the triggers runs and emails me but it sends the top result from the table "contacts" rather than the one that has just inserted???

    I am obviously missing something.

    Can anyone point me in the right direction??

    Many Thanks in advance

  • February 26, 2013 at 10:16 am

    #1591258

    Try this bit in the middle:

    SELECT @contact_id = contact_id,

    @staff_issued = staff_issued

    FROM inserted

    The inserted "table" contains the record you have inserted whilst contacts won't know which record you want.

  • February 26, 2013 at 10:20 am

    #1591259

    Also beware the the inserted table can contain more than one row if a multiple row insert is done.

    Far away is close at hand in the images of elsewhere.
    Anon.

  • February 27, 2013 at 4:52 am

    #1591496

    Awesome that works.

    Many Thanks

  • February 27, 2013 at 7:15 am

    #1591545

    WiRL (2/27/2013)

    Awesome that works.

    Many Thanks

    Actually, it doesn't. If you insert more than one rrow into the table that fires the trigger, the trigger will only process one row. You need to rewrite the code in a set based fashion to get all of the rows. Please see Books Online (press the {f1} key) and read up on the INSERTED and DELETED logical tables that are available when the trigger is doing its job.

    It's also a pretty bad idea to call email in a trigger. If email is down, the trigger could fail which will rollback the external transaction. It's a far bettter thing to insert what you want into a staging table and have a job sweep through that tablee to send the emails.

    --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)

Viewing 5 posts - 1 through 4 (of 4 total)

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