Trigger - Can they be bypassed?

• trabun

  SSC Enthusiast

  Points: 134

  More actions

  September 30, 2003 at 1:15 pm

  #476432

  quote:

  ---

  My thought exactly. OP was looking for a way to bypass a trigger for a specific stored procedure, not an application or user.

  ---

  Oh. I assumed something was calling the stored procedure such as an application. Is there a way to exec a procedure without running in the context of user? If so, disregard my previous post.

  BTW, The quote was not directed at you personally, it is just a signature. I quoted you because your post reflected my thoughts.

  There is always the soon-to-be-deprecated setuser command , though this would require db_owner or sysadmin not a good idea server role membership and of course, may not be supported in future releases.

  However, if the stored procedure is being called by an application, it is safe to assume that login identity is being provided. It would still be a simple matter to create a new login if necessary and add it to a new role as my previous post explained.

  Cheers

  
  It is a privilege to see so much confusion. -- Marianne Moore, The Steeplejack

• Jonathan

  SSC-Insane

  Points: 20427

  More actions

  September 30, 2003 at 1:45 pm

  #476436

  quote:

  ---

  However, if the stored procedure is being called by an application, it is safe to assume that login identity is being provided. It would still be a simple matter to create a new login if necessary and add it to a new role as my previous post explained.

  ---

  I was ironically referring to your post and the post you quoted.

  The OP's statement that "many stored procs touch the table in question and only 1 app needs to get around the trigger" may mean that your (and Ray's) methods are what's needed. I find his statements at best confusing as to definitions and at worst self-contradictory. Does he mean one SP among many needs to bypass the trigger or one application among many needs to bypass the trigger in all relevant SPs?

  I believed, based on all his posts in this thread, that the OP wants the trigger to be bypassed for a specific stored procedure. Applications may have many stored procedures, many of which may update the same table. If only one of these SPs should bypass the business rule contained in the trigger, how does knowing the application (or user) context have any relevance?

  I admit it; I'm confused now, and only Randy knows for sure.

  --Jonathan

  
  
  --Jonathan

• RandyB

  SSCommitted

  Points: 1565

  More actions

  September 30, 2003 at 2:01 pm

  #476438

  From a previous reply:

  *****************************************

  The DISABLE/ENABLE trigger looks like the tidiest one to implement since so many stored procs touch the table in question and only 1 app needs to get around the trigger.

  ***************************************

  The many stored procs refer to different apps. For the most part we are talking 1 stored proc per app. The table is a common table that many applications have update permission to.

  Thanks for everybodys input.

  For us it is a dead issue now. The developers are going to do some upfront validation which will eliminate the need for the trigger in question.