Multiple alert counts, one response
-
We have a trigger for INSERT,UPDATE,DELETE that raises an error to call an alert which issues a response in the form of an executable. When the trigger fires, the alert count increments by three, but the response executable only gets called once. Why is the alert count increasing by 3's?
-
We can't help you without the trigger code. Can you send us the relevant part of the script?
-
CREATE TRIGGER TRG_GOAL ON DBO.GOAL
FOR INSERT, UPDATE, DELETE
AS
INSERT INTO IPGOALS.DBO.PROCESS_CONTROL_GOAL(TRIGGER_LOAD) VALUES (GETDATE())
RAISERROR(50001,10,1)
-
When are you increasing the alert count?
-
I'm not sure I know what you mean. The count happens when I use the RAISERROR function in the trigger.
Here is some other info in the alert:
Type - SQL Server event alert
Database name - All databases
Delay between responses - 5 seconds
-
I'm obviously way over my head in this thread... I'll let the gurus handle it.
-
I think the problem is here:
Database name - All databases
That alert is global shoud you have something similar on another database and it happens within 5 seconds you will get that problem.
and may I suggest you not to use raiserror inside the trigger if it is only an alert what you want to handle.
BTW is the alert calling a job that executes an exe ? and if yes there is a problem also!
hth
* Noel -
Error 50001 falls outside the reserved system errors, it is user defined and nothing else should be calling it. The Microsoft documentation suggests using RAISERROR inside triggers instead of the xp_logevent:
"When sending messages from Transact-SQL procedures, triggers, batches, and so on, use the RAISERROR statement instead of xp_logeven." This appears under the help for xp_logevent in the SQL Server books. Is there another way??
Yes, the alert is calling a job that executes an exe. Why is calling an executable a problem?
-
That is true if you need the client to execute code but if all you need is to activate an alert the Client does not need to be aware of it. At the Top of the BOL section for xp_logevent it reads:
"xp_logevent
can be used to send an alert without sending a message to the client "
And therefore this is the option of choise!
>>Why is calling an executable a problem?<<
Jobs are not re-entrant: If the exe has not finished when the next alert happened (on another DB maybe ...?) the job is not run
Does that makes sense
* Noel -
- How many times is your trigger fired ?
(I've seen systems that perform an insert and then do a couple of updates to the same row to complete data. In such cases the trigger will fire at least two times !)
- your delay wil control the response., but the counter wil increase
- Check your sqlagent.out logfile and you'll (hopefully) see it states two notifications your job is already running.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply