Re: Certain dynamic sql statements make stored procedures "forget" transaction?
From: ddf <oratune_at_msn.com>
Date: Wed, 15 May 2013 06:24:25 -0700 (PDT)
Message-ID: <349e6937-b7fa-45ce-bc86-15ecacefd5f3_at_googlegroups.com>
On Tuesday, May 14, 2013 10:38:23 PM UTC-6, neilsolent wrote:
> 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.
Date: Wed, 15 May 2013 06:24:25 -0700 (PDT)
Message-ID: <349e6937-b7fa-45ce-bc86-15ecacefd5f3_at_googlegroups.com>
On Tuesday, May 14, 2013 10:38:23 PM UTC-6, neilsolent wrote:
> 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.
You have that backwards -- look at the correctly functioning example again and you will see the insert is AFTER the execute immediate, not before. YOUR example does it the way you stated and it won't rollback the insert on error.
David Fitzjarrell Received on Wed May 15 2013 - 15:24:25 CEST