Trigger to find caller stored procedure

Question

Trigger to find caller stored procedure

Raul Undreiner

SSCommitted

Points: 1554
More actions
January 5, 2006 at 9:01 am

#170465

I have an amount column on a table that sometimes is being updated with four decimals instead of the regular 2 for amounts. Apparently that number is calculated but I haven't been able to locate how or where. So, I thought on creating a temporary trigger to check when it is updated but I don't know how to get the name of the caller procedure. Anyone can help pointing to where to find the variable that contains it. And what other variables are available.
Here is what I have so far:
CREATE TRIGGER AmountWith4Decimals
ON receipts
FOR INSERT, UPDATE
AS
if update (receiptamount)
if round(receiptamount,2) > receiptamount
begin
   EXEC master..xp_sendmail @recipients =  MyEmail,
    @subject = 'Amount with 4 decimals',
    @message = 'Amount has been created with 4 decimals by:' + StoredProcedureName
end
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

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

Raul Undreiner SSCommitted Points: 1554 More actions · Answer 1

I had to make some changes to the trigger to make it work but now I am getting the name of the trigger instead of the stored procedure that made it go off.

Here is the new trigger:

ALTER TRIGGER AmountWith4DecimalsReceipts

ON receipts

AFTER INSERT, UPDATE

AS

set nocount on

Declare @RAmount as money

if update (Receiptamount)

begin

select @RAMount = ReceiptAmount from inserted

if round(@RAmount,2) > @RAmount

begin

declare @mensaje varchar(200)

set @mensaje = 'Amount has been created with 4 decimals by Procid: ' + object_name(@@procid) + ' SPID: ' + cast(@@SPID as varchar)

EXEC master..xp_sendmail @recipients = MyEmail,

@subject = 'Amount with 4 decimals',

@message = @mensaje

end

set nocount off

Is it possible to receive the name of the procedure being executed when the trigger went off?

Sameer Sakhare SSC Journeyman Points: 75 More actions · Answer 2

Hi there,

I can't think of a quick way which you can implement to find the name of the stored procedure that is causing the trigger to fire but you can nail down the application that is executing the stored procedure by using the app_name() function.

Sorry about not being able to provide an exact answer but its a start...

Cheers,

Sameer

sushila SSC-Dedicated Points: 35293 More actions · Answer 3

If the amount column is to be accurate only upto 2 decimals wouldn't it be easier to just alter the column for precision ?!?!

Another way that I can think of is to query the system tables to see how many stored procedures are modifying this table and then "eyeballing" them to find the culprit - I'm assuming, of course, that you don't have zillions of stored procedures all modifying the same column...

Your query would be something like this:

select so.name, sc.text from syscomments sc
inner join sysobjects so
on sc.id = so.id
where so.xtype = 'P' and
so.name not like 'dt%' and
sc.text like '%receipts%'

**ASCII stupid question, get a stupid ANSI !!!**