Determine caller within stored proc or trigger

Question

I am working with an insert trigger within a Sybase database. I know I can access the @@nestlevel to determine whether I am being called directly or as a result of another trigger or procedure.

Is there any way to determine, when the nesting level is deeper than 1, who performed the action causing the trigger to fire?

For example, was the table inserted to directly, was it inserted into by another trigger and if so, which one.

Answer 1

As far as I know, this is not possible. Your best bet is to include it as a parameter to your stored procedure(s). As explained here, this will also make your code more portable since any method used would likely rely on some database-specific call. The link there was specific for SQL Server 2005, not Sybase, but I think you're pretty much in the same boat.

Answer 2

I've not tested this myself, but assuming you are using Sybase ASE 15.03 or later, have your monitoring tables monProcessStatement and monSysStatement enabled, and appropriate permissions set to allow them to be accessed from your trigger you could try...

declare @parent_proc_id int
if @@nestlevel > 1
begin

   create table #temp_parent_proc (
    procId int,
    nestLevel int,
    contextId int
   )
   insert into #temp_parent_proc
    select  mss.ProcedureID,
            mss.ProcNestLevel,
            mss.ContextID
    from monSysStatement mss 
    join monProcessStatement mps
        on mss.KPID = mps.KPID
        and mss.BatchID = mps.BatchID
        and mss.SPID = mps.SPID
    where mps.ProcedureID =@@procid
        and mps.SPID = @@spid

    select @parent_proc_id = (select tpp.procId
                from #temp_parent_proc tpp,
                     #temp_parent_proc2 tpp2
                where tpp.nestLevel = tpp2.nestLevel-1
                  and tpp.contextId < tpp2.contextId
                  and tpp2.procId = @@procid
                  and tpp2.nestLevel = @@nestlevel
                group by tpp.procId, tpp.contextId
                having tpp.contextId = max(tpp.contextId ))

    drop table #temp_parent_proc
end

The temp table is required because of the nature of monProcessStatement and monSysStatement. monProcessStatement is transient and so if you reference it more than once, it may no longer hold the same rows. monSysStatement is a historic table and is guaranteed to only return an individual rown once to any process accessing it.

if you do not have or want to set permissions to access the monitoring tables, you could put this into a stored procedure you pass @@procid, @@spid, and @@nestlevel to as parameters.

If this also isn't an option, since you cannot pass parameters into triggers, another possible work around would be to use a temporary table.

in each proc that might trigger this...

create table #trigger_parent (proc_id int)
insert into #trigger_parent @@procid

then in your trigger the temp table will be available...

if object_id('#trigger_parent') is not null 
    set @parent_proc = select l proc_id from #trigger_parent

you will know it was triggered from within another proc.

The trouble with this is it doesn't 'just work'. You have to enforce temp table setup. You could do further checking to find cases where there is no #trigger_parent but the nesting level > 1 and combine a similar query to the monitoring tables as above to find potential candidates that would need to be updated.