pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSequence of SQL command execution involving triggers and stored procedures.
2+ messages / 2 participants
[nested] [flat]
* Sequence of SQL command execution involving triggers and stored procedures.
@ 2004-09-18 04:54 Omkar Rath <orath@cisco.com>
2004-09-18 16:39 ` Re: Sequence of SQL command execution involving triggers and stored procedures. Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Omkar Rath @ 2004-09-18 04:54 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Sequence of SQL command execution involving triggers and stored procedures.
2004-09-18 04:54 Sequence of SQL command execution involving triggers and stored procedures. Omkar Rath <orath@cisco.com>
@ 2004-09-18 16:39 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2004-09-18 16:39 UTC (permalink / raw)
To: Omkar Rath <orath@cisco.com>; +Cc: pgsql-sql
"Omkar Rath" <orath@cisco.com> writes:
> 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.
In existing PG releases, AFTER triggers are always fired at the
completion of the whole interactive command (ie, the outer SELECT that
called sp_foo).
There are changes in 8.0 to fire them at the end of the specific query
that triggered them, which I believe will do what you want.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-09-18 16:39 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-09-18 04:54 Sequence of SQL command execution involving triggers and stored procedures. Omkar Rath <orath@cisco.com>
2004-09-18 16:39 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox