How to get the table name in the trigger defn.

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 2008 Forums

Transact-SQL (2008)

How to get the table name in the trigger defn.

Author

Topic

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2011-12-15 : 04:13:54

CREATE TRIGGER tr_1 ON dbo.mytablename

AFTER UPDATE,INSERT

AS

INSERT INTO table1(col1)

SELECT InsRec.col1

FROM

INSERTED Ins

--Below i am calling one sp for which i have to pass the table name

EXEC myspname 'mytablename'

In the above trigger,presently i am hard coding the tablename

but is it possible to get the table name dynamically on which the trigger is defined in order to avoid hard coding the table name

i need to get the tablename dynamically without hard coding to avoid manual errors

Jai Krishna

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2011-12-15 : 04:27:19

any help please

Jai Krishna

sql-programmers
Posting Yak Master

190 Posts

Posted - 2011-12-15 : 04:35:08

Hi,

YOu can use this query to get table name of the trigger

select object_name(parent_obj) from sysobjects
where id = object_id('tr_1')

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2011-12-15 : 04:40:56

But nothing wrong to Specify the table name into the Trigger. Hardcording is not a proper word here.

Even in the above thread also we have to specify the "Trigger_name".

So you can use your table name as you are doing now. Becasue Trigger can be executed if and only if any DML hits the Particular Table.

Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources