how do I identify the calling procedure that caused a trigger to fire?
-
here's the trigger code. Effectively, I want SQL to send me an email everytime someone changes an instrument's associated country to 'QQ'. This works great when a developer does so in Query Analyzer, however, this is rarely the case. Is there any way to get the name of the calling application or ideally, the Windows login of the user that caused the trigger to fire? I could have sworn I saw this data in SQL Profiler at some point...
thanks in advance.
Greg
-----------------
ALTER trigger trgInstGregTemp
on tbInst
for insert, update as
declare @newCountry int
declare @SevernTicker varchar(128)
declare @InstsAffected int
declare @EditedByName varchar(128)
declare @messagebody varchar(1024)
select @InstsAffected = count(*)
from inserted i
if @InstsAffected = 1
select @newCountry = i.InstCountryID,
@SevernTicker = i.SevernTicker,
@EditedByName = EP.EtcPersonName
from inserted i
inner join tbEtcPerson EP
on i.EditorID = EP.EtcPersonID
if @newCountry = 1
begin
set @SevernTicker = 'SevernTicker: ' + @SevernTicker + ' -- country updated to ''QQ'' by ' + ISNULL(@EditedByName, 'unknown')
exec master..xp_sendmail
@recipients = 'greg.johnson@severnrivercapital.com; jeff.francis@severnrivercapital.com',
@message = @SevernTicker,
@subject = 'bad country assigned to Instrument'
end
---------------------------
-
Use @@SPID to join to master..sysprocesses.
There are functions for returning specific items, but this is the old fashioned way I always use.
-
rockin suggestion john. Here's my uber-quality @message now...
SevernTicker: cyCAD -- country updated to 'QQ'
NTLOGIN: greg johnson
MACHINE: CT-DEV-05
PROGRAM: SQL Query Anal
-
another pointer...you should never have a trigger actually send an email; it can take several seconds for the email step to occur, and if there is a network or other mail server error that raises an unexpected error, you could cause the trigger to fail.
you should have the trigger insert a record into a separate table with the email information, and that table is processed by a scheduled job to send any items that have not been flagged as successfully sent. getting that job to run every few minutes or whatever is appropriate.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Is there anyway to get the name/id of the stored procedure that caused the trigger to fire?
-
IF
- You are using SQL 2000
- You can modify (a little bit
) the stored procedures - You don't permit direct access to the tables
Then:
Change the SPs as:
--at the begining of the proc
Declare @data varbinary(128)
SET @data = CAST( @@PROCID as binary(4) )
SET CONTEXT_INFO @data
........
<the proc code >
......
-- at the end
SET CONTEXT_INFO 0x0
On the Trigger:
Declare @Calling_Proc varchar(128)
select @Calling_Proc = object_name(substring( p.context_info, 1, 4 ) ) from master..sysprocesses as p WHERE p.spid = @@SPID
hth
* Noel - I am using SQL Server 2000. I have complete control over the stored procedures. I'm not allowing direct access to the tables but I want the triggers to work if someone does directly access the tables.All of that having been said, using the Context_Info looks like it will do exactly what I need.Thanks.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply