Calling procedure / trigger

• schleep

  SSChampion

  Points: 12586

  More actions

  February 20, 2007 at 9:33 am

  #116280

  Hi all,

  I'm *pretty* sure this is not possible, but:

  Sproc A affects Table B which has Trigger C which calls Sproc D

  From within Sproc D, can I find out programmatically that Sproc A is at the head of the stack?

  Thanks,

  Paul

• Chuck Rivel

  SSCrazy

  Points: 2991

  More actions

  February 20, 2007 at 10:07 am

  #690701

  my first thought is that trigger c would not even know how table b was affected, only that it had an event to execute from.

  table b could have been affected via either a manual sql statement or executing proc a, the trigger would not care either way and i do not believe even knows.

  with that being said, there would be no way for proc d to know about proc a.

   

• R Michael

  SSCarpal Tunnel

  Points: 4211

  More actions

  February 21, 2007 at 7:50 am

  #690901

  There are a couple of possibilities that I know of.

  Yousef Ekhtiari wrote a great article about using

  CONEXT_INFO

  to acomplish something like that (http://www.sqlservercentral.com/columnists/yEkhtiari/2765.asp).

  You can also query

  DBCC INPUTBUFFER

  There is a good example on how to implement it here:

  http://vyaskn.tripod.com/tracking_sql_statements_by_triggers.htm

  Essentially what this will do is tell you what the original TSQL statement was.

  Good Luck!

  SQL guy and Houston Magician

• Chuck Rivel

  SSCrazy

  Points: 2991

  More actions

  February 21, 2007 at 8:00 am

  #690911

  good information to know. thanks robert

• schleep

  SSChampion

  Points: 12586

  More actions

  February 21, 2007 at 8:28 am

  #690927

  Robert,

  Another good day, I learned something new (and useful).

  Thanks!!

  P

• R Michael

  SSCarpal Tunnel

  Points: 4211

  More actions

  February 21, 2007 at 10:11 am

  #690975

  Anytime! Glad I could help

  SQL guy and Houston Magician