Re: Can you prevent commits from a client application?
From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 22 Aug 2008 21:19:27 -0700
Message-ID: <1219465156.280047@bubbleator.drizzle.com>
>
> I've implemented and been using the new (check 1=0) constraint method
> and it appears to be a good solution that I will maintain in
> production code. DDL statements, truncates, and other sources of
> commits have been caught and typically the transaction is rolled back
> if there is any attempt to commit, while the statement itself also
> appears to fail. For example doing a truncate does the following: (1)
> Roll back uncommitted changes, including the insert into the table
> that contains the check. (2) Provide an error message (see below). (3)
> Statement fails.
>
> CREATE TABLE A (DUMMY NUMBER);
>
> Table created.
>
>
> CREATE TABLE TRANSACTION_STOP (DUMMY NUMBER NOT
> NULL,
> CONSTRAINT "TRANSACTION_STOP_I" CHECK(1=2) DEFERRABLE
> INITIALLY DEFERRED) ;
>
> Table created.
>
>
>
>
> insert into A values (1);
>
> 1 row created.
>
> commit;
>
> Commit complete.
>
> insert into transaction_stop values (1); -- <- contains deferred
> check(1=0) constraint
>
> 1 row created.
>
> truncate table A;
> *
> ERROR at line 1:
> ORA-02091: transaction rolled back
> ORA-02290: check constraint (GC_FEB08.TRANSACTION_STOP_I) violated
>
>
> select * from A; -- Test if truncate succeeded.
>
> DUMMY
> ----------
> 1
>
> 1 row selected.
Date: Fri, 22 Aug 2008 21:19:27 -0700
Message-ID: <1219465156.280047@bubbleator.drizzle.com>
dean wrote:
> On Aug 15, 8:29 pm, dean <deanbrow..._at_yahoo.com> wrote:
>> On Jul 16, 3:30 pm, Kai Rode <nik..._at_web.de> wrote: >> >>>> Sorry I'm not being clear here - I would like do a series of updates >>>> and inserts, and then once all that is finished, turn off any ability >>>> to make changes. In other words, a run-time and dynamic change in the >>>> read-only state (such as with alter session). >>> My idea (haven't had time to test yet): Create a table with a deferred >>> constraint CHECK (1=0). Insert a row into that table, then let your testers >>> play. If anyone tries to commit, the commit will be prevented by the failed >>> checking of the deferred constraint. >> Interesting! I'll try it and post up either way whether it works or >> not. >> >> Thanks! >> >> -Dean
>
> I've implemented and been using the new (check 1=0) constraint method
> and it appears to be a good solution that I will maintain in
> production code. DDL statements, truncates, and other sources of
> commits have been caught and typically the transaction is rolled back
> if there is any attempt to commit, while the statement itself also
> appears to fail. For example doing a truncate does the following: (1)
> Roll back uncommitted changes, including the insert into the table
> that contains the check. (2) Provide an error message (see below). (3)
> Statement fails.
>
> CREATE TABLE A (DUMMY NUMBER);
>
> Table created.
>
>
> CREATE TABLE TRANSACTION_STOP (DUMMY NUMBER NOT
> NULL,
> CONSTRAINT "TRANSACTION_STOP_I" CHECK(1=2) DEFERRABLE
> INITIALLY DEFERRED) ;
>
> Table created.
>
>
>
>
> insert into A values (1);
>
> 1 row created.
>
> commit;
>
> Commit complete.
>
> insert into transaction_stop values (1); -- <- contains deferred
> check(1=0) constraint
>
> 1 row created.
>
> truncate table A;
> *
> ERROR at line 1:
> ORA-02091: transaction rolled back
> ORA-02290: check constraint (GC_FEB08.TRANSACTION_STOP_I) violated
>
>
> select * from A; -- Test if truncate succeeded.
>
> DUMMY
> ----------
> 1
>
> 1 row selected.
To some extent you have recreated the wheel.
All currently supported versions of Oracle contain DDL triggers that can trap is block any or all DDL.
Check it out:
http://www.psoug.org/reference/ddl_trigger.html
-- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Fri Aug 22 2008 - 23:19:27 CDT