Any way to get table name''s trigger?

Question

Any way to get table name''s trigger?

Miquel Martí

Ten Centuries

Points: 1217
More actions
November 17, 2005 at 2:40 am

#89959

Hi agn' !
I'm dinamically generating triggers for all the tables that has to be audited in the database.
In the scope of a trigger... Is there any way I can retrieve the table name's trigger?
Kind regards!
PD: When finished, I swear I'll post my audit paradigm for the comunity

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jan-155192 SSCrazy Points: 2613 More actions · Answer 1

Miguel,

When executing a trigger, you are in fact logically in a transaction. This means that there is a lock on at least one record of your table. You can check the locks of the current process:

CREATE TRIGGER [ti_test] ON [dbo].[authors]

FOR UPDATE

AS

DECLARE @sObjName varchar(255)

select @sObjName = object_name(rsc_objid)

from master.dbo.syslockinfo

where req_spid = @@spid

EXEC master..xp_logevent 60000, @sObjName, informational

and then, I could read my table name in the event log.

Probably some finetuning is necessary to chech that you are verifying the correct lock. Index locks may exist, or additional object locks if your trigger logic is complex. You probably should check the lock type.

Another problem is that there may be more then one trigger on the same table.

Another direction to look for a solution might be extended properties

Hope this helps

Jan

Miquel Martí Ten Centuries Points: 1217 More actions · Answer 2

Thanks Jan.

Your idea is very intelligent, but I'm scared that this can hog my system pipe; and may be this could create some unconsitency if there are two triggers at once in the same table.

I'll keep investigating.

Thanks so 4 ya thoughs!

PD: I love PL :p

noeld SSC Guru Points: 96590 More actions · Answer 3

you can get it from sysobjects using parent_obj:

CREATE TRIGGER ...

select object_name(parent_obj) as Tablename

from sysobjects

where id = @@procid and xtype ='TR'

but I don't know why would you do that?

If you are 'generating' the trigger code, you definitely know the table name on the generator

* Noel

Miquel Martí Ten Centuries Points: 1217 More actions · Answer 4

I have a trigger on AuditTables containing table names to audit.

When the user inserts a new table to be audited, a new trigger is created for the table name he/she inserted.

May be is easyer to generate the trigger with the propper table name in it, instead of writing a generic one that works with everybody.... but for testing it would have been util (and fanzy) 8)

C U!

Thanks for your ideas

noeld SSC Guru Points: 96590 More actions · Answer 5

Correct! it is easier and more efficient to simply add the tableName at generation time.

Now, what I consider no such a good Idea is to create the triggers on the fly

Why?

Instability may be generated due to unexpected sequecing
The final code in production is effectively untested (no QA nor unit test done)
The locking issues that may appear are totally unforseen

Don't get me wrong, I do like the Idea of a Trigger generator, in fact that's I do. But everything has its time and place. The gererated code is done offline, then it is applied to a test and afterwards to a QA environment and finally rolled out to production

Cheers,

* Noel