obtaining the names of triggers within T-SQL

I have a db containing 2 base tables and a view with an INSTEAD OF INSERT
trigger that updates both tables. I want to prevent inserts on the base
tables themselves with an INSTEAD OF INSERT trigger that would raise an erro
r
indicating that the appliation should insert against the view and then
rolling back the insert on the base table.
Is there a way to find out the names of the triggers in play so I could do
this? (I've experimented with trigger_nestlevel() but it doesn't do exactly
what I want.)
This is on SQL Server 2000.Brian,
Deny "insert" permission on the table, grant "insert" on the view.
AMB
"Brian Cobb" wrote:

> I have a db containing 2 base tables and a view with an INSTEAD OF INSERT
> trigger that updates both tables. I want to prevent inserts on the base
> tables themselves with an INSTEAD OF INSERT trigger that would raise an er
ror
> indicating that the appliation should insert against the view and then
> rolling back the insert on the base table.
> Is there a way to find out the names of the triggers in play so I could do
> this? (I've experimented with trigger_nestlevel() but it doesn't do exact
ly
> what I want.)
> This is on SQL Server 2000.
>|||That will work only if one can safely assume one has control over what users
and roles will be permitted in the database, won't it?
"Alejandro Mesa" wrote:
> Brian,
> Deny "insert" permission on the table, grant "insert" on the view.
>
> AMB
> "Brian Cobb" wrote:
>|||> That will work only if one can safely assume one has control over what
> users
> and roles will be permitted in the database, won't it?
Yes - And why wouldn't one make this assumption? Any "solution" can be
circumvented by someone with administrative access to the database. The
suggestion, by far, is the most straightforward one and involves no obscure
code to determine the "triggers in play" (without know specifically what
that means).|||I thought "triggers in play" was obvious given the context in which it was
used in my original post. But let me try to clarify. If a trigger,
trigger1, performs an action that initiates another trigger, trigger2, is it
possible within trigger2 to determine the name of the initiating trigger?
I am only interested in an answer to my question as posed in the original
post, and (hopefully) clarified here. Please feel free not to respond if yo
u
can't answer it.
"Scott Morris" wrote:

> Yes - And why wouldn't one make this assumption? Any "solution" can be
> circumvented by someone with administrative access to the database. The
> suggestion, by far, is the most straightforward one and involves no obscur
e
> code to determine the "triggers in play" (without know specifically what
> that means).
>
>|||On Fri, 17 Mar 2006 12:41:26 -0800, Brian Cobb wrote:

>I thought "triggers in play" was obvious given the context in which it was
>used in my original post. But let me try to clarify. If a trigger,
>trigger1, performs an action that initiates another trigger, trigger2, is i
t
>possible within trigger2 to determine the name of the initiating trigger?
Hi Brian,
There's no easy way to do that. The only method I can think of is to
query TRIGGER_NESTLEVEL(object_id) with the object_id of each trigger
that might be active when trigger2 runs; if it's not 0, trigger2 must
have been fired (either directly or indirectly) by that trigger.

>I am only interested in an answer to my question as posed in the original
>post, and (hopefully) clarified here. Please feel free not to respond if y
ou
>can't answer it.
The only correct answer to your original post would be to dump the idea
of enforcing this with triggers. Use DCL, as suggested by Alejandro.
Hugo Kornelis, SQL Server MVP|||I suspected it probably couldn't be done the way I hoped but wanted some
outside input. I had already considered something similar to what Mr. Mesa
had suggested, prior to my first post, which I will now revisit.
Thank you for your response.
"Hugo Kornelis" wrote:

> On Fri, 17 Mar 2006 12:41:26 -0800, Brian Cobb wrote:
>
> Hi Brian,
> There's no easy way to do that. The only method I can think of is to
> query TRIGGER_NESTLEVEL(object_id) with the object_id of each trigger
> that might be active when trigger2 runs; if it's not 0, trigger2 must
> have been fired (either directly or indirectly) by that trigger.
>
> The only correct answer to your original post would be to dump the idea
> of enforcing this with triggers. Use DCL, as suggested by Alejandro.
> --
> Hugo Kornelis, SQL Server MVP
>