March 8, 2004 at 2:45 pm
Hi:gurus, I am setting a trigger on a table that inserts some information about the user accessing the table (Hostname etc) in a log table. My script is very generic, but some how I need to know the name of the object that is firing this trigger. Is their any global variable or function to automatically get the name of the object firing trigger...
March 8, 2004 at 6:16 pm
select object_name(parent_obj) from sysobjects where id = @@procid
March 9, 2004 at 7:59 am
Thanks for your help.
I tried it, but having a null value for the column (name of my table). Here is my sp which gets executed each time a insert, delete, or update happens. BTW is there any way to know eaxctly what kind of trigger (insert,update, delete) was fired so I can store that value too...
declare @tableName varchar(50)
select @tableName = object_name(parent_obj) from sysobjects where id = @@procid
insert into TableAcessLog values (HOST_NAME ( ) ,HOST_ID ( ), GetDate(),@tableName)
March 9, 2004 at 11:33 am
The trigger knows which table because it can only be fired from that table. IF you're trying to write a generic trigger you can copy to every table, you can do this, but you won't get the table name. You need to add it to each script.
As far as which trigger, if deleted is empty (no rows) then its an insert trigger. If inserted is empty, then it's a deleted trigger. Otherwise an update trigger.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply