September 10, 2009 at 11:55 am
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
September 10, 2009 at 12:40 pm
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
September 10, 2009 at 1:35 pm
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
September 10, 2009 at 5:45 pm
No
September 10, 2009 at 8:31 pm
Have you identified all the objects that email yet?
CEWII
September 11, 2009 at 1:45 am
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.
September 11, 2009 at 10:12 am
Thats a good catch Ian, the question I still have is whether he is getting emails when there is NOT an insert.
CEWII
September 14, 2009 at 8:45 am
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