[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Table Name Inside Trigger

Sugandh Jain

3/28/2007 3:40:00 PM

Hi,
How do I get the Table name on which the current trigger is running on in
SQL Server 2005?

I need the name in a varchar variable.

Regards,
Sugandh Jain
5 Answers

Kalen Delaney

3/28/2007 3:40:00 PM

0

Each trigger is linked to only one table, at the time of its creation, and
whoever is writing should know the name of the table. Can't the developer
just hardcode the name of the table in an assignment to a variable, if you
really do need it in a variable,.

Perhaps I'm missing something in your question. Could you elaborate on why
you need this functionality?

--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://s...


"Sugandh Jain" <sugandh.jain@nirvana-sol.com> wrote in message
news:usqzq6UcHHA.4344@TK2MSFTNGP02.phx.gbl...
> Hi,
> How do I get the Table name on which the current trigger is running on in
> SQL Server 2005?
>
> I need the name in a varchar variable.
>
> Regards,
> Sugandh Jain
>
>
>
>
>


Aaron [SQL Server MVP]

3/28/2007 4:02:00 PM

0

Maybe some kind of auditing? In which case if you apply the same trigger
code to every table (avoiing hard-coding), you could do something like
OBJECT_NAME(@@PROCID), assuming the trigger developers are naming the
trigger appropriately (so that its name can tie directly back to exactly one
table). Or you could go back up to sys.tables within the trigger, e.g.

SELECT @table_name = o.name
FROM sys.tables o
INNER JOIN sys.triggers t
ON o.[object_id] = t.parent_id
WHERE t.[object_id] = @@PROCID;

--
Aaron Bertrand
SQL Server MVP
http://www.sq...
http://www.aspfa...




"Kalen Delaney" <replies@public_newsgroups.com> wrote in message
news:epJQz9UcHHA.648@TK2MSFTNGP04.phx.gbl...
> Each trigger is linked to only one table, at the time of its creation, and
> whoever is writing should know the name of the table. Can't the developer
> just hardcode the name of the table in an assignment to a variable, if you
> really do need it in a variable,.
>
> Perhaps I'm missing something in your question. Could you elaborate on why
> you need this functionality?
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://s...
>
>
> "Sugandh Jain" <sugandh.jain@nirvana-sol.com> wrote in message
> news:usqzq6UcHHA.4344@TK2MSFTNGP02.phx.gbl...
>> Hi,
>> How do I get the Table name on which the current trigger is running on in
>> SQL Server 2005?
>>
>> I need the name in a varchar variable.
>>
>> Regards,
>> Sugandh Jain
>>
>>
>>
>>
>>
>
>


Sugandh Jain

3/28/2007 4:18:00 PM

0

Yes, what you are saying is fine if the trigger is created at design time or
from the SQL Server Management Studio.
But in our case, the requirement is such that a new tables are generated for
certain purpose. This table is generated through a run time create table
script generated via the C# code, based on the collection of table column
names and types from the users.

Now, we need a ON INSERT Trigger on these tables. the script for the trigger
is also generated Dynamically.
But yes, your solution is simple and straight forward. I will do this
itself.

Actually this table name is lying in out database table and is needed to
pick some other values in the Trigger to proceed.

Thanks for your simple and to the point solution. It should have come to my
mind too.. but :(

Regards,
Sugandh


"Kalen Delaney" <replies@public_newsgroups.com> wrote in message
news:epJQz9UcHHA.648@TK2MSFTNGP04.phx.gbl...
> Each trigger is linked to only one table, at the time of its creation, and
> whoever is writing should know the name of the table. Can't the developer
> just hardcode the name of the table in an assignment to a variable, if you
> really do need it in a variable,.
>
> Perhaps I'm missing something in your question. Could you elaborate on why
> you need this functionality?
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://s...
>
>
> "Sugandh Jain" <sugandh.jain@nirvana-sol.com> wrote in message
> news:usqzq6UcHHA.4344@TK2MSFTNGP02.phx.gbl...
>> Hi,
>> How do I get the Table name on which the current trigger is running on in
>> SQL Server 2005?
>>
>> I need the name in a varchar variable.
>>
>> Regards,
>> Sugandh Jain
>>
>>
>>
>>
>>
>
>


Kalen Delaney

3/28/2007 5:22:00 PM

0

There still has to be some hardcoding, because the trigger header must be
different for each trigger.

--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://s...


"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:%237oTVJVcHHA.648@TK2MSFTNGP04.phx.gbl...
> Maybe some kind of auditing? In which case if you apply the same trigger
> code to every table (avoiing hard-coding), you could do something like
> OBJECT_NAME(@@PROCID), assuming the trigger developers are naming the
> trigger appropriately (so that its name can tie directly back to exactly
> one table). Or you could go back up to sys.tables within the trigger,
> e.g.
>
> SELECT @table_name = o.name
> FROM sys.tables o
> INNER JOIN sys.triggers t
> ON o.[object_id] = t.parent_id
> WHERE t.[object_id] = @@PROCID;
>
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sq...
> http://www.aspfa...
>
>
>
>
> "Kalen Delaney" <replies@public_newsgroups.com> wrote in message
> news:epJQz9UcHHA.648@TK2MSFTNGP04.phx.gbl...
>> Each trigger is linked to only one table, at the time of its creation,
>> and whoever is writing should know the name of the table. Can't the
>> developer just hardcode the name of the table in an assignment to a
>> variable, if you really do need it in a variable,.
>>
>> Perhaps I'm missing something in your question. Could you elaborate on
>> why you need this functionality?
>>
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://s...
>>
>>
>> "Sugandh Jain" <sugandh.jain@nirvana-sol.com> wrote in message
>> news:usqzq6UcHHA.4344@TK2MSFTNGP02.phx.gbl...
>>> Hi,
>>> How do I get the Table name on which the current trigger is running on
>>> in SQL Server 2005?
>>>
>>> I need the name in a varchar variable.
>>>
>>> Regards,
>>> Sugandh Jain
>>>
>>>
>>>
>>>
>>>
>>
>>
>
>


Aaron [SQL Server MVP]

3/28/2007 5:54:00 PM

0

> There still has to be some hardcoding, because the trigger header must be
> different for each trigger.

Right, I'm just thinking there may be some automated process that creates
the trigger when a table is created, maybe as part of the design or maybe as
a reactionary process, and it certainly is possible to get the table name
dynamically instead of changing that process to hard-code it.