Get Table Name for which the trigger is defined

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Get Table Name for which the trigger is defined

Author

Topic

DMP
Starting Member

41 Posts

Posted - 2005-12-19 : 01:56:59

Within a trigger, is there a way to get the table name for which the trigger is defined?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-19 : 02:31:35

What is your requirement ?

A (Insert / Update /Delete)trigger is attached to a table. You can't have one trigger attached to multiple trigger. When a trigger is being fired, either for Insert / Update / Delete, it must be of the attached table it can't be fired on another table.

-----------------
[KH]
Learn something new everyday

DMP
Starting Member

41 Posts

Posted - 2005-12-19 : 02:54:31

Thanks Khtan,
That I Know, But U missed my requirement,
I Want to get the table name dynamically at the time of Trigger Fired.

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2005-12-19 : 04:31:46

if you know the trigger name this could help you out..

select name from sysobjects
where id in (select parent_obj
from sysobjects where xtype = 'tr' and name = '<triggername>')

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-19 : 04:37:05

DMP,

I don't think you can find out the table name of a trigger and the trigger is fired.

Looking at your previous posts regarding trigger, I am guessing you are calling a stored procedure inside your trigger. Well, . . . just hardcode the table name in the trigger and pass the table name to your stored procedure. That should do the job.

create trigger mytable_trigger on mytable ....
     exec your_sp 'mytable'
...

-----------------
[KH]
Learn something new everyday

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-19 : 04:38:50

or

select object_name(parent_obj)
from sysobjects where xtype = 'tr' and name = 'yourTrigger'

Madhivanan

Failing to plan is Planning to fail

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2005-12-19 : 05:09:26

thanks for object_name i was not aware of that :)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-19 : 06:27:06

Well. That will avoid querying the table again

Madhivanan

Failing to plan is Planning to fail

DMP
Starting Member

41 Posts

Posted - 2005-12-19 : 07:01:13

Khtan,
U r right,
Now I am following exec your_sp 'mytable' process

But my purpose retrive the table name from SQL Server instead of hardcode 'mytable'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-19 : 07:47:56

Did you see other replies?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources