|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
obtaining the names of triggers within T-SQLI 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 error 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 Show quote "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 error > 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. > 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? Show quote "Alejandro Mesa" wrote: > 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 error > > 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. > > > That will work only if one can safely assume one has control over what Yes - And why wouldn't one make this assumption? Any "solution" can be > users > and roles will be permitted in the database, won't it? 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 you can't answer it. Show quote "Scott Morris" 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). > > > 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 Hi Brian,>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? 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 The only correct answer to your original post would be to dump the idea>post, and (hopefully) clarified here. Please feel free not to respond if you >can't answer it. 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. Show quote "Hugo Kornelis" wrote: > 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 it > >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 you > >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 >  |
|||||||||||||||||||||||
Other interesting topics