Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: trigger to send mail

    Hi,
    I've this table MY_TAB:
    CREATE TABLE [my_tab] (
    [description] [varchar](2000) NULL DEFAULT (null),
    [col_id] [char](32) NOT NULL DEFAULT (null),
    [requestor] [char](32) DEFAULT (null)
    )

    I create this trigger to send mail when insert a new record into my_tab table:

    CREATE TRIGGER my_tab_send_mail
    ON my_tab
    AFTER INSERT
    AS

    declare @requestor varchar(50)
    declare @body varchar(2000)
    declare @description varchar(2000)

    SELECT @requestor = requestor
    ,@description = description
    FROM inserted

    SET @body = 'The requestor '@requestor ' has this problem: '@description


    EXEC msdb.dbo.sp_send_dbmail @profile_name = 'myprofile'
    ,@recipients = 'mymail@gmail.com'
    ,@copy_recipients = 'maymail2@gmail.com'
    ,@message = @body
    ,@subject = 'new record insert'

    but I get this error when I try to create the trigger:

    Msg 102, Level 15, State 1, Procedure my_tab_send_mail, Line 14
    Incorrect syntax near '@requestor'.

    What I wrong?
    Is correct this trigger to send mail with the values from my_tab table?

    Thanks in advance

  2. #2
    Join Date
    Apr 2008
    Location
    Along the shores of Lake Michigan
    Posts
    242
    just a quick glance - Something like this should work. I don't currently have resources to test it in SQL.

    SET @body = 'The requestor ' + @requestor + ' has this problem: ' + @description

    You're concatenating the variables so you'll need the + signs. Also, depending on the number of inserts to the table, you could simply swamp sombody's inbox (thankfully, not mine!) - IMO.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Do NOT use a trigger to send mail.

    Let me repeat that:
    DO NOT USE A TRIGGER TO SEND MAIL.

    If necessary, use the trigger to populate a message table, and then have a job periodically (every minute, if necessary) check that table for messages to send.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jul 2002
    Posts
    227
    Quote Originally Posted by blindman
    Do NOT use a trigger to send mail.

    Let me repeat that:
    DO NOT USE A TRIGGER TO SEND MAIL.

    If necessary, use the trigger to populate a message table, and then have a job periodically (every minute, if necessary) check that table for messages to send.
    Why I must not use trigger to send mail?

    How can I use a job to send mail?

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Mainly because it will cause a performance problem with any insert. Maybe you won't see it on the first day, but the poor schmuck who has to support this a year from now will likely curse your name several times a day.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...and a debugging problem.
    ...and you are unnecessarily adding a point of failure to your day-to-day transaction processing.

    These should be your mantras when designing triggers:
    1) All triggers should be written to handle multi-record inserts.
    2) The scope of a trigger should be limited to its table, if possible, and to its database, at the most.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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