Re: Certain dynamic sql statements make stored procedures "forget" transaction?
From: neilsolent <n_at_solenttechnology.co.uk>
Date: Tue, 14 May 2013 21:38:23 -0700 (PDT)
Message-ID: <b571c2bd-b32a-4043-b090-531a202d3991_at_s8g2000vbw.googlegroups.com>
Thanks, makes sense.
Date: Tue, 14 May 2013 21:38:23 -0700 (PDT)
Message-ID: <b571c2bd-b32a-4043-b090-531a202d3991_at_s8g2000vbw.googlegroups.com>
Thanks, makes sense.
This will be for auditing purposes eventually - the insert will be of
a log record in an audit table if a user ran any procedure and changed
anything in the database.
Armed with your advice, I know now to insert the audit table record
just before the execute statement and to limit myself to just one
execute per procedue, and as the last entry in the procedure. Then
either: (A) the execute statement fails and the audit record is rolled
back (and user changed nothing) or (B) the next execute statement was
successful (changed the database) and the audit record is committed.
In this way audit log records represent actual database changes (which
is my requirement) - syntax errors etc are omitted.
Received on Wed May 15 2013 - 06:38:23 CEST