Trigger - How to get tablename

[tomjscribe]

I want to create a generic trigger that KNOWS what table it is built against. I took a guess the @@tablename might be a system variable and hoped the following code would work:

declare @tblname as char(50)
select @tblname = @@tablename

It did not.

ANY CLUES.

Again I want to write a generic Trigger that knows which table it is build against.

Thanks

 

[TomSark]

Fear not, young programmer... this is possible.

CREATE TRIGGER [authors_test_trg] ON [authors]
FOR delete as
-- get the table (parent) name

SELECT 'table name' = OBJECT_NAME( parent_obj )
FROM sysobjects
WHERE id = @@PROCID

Hope this helps... Tom Davis
tdavis@sark.com

http://www.sarkdallas.com

 

[foxdev]

I'm pleased with Tom's answer, because I wanted to know this, too, out of curiousity.

But - since a trigger is always built on a specific table, couldn't you just code it in? For example:

[tt]create trigger mytrigger on mytable
for insert, update, delete
as
declare @tablename varchar(20)
set @tablename = 'mytable'
blah blah blah [/tt]

I know there must be some reason why this isn't appropriate for your situation.

Robert Bradley

http://www.foxdev.com/

 

[TomSark]

Hi Robert, long time no talk to... (I've been busy lately)

You sure could, but I was answering the question due along the lines of determining a generic way to do it, as he stated.

I think it would be a lot easier to code the table name in the trigger as well. But, if you run sp_rename on the table, then you will have to change the trigger... don't know if this is his reason for this need or not...
Tom Davis
tdavis@sark.com

http://www.sarkdallas.com

 

[foxdev]

No no, Tom, your answer was great - in fact, I was eagerly awaiting the answer myself; it is a cool technique. I was only curious as to why not take the easy way out.

And yes, I noticed you seemed to be absent lately (as opposed to my being absent-minded).

Robert Bradley

http://www.foxdev.com/