Triggers and constraints

Oracle documentation specifies the Trigger Execution Model which explains how triggers counteract with integrity constraints. One interesting question concerns to the intergity conception and Oracle internal mechanisms of constraint checking.

Let’s do the following:

SQL> create table t (x int primary key);

Table created.

SQL> create table t_r(x int references t(x), y int)
2 /

Table created.

SQL> create or replace trigger aifer_usual
2 after insert on t_r
3 for each row
4 begin
5 dbms_output.put_line(:new.y);
6 if :new.y = 6 then
7 insert into t values(:new.x);
8 end if;
9 end;
10 /

Trigger created.

SQL> create or replace trigger aifer_usual
2 after insert on t_r
3 for each row
4 begin
5 dbms_output.put_line(:new.y);
6 if :new.y = 6 then
7 insert into t values(:new.x);
8 end if;
9 end;
10 /

Trigger created.

SQL> set serveroutput on
SQL> insert into t_r select 1, rownum from dual connect by level select * from t;

X
———-
1

No surprices – everything works as it’s described. Really, Oracle claims what the integrity has to be guaranteed at the end of SQL operator execution (for non-deferrable constraints):

“…If any of the results of a DML statement execution violate an integrity constraint, then Oracle rolls back the statement and returns an error…
…Even though a constraint is defined to verify that each mgr value matches
an empno value, this statement is legal because Oracle effectively performs
its constraint checking after the statement completes. Figure 21-11 shows that Oracle performs the actions of the entire SQL statement before any constraints are checked…”

But if we cover trigger code in an autonomous transaction, we will find than the result is differ:

SQL> roll;

Rollback complete.

SQL> create or replace trigger aifer_usual
2 after insert on t_r
3 for each row
4 declare
5 pragma autonomous_transaction;
6 begin
7 dbms_output.put_line(:new.y);
8 if :new.y = 6 then
9 insert into t values(:new.x);
10 end if;
11 commit;
12 end;
13 /

Trigger created.

SQL> insert into t_r select 1, rownum from dual connect by level <= 8; 1 2 3 4 5 6 7 8 insert into t_r select 1, rownum from dual connect by level select * from t;

X
———-
1

Why ? After all at the end of INSERT statement all rows in the table satisfy intergity constraint.

Of course we can think that the statement checks the constraint using a single point-in-time snapshot at SCN of the statement begin. But in the case of non-autonomous trigger the parent record did not exist too at the start point of operator – and INSERT was in success.

Probably the internal mechanism of Oracle is based on the transaction diagnostic area stack processing. The following example shows the basis for that conclusion.

Let’s create the table with deferred constrain:

SQL> create table t_r(x int references t(x) deferrable initially deferred, y int);

Table created.

SQL> create or replace trigger aifer_usual
2 after insert on t_r
3 for each row
4 declare
5 pragma autonomous_transaction;
6 begin
7 dbms_output.put_line(:new.y);
8 if :new.y = 6 then
9 insert into t values(:new.x);
10 end if;
11 commit;
12 end;
13 /

Trigger created.

SQL> insert into t_r select 1, rownum from dual connect by level commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (SCOTT.SYS_C0026537) violated – parent key not
found

Notice what now at the end of transaction all data satisfy the intergity constraint too but the exception is raised like as in the example above.

But it’s easy to correct.

SQL> delete from t;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from t_r;

no rows selected

SQL> insert into t_r select 1, rownum from dual connect by level delete from t_r where y commit;

Commit complete.

But if we leave in the table any row what was inserted before insertion of the parent row, we will have our usual exception:

SQL> delete from t_r;

2 rows deleted.

SQL> delete from t;

1 row deleted.

SQL> commit;

Commit complete.

SQL> insert into t_r select 1, rownum from dual connect by level delete from t_r where y commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (SCOTT.SYS_C0026537) violated – parent key not
found

So we can imagine the internal meachinsm of constraint checking as follows (the follwing text is no more that the speculation about possible internal Oracle mechanism):

1)

For each new row the integrity constraint is checked, based on the current single point-in-time snapshot.

2)

If the constraint is violated the exception information is placed in the diagnostic area stack.

3)

Than the statement / transaction ends Oracle checks the content of the diagnostic area stack and generates the exception if it is not empty.

4)

Any DML operator in the same transaction touches the error stack and can clear it
(if it’s action corrects the prevoius errors). For this reason INSERT statement in the non-autonomous trigger “removes” errors generated by rows with y = 1…6 from the error stack. At the end of the basic INSERT statement the error stack is clear.

Autonomous DML operator can’t touche the local error stack and can’t clear it – because of this the basic INSERT failed dispite the presence of the parent row. When we use deferred constraint the diagnostic area error stack is not clear at the end of statement but is doesn’t raise the exception – Oracle waits for the transaction end to check the error stack. When we use DELETE statement we remove the rows what are the cause of errors in the stack – so we clear the stack too.

About dnikiforov

I'm writing here about everything what is in my interest - Java, Oracle, MySQL and other things. These are just remarks about things I'm working with, and if this helps you, guys, in your work - this makes me happy. ;)
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s