trigger

  • CREATE Trigger dbo.Tab_trigger on dbo.Tab for INSERT

    as

    declare @body2 varchar(2000)

    declare @ID varchar(100)

    declare @rc int

    declare @Tab1Description varchar(2000)

    declare @Tab2Description varchar(8000)

    select @ID= I.ID , @Tab1Description= L.Tab1_Description,@Tab2Description = LS.Tab2_Description

    from Tab I

    inner join Tab1 L on I.CatID = L.Id

    inner join Tab2 LS on I.SubCatID=LS.ID

    where I.CatID = '12' or I.CatID = '14'

    SET @rc = @@ROWCOUNT

    IF @rc > 0

    BEGIN

    SET @body2 = 'ID is '+ ' '+ @ID + ' ' + 'Cat description is' + ' '+ @Tab1Description + ' '+ 'SubCat description is ' + ' '+ @Tab2Description

    EXEC master..xp_sendmail

    @recipients = 'abc@zzz.org',

    @subject = 'A new ID is created',

    @message = @body2

    END

    The above trigger is working whenever I insert a new row into Table 'Tab' an email is sent across to abc@zzz.org .

    But the problem is it is sending across random mails with the same ID to abc@zzz.org even if a row is not inserted . But if I insert a new row it sends across an email with the correct details .

    Responses are appreciated .

    Thanks

  • I'm guessing you have another trigger out there since this one will not fire for anything BUT an insert.

    How many servers are you working with?

    Also, run this code in your database (or all of them to be sure)

    SELECT DISTINCT

    o.name,

    o.type

    FROM dbo.syscomments c,

    dbo.sysobjects o

    WHERE o.id = c.id

    AND c.text LIKE '%abc@zzz.org%'

    It will return the name of any object that includes that email address.

    CEWII

  • Are the inserts part of a transaction that might be rolled back after the INSERT is completed?

    Emails can't be rolled back.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • No

  • Have you identified all the objects that email yet?

    CEWII

  • Your trigger doesn't refer to the INSERTED table, but refers to the base table (Tab) instead.

    It's therefore possible for it to pick up data already in the table, even if nothing was actually inserted by the statement causing the trigger to fire.

    Change the trigger to use the INSERTED table, or at the very least put an "IF @@rowcount = 0 Return" at the top of the trigger.

  • Thats a good catch Ian, the question I still have is whether he is getting emails when there is NOT an insert.

    CEWII

  • Thanks...Thats exactly what I identified and it worked.

Viewing 8 posts - 1 through 7 (of 7 total)

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