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.
