Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    43

    Unanswered: sql 2005 trigger insert and dbmail

    I have a stored proc that inserts records into a table, gets the identity, and uses that identity on a .net webpage. The page retrieves that identity key and processes some stuff on the page.

    Everything was working fine until I tried to enable sql mail with triggers. I wanted to double-check some stuff, so I requested an sql email be sent whenever an insert occurs on the table above. Here's what I think is happening --- please correct and/or help me out with a work around?

    I use the stored proc to insert a record.
    I select the @@identity.
    The trigger fires but uses select to retrieve the latest insert - thereby replacing the @@identity number returned to the page?

    If this is true, could I do something like the following in the original stored proc? Is this a good idea or bad idea?

    BEGIN
    Declare @myID as int, @myBody1 as varChar(200)
    Set @myID=0
    INSERT INTO table (fields) VALUES (@PID, more stuff);
    Set @myID = SELECT @@IDENTITY As [Identity];
    If @myID<>0
    Begin
    Set @body1='<br />pid=' + more stuff.....
    Exec msdb.dbo.sp_send_dbmail
    @profile_name='profileName',
    @recipients='email@email.com',
    @subject='Temp History Insert',
    @body=@body1,
    @body_format= 'HTML' ;
    End
    END

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by janetb
    Everything was working fine until I tried to enable sql mail with triggers.
    don't worry everyone makes the mistake of doing this.

    SCOPE_IDENTITY instead of @@IDENTITY will fix this.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Oct 2003
    Posts
    43
    Close - can I trouble you for one more thing? When I try to do the following, the email body comes out blank. If I remove the reference to the @myID, it comes through. So, is it really returning the Identity field or not? Why does it crash the email body? Thanks for your patience - I'm brand new to triggers and sql emails so it's a slogging process right now.

    declare @myID as varChar(4)
    --do my insert stuff
    SELECT @MyIdentity = Scope_Identity();
    If @myID<>'0'
    Begin
    Set @body1='<br />pid=' + @myID + 'more stuff.....'
    Exec msdb.dbo.sp_send_dbmail
    @profile_name='profileName',
    @recipients='email@email.com',
    @subject='Temp History Insert',
    @body=@body1,
    @body_format= 'HTML' ;
    End

  4. #4
    Join Date
    Oct 2003
    Posts
    43
    And, I forgot, why would the original trigger screw with the store proc return value? Any ideas?

Posting Permissions

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