Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2004
    Posts
    31

    Unanswered: SQL Mail trigger - add field data

    I have a trigger that uses SQL Mail to generate an email everytime a new record is added. I would like the following fields included in the email with the last record that was entered:

    ticketID
    loggedby
    ActualHours
    LoggedDate
    RequestDate
    TicketDetails
    FinalOutcome

    This is the trigger:

    CREATE TRIGGER [NewRowsAdded] ON [dbo].[tablename]
    FOR INSERT
    AS


    exec master.dbo.xp_sendmail
    @recipients = 'joe.bloggs@bloggs.co.uk',
    @subject = 'New Rows Added',
    @message = 'Hey theres some new rows? Take a peek!'

    Any assistance is greatly appreciated!

    JT

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    BOL describes this procedure very well, so check in there. In your case @query parameter should be looked at, but since it's a trigger you'd have to avoid referencing inserted table.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Apr 2004
    Posts
    31
    I have looked their and it does give me a sense but I was hoping someone would have an example that might clarify the syntax.

    Just to mention again, I want to generate the mail message with the data record that corresponds to the execution of the trigger and not all records within the table.

  4. #4
    Join Date
    Mar 2004
    Location
    West London
    Posts
    34

    Lightbulb

    The trigger fires once irrispective of how many records are going into the table. If you want to mail particular people depending upon the row you need to access the inserted table and as rdjabarov rightly says you can't do this from the trigger.

    You could use the trigger to dump the records into a table and then use a cursor (gulp did I say that) to cycle through each record and send the record via the xp_sendmail sp.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You need to capture the inserted record from inserted and store it into a permanent table.

    CREATE TRIGGER [NewRowsAdded] ON [dbo].[tablename]
    FOR INSERT
    AS
    insert permanent_table select * from inserted
    exec master.dbo.xp_sendmail
    @recipients = 'joe.bloggs@bloggs.co.uk',
    @subject = 'New Rows Added',
    @message = 'Hey theres some new rows? Take a peek!',
    @query = 'select * from permanent_table',
    @attach_results = 'true'
    delete permanent_table
    go
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Apr 2004
    Posts
    31
    thank you both. I will give it a try and let you know.

  7. #7
    Join Date
    Apr 2004
    Posts
    31
    Ok. When I attempted to enter new data through my app, I am getting an error message; "Could not execute query; could not find linked table".

    The part I am unclear on within your last post is:

    insert permanent_table select * from inserted

    1) Should I create a table called permanent_table with the same fields as my existing table with the trigger?
    2) I think I understand what you are trying to accomplish by having the inserted data publish to a separate table to capture just that record of information, email it, then delete the table but will my insert on my exisiting table still work?

    Thanks again for all your help!

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Again, I "assumed" that you'd create that "permanent_table" You DO need to create it, call it whatever you want, and then replace "permanent_table" with the actual name within the trigger. And your insert will work just fine with this syntax.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Apr 2004
    Posts
    31
    ok. That is what I thought but just wanted to make sure.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •