This is a multi-part message in MIME format.
------_=_NextPart_001_01C49D3B.91BA5806
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
I am observing the following:
Say there is a stored procedure i.e FUNCTION sp_foo(...) which has say,
SQL commands (any of SELECT,INSERT,UPDATE,DELETE) in its body, say S1
followed by S2 then S3.... Furthermore, execution of statement S2
results in trigger functions getting invoked (that contain yet other SQL
statements).
By the time, the trigger functions execute (as a result of S2), S3 has
already executed.
Note that sp_foo() was invoked via a SELECT i.e SELECT sp_foo(...).
To work around this "behaviour", I wrapped sp_foo() within another
FUNCTION say sp_foo_wrapper(...) and in the body of sp_foo_wrapper() I
did this -
1. INSERT <something> in a scratch table // akin to setting a flag
2. Invoke sp_foo().
3. DELETE <something> from the scratch table // akin to resetting the
flag
Even then, by the time the triggers fired as a result of S2 (in the body
of sp_foo), the DELETE (in step 3 above) had executed.
Questions:
1. Did I miss something very basic?
2. Is this behavior random i.e the manifestation pertians to the
particular SQL commands in my code?
3. How does one achieve, the chronolgy that I want i.e I want S3 AND/OR
step 3 to occur AFTER the triggers (as a result of S2) to fire?
Thanx in advance,
Omkar Rath
Software Engr.
VTG
Cisco Systems Inc.
------_=_NextPart_001_01C49D3B.91BA5806
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; charset=3Dus-ascii">
<TITLE>Message</TITLE>
<META content=3D"MSHTML 6.00.2800.1458" name=3DGENERATOR></HEAD>
<BODY>
<DIV><SPAN class=3D567253304-18092004><FONT face=3DArial size=3D2>I am obse=
rving the
following:</FONT></SPAN></DIV>
<DIV><SPAN class=3D567253304-18092004><FONT face=3DArial
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D567253304-18092004><FONT face=3DArial size=3D2>Say there=
is a
stored procedure i.e FUNCTION sp_foo(...) which has say, SQL commands =
(any
of SELECT,INSERT,UPDATE,DELETE) in its body, say S1 followed by S2 then S3.=
....
Furthermore, execution of statement S2 results in trigger functions ge=
tting
invoked (that contain yet other SQL statements).</FONT></SPAN></DIV>
<DIV><SPAN class=3D567253304-18092004><FONT face=3DArial
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D567253304-18092004><FONT face=3DArial size=3D2>By the ti=
me, the
trigger functions execute (as a result of S2), S3 has already
executed.</FONT></SPAN></DIV>
<DIV><SPAN class=3D567253304-18092004><FONT face=3DArial
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D567253304-18092004><FONT face=3DArial size=3D2>Note that=
sp_foo()
was invoked via a SELECT i.e SELECT sp_foo(...).</FONT></SPAN></DIV>
<DIV><SPAN class=3D567253304-18092004><FONT face=3DArial
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D567253304-18092004><FONT face=3DArial size=3D2>To work a=
round this
"behaviour", I wrapped sp_foo() within another FUNCTION say sp_foo_wrapper(=
....)
and in the body of sp_foo_wrapper() I did this -</FONT></SPAN></DIV>
<DIV><SPAN class=3D567253304-18092004><FONT face=3DArial
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D567253304-18092004><FONT face=3DArial size=3D2>1. INSERT=
<something> in a scratch table // akin to setting a
flag</FONT></SPAN></DIV>
<DIV><SPAN class=3D567253304-18092004><FONT face=3DArial size=3D2>2. Invoke=
sp_foo().</FONT></SPAN></DIV>
<DIV><SPAN class=3D567253304-18092004><FONT face=3DArial size=3D2>3. DELETE=
<something> from the scratch table // akin to resetting the
flag</FONT></SPAN></DIV>
<DIV><SPAN class=3D567253304-18092004><FONT face=3DArial
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D567253304-18092004><FONT face=3DArial size=3D2>Even then=
, by the
time the triggers fired as a result of S2 (in the body of sp_foo), the DELE=
TE
(in step 3 above) had executed.</FONT></SPAN></DIV>
<DIV><SPAN class=3D567253304-18092004><FONT face=3DArial
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D567253304-18092004><FONT face=3DArial
size=3D2>Questions:</FONT></SPAN></DIV>
<DIV><SPAN class=3D567253304-18092004><FONT face=3DArial size=3D2>1. Did I =
miss
something very basic?</FONT></SPAN></DIV>
<DIV><SPAN class=3D567253304-18092004><FONT face=3DArial
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D567253304-18092004><FONT face=3DArial size=3D2>2. Is thi=
s behavior
random i.e the manifestation pertians to the particular SQL commands in my=
code?</FONT></SPAN></DIV>
<DIV><SPAN class=3D567253304-18092004><FONT face=3DArial
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D567253304-18092004><FONT face=3DArial size=3D2>3. How do=
es one
achieve, the chronolgy that I want i.e I want S3 AND/OR step 3 to occur AFT=
ER
the triggers (as a result of S2) to fire?</FONT></SPAN></DIV>
<DIV><SPAN class=3D567253304-18092004><FONT face=3DArial
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D567253304-18092004><FONT face=3DArial size=3D2>Thanx in=
advance,</FONT></SPAN></DIV>
<DIV><SPAN class=3D567253304-18092004><FONT face=3DArial
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D567253304-18092004><FONT face=3DArial
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D567253304-18092004><FONT face=3DArial
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D567253304-18092004><FONT face=3DArial size=3D2>Omkar
Rath</FONT></SPAN></DIV>
<DIV><SPAN class=3D567253304-18092004><FONT face=3DArial size=3D2>Software=
Engr.</FONT></SPAN></DIV>
<DIV><SPAN class=3D567253304-18092004><FONT face=3DArial
size=3D2>VTG</FONT></SPAN></DIV>
<DIV><SPAN class=3D567253304-18092004><FONT face=3DArial size=3D2>Cisco Sys=
tems
Inc.</FONT></SPAN></DIV></BODY></HTML>
------_=_NextPart_001_01C49D3B.91BA5806--
