Home » SQL & PL/SQL » SQL & PL/SQL » Trigger Execution (Oracle 10g, 10.2.0.1.0, Windows XP)
Trigger Execution [message #448777] Thu, 25 March 2010 03:03 Go to next message
s4.ora
Messages: 63
Registered: March 2010
Member
Hello everyone,

i have an understanding about Integrity constraint checking and the trigger execution sequence, which i decsribe below.

Integrity constraint are restiction on DML operation performed by the user. When a user deletes or updates or inserts a rows in a table then oracle performs certain checking to see that the data which is effecting the row abide certain rules. There are certain per-defined set or rules that can be applied on a table such as PRIMARY KEY, FORIEGN KEY, UNIQUE, CHECK, NOT NULL etc, user-defined rules can be applied on tables by using Triggers.

In both the cases the Integrity Constraint Checking is deferred until the complete execution of the statement. All rows are inserted first, then all rows are checked for constraint violations.

So when i see the trigger execution model the following steps are performed by oracle, Oracle uses the following execution model to maintain the proper firing sequence of multiple triggers and constraint checking:

This is what the Oracle Documentation Library says [extract from Oracle Database Concepts 10g Release 1 (10.1)].

1. Run all BEFORE statement triggers that apply to the statement.

2. Loop for each row affected by the SQL statement.
a.Run all BEFORE row triggers that apply to the statement.
b.Lock and change row, and perform integrity constraint checking. (The lock is not released until the transaction is committed.)
c.Run all AFTER row triggers that apply to the statement.

3.Complete deferred integrity constraint checking.

4.Run all AFTER statement triggers that apply to the statement.

As for step 3 here the checking of the constraints for the statement is performed which where defered till the complete execution of the statement,
then what is done in step 2b? what constraints are checked there?

Re: Trigger Execution [message #448783 is a reply to message #448777] Thu, 25 March 2010 03:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The obvious difference between step 2b and step 3 is that step 3 refers to deferred integrity constraints.

Do you have a link to this - as far as I know, deferred integrity constraints are enforced on commit, not as part of any one DML.

<Pause for coding>

I've tested it, and Deferred Constraints definitely aren't validated during row insert - or if they are, then nothing is done with the validation until the change is committed.

Here's the code I used. Insert values into table test_155, commit, and see what values show up in the log:
create table test_155 (col_1  number);

create table test_155_log (id  number
                          ,log_col   varchar2(1000));
                          
create sequence test_155_seq;

alter table test_155 add constraint test_155_ckd check (col_1 >0) deferrable initially deferred;
/

create or replace procedure test_155_do_log (p_text  in  varchar2) as
  pragma autonomous_transaction;
begin
  insert into test_155_log(id,log_col) values (test_155_seq.nextval,p_text);
  commit;
end;
/

create or replace trigger test_155_bi_trg
before insert on test_155
begin
  test_155_do_log(' Before Insert Statement Level');
end;
/

create or replace trigger test_155_bri_trg
before insert on test_155 for each row
begin
  test_155_do_log(:new.col_1||' Before Insert Row Level');
end;
/

create or replace trigger test_155_ari_trg
after insert on test_155 for each row
begin
  test_155_do_log(:new.col_1||' After Insert Row Level');
end;
/

create or replace trigger test_155_ai_trg
after insert on test_155
begin
  test_155_do_log(' After Insert Statement Level');
end;
/
Re: Trigger Execution [message #448808 is a reply to message #448783] Thu, 25 March 2010 04:54 Go to previous messageGo to next message
s4.ora
Messages: 63
Registered: March 2010
Member
As par my knowledge Oracle deferrs constraint checking in either of the 2 scenarios
1. constraint checking is deferred until the complete execution of the statement.
2. constraint checking is deferred until the complete execution of the transaction.

consider the following example

drop table deferr_test1;
create table deferr_test1
(id number(10),
 name varchar2(100),
 constraint chk_id check(id in (10,20,30,40,50)) INITIALLY DEFERRED DEFERRABLE);

drop table deferr_test2;
create table deferr_test2
(n1 number(10),
 n2 number(10));

SQL> insert into deferr_test1(id, name) values(10, 'A');

1 row created.

SQL> insert into deferr_test1(id, name) values(60, 'B');

1 row created.

SQL> insert into deferr_test1(id, name) values(10, 'C');

1 row created.

SQL> insert into deferr_test1(id, name) values(90, 'D');

1 row created.

SQL> insert into deferr_test2(n1, n2) values(1,2);

1 row created.

SQL> insert into deferr_test2(n1, n2) values(3,4);

1 row created.

SQL> insert into deferr_test2(n1, n2) values(5,6);

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (SUVRO_TEST.CHK_ID) violated


In this case the constraint checking was deferred until the complete execution of the transaction, as i issued a commit the transaction ended and Oracle checked the constraint and found a violation and came up with the error message.

Now consider this example

drop table deferr_test2;
create table deferr_test2
(n1 number(10),
 n2 number(10),
 constraint chk_n1 check(n1 in (1,2,3)));
 
SQL> insert into deferr_test2(n1, n2) values(1,2);

1 row created.

SQL> insert into deferr_test2(n1, n2) values(3,4);

1 row created.

SQL> insert into deferr_test2(n1, n2) values(5,6);
insert into deferr_test2(n1, n2) values(5,6)
*
ERROR at line 1:
ORA-02290: check constraint (SUVRO_TEST.CHK_N1) violated

SQL> select * from deferr_test2;

        N1         N2
---------- ----------
         1          2
         3          4



In this case the constraint checking was deferred until the complete execution of the statement, Oracle checked the constraint and found a violation and came up with the error message.

In both the cases the constraint checking was deferred until the complete execution of the Statement or Transaction that is what is meant by Step 3,but my question is not about deferred integrity constraint checking, that i know, i want to know what is going on in step 2b, as it is saying

Quote:
and perform integrity constraint checking.


what constraint are been checked there.

Re: Trigger Execution [message #449327 is a reply to message #448808] Mon, 29 March 2010 03:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Oracle does not defer constraint checking at all unless the constraint is Deferred, in which case it is validated on Commit.

Specifically, your statement 'In this case the constraint checking was deferred until the complete execution of the statement' is true only in the case that the statement in question affects a single row of the table.

Take my previous test case, and run this code:
alter table test_155 drop constraint test_155_ckd;

alter table test_155 add constraint test_155_ck check (col_1 >0);
/

insert into test_155 (select 5-level from dual connect by level <= 10);


From the log entries generated, you will see that Oracle only inserts 5 rows before raising the error - if constraint validation were deferred until the statement was complete then you'd expect to see log entries for all 10 rows in the log table.

So, Step 2b in your example is exactly where the normal constraint checking takes place if triggers are present on the table.


Re: Trigger Execution [message #449392 is a reply to message #449327] Mon, 29 March 2010 06:36 Go to previous messageGo to next message
s4.ora
Messages: 63
Registered: March 2010
Member
I have tried the following 2 examples, but i am very much confused with the outcome..

SQL> create table parent_tab
  2  (id    number(10),
  3   name  varchar2(50),
  4   constraint pk_parent_tab primary key(id));

Table created.

SQL> insert into parent_tab(id, name) values(10, 'A');

1 row created.

SQL> insert into parent_tab(id, name) values(20, 'B');

1 row created.

SQL> insert into parent_tab(id, name) values(30, 'C');

1 row created.

SQL> insert into parent_tab(id, name) values(40, 'D');

1 row created.

SQL> create table child_tab
  2  (id    number(10),
  3   detl1 varchar2(50),
  4   detl2 varchar2(50),
  5   detl3 varchar2(50),
  6   constraint fk_child_tab foreign key(id) references parent_tab(id),
  7   constraint chk_child_id check(id <=100));

Table created.

SQL> insert into child_tab(id, detl1, detl2, detl3) values(10, 'Field1', 'Field2', 'Field3');

1 row created.

SQL> insert into child_tab(id, detl1, detl2, detl3) values(10, 'Field4', 'Field5', 'Field6');

1 row created.

SQL> insert into child_tab(id, detl1, detl2, detl3) values(10, 'Field7', 'Field8', 'Field9');

1 row created.

SQL> insert into child_tab(id, detl1, detl2, detl3) values(10, 'Field10', 'Field11', 'Field12');

1 row created.

SQL> insert into child_tab(id, detl1, detl2, detl3) values(10, 'Field13', 'Field14', 'Field15');

1 row created.

SQL> insert into child_tab(id, detl1, detl2, detl3) values(20, 'Field1', 'Field2', 'Field3');

1 row created.

SQL> insert into child_tab(id, detl1, detl2, detl3) values(20, 'Field4', 'Field5', 'Field6');

1 row created.

SQL> insert into child_tab(id, detl1, detl2, detl3) values(20, 'Field7', 'Field8', 'Field9');

1 row created.

SQL> insert into child_tab(id, detl1, detl2, detl3) values(20, 'Field10', 'Field11', 'Field12');

1 row created.

SQL> insert into child_tab(id, detl1, detl2, detl3) values(20, 'Field13', 'Field14', 'Field15');

1 row created.

SQL> create or replace trigger trig_child_tab_1
  2  before update on child_tab
  3  for each row
  4  begin
  5   dbms_output.put_line('Child tab BEFORE upd trig executed......');
  6  end;
  7  /

Trigger created.

SQL> create or replace trigger trig_child_tab_2
  2  after update on child_tab
  3  for each row
  4  begin
  5   dbms_output.put_line('Child tab AFTER upd trig executed......');
  6  end;
  7  /

Trigger created.

SQL> update child_tab
  2  set id=99;
Child tab BEFORE upd trig executed......
Child tab AFTER upd trig executed......
Child tab BEFORE upd trig executed......
Child tab AFTER upd trig executed......
Child tab BEFORE upd trig executed......
Child tab AFTER upd trig executed......
Child tab BEFORE upd trig executed......
Child tab AFTER upd trig executed......
Child tab BEFORE upd trig executed......
Child tab AFTER upd trig executed......
Child tab BEFORE upd trig executed......
Child tab AFTER upd trig executed......
Child tab BEFORE upd trig executed......
Child tab AFTER upd trig executed......
Child tab BEFORE upd trig executed......
Child tab AFTER upd trig executed......
Child tab BEFORE upd trig executed......
Child tab AFTER upd trig executed......
Child tab BEFORE upd trig executed......
Child tab AFTER upd trig executed......
update child_tab
*
ERROR at line 1:
ORA-02291: integrity constraint (SUVRO_TEST.FK_CHILD_TAB) violated - parent key
not found



Here as par my understanding, the constraint checking is done after all the rows level trigger successfully executed..
Now, check out this


SQL> create table child_tab
  2  (id    number(10),
  3   detl1 varchar2(50),
  4   detl2 varchar2(50),
  5   detl3 varchar2(50),
  6   constraint chk_child_id_mod check(id <=100));

Table created.

SQL> insert into child_tab(id, detl1, detl2, detl3) values(10, 'Field1', 'Field2', 'Field3');

1 row created.

SQL> insert into child_tab(id, detl1, detl2, detl3) values(10, 'Field4', 'Field5', 'Field6');

1 row created.

SQL> insert into child_tab(id, detl1, detl2, detl3) values(10, 'Field7', 'Field8', 'Field9');

1 row created.

SQL> insert into child_tab(id, detl1, detl2, detl3) values(10, 'Field10', 'Field11', 'Field12');

1 row created.

SQL> insert into child_tab(id, detl1, detl2, detl3) values(10, 'Field13', 'Field14', 'Field15');

1 row created.

SQL> insert into child_tab(id, detl1, detl2, detl3) values(20, 'Field1', 'Field2', 'Field3');

1 row created.

SQL> insert into child_tab(id, detl1, detl2, detl3) values(20, 'Field4', 'Field5', 'Field6');

1 row created.

SQL> insert into child_tab(id, detl1, detl2, detl3) values(20, 'Field7', 'Field8', 'Field9');

1 row created.

SQL> insert into child_tab(id, detl1, detl2, detl3) values(20, 'Field10', 'Field11', 'Field12');

1 row created.

SQL> insert into child_tab(id, detl1, detl2, detl3) values(20, 'Field13', 'Field14', 'Field15');

1 row created.

SQL> create or replace trigger trig_child_tab_1
  2  before update on child_tab
  3  for each row
  4  begin
  5   dbms_output.put_line('Child tab BEFORE upd trig executed......');
  6  end;
  7  /

Trigger created.

SQL> 
SQL> create or replace trigger trig_child_tab_2
  2  after update on child_tab
  3  for each row
  4  begin
  5   dbms_output.put_line('Child tab AFTER upd trig executed......');
  6  end;
  7  /

Trigger created.

SQL> update child_tab
  2  set id=120;
Child tab BEFORE upd trig executed......
update child_tab
*
ERROR at line 1:
ORA-02290: check constraint (SUVRO_TEST.CHK_CHILD_ID_MOD) violated



So, here the execution is stopped, due to a constraint violation, but in my previous example there was a constraint violation too for all
records and the execution did stopped, but after the execution of all the Row Level Triggers.

If in both the examples there was a constraint violation for all the records, then why not all the Row Level Triggers executed in the 2nd case as it was
with the 1st case?

This is what the Documentation Library has to say about the Mechanisms of Constraint Checking

[Extract from Oracle Database Concepts 10g Release 2 (10.2) --- Data Integrity]

To know what types of actions are permitted when constraints are present, it is useful to understand when Oracle actually performs the checking of constraints. Assume the following:

1.The emp table has been defined as in Figure 21-7.

2.The self-referential constraint makes the entries in the mgr column dependent on the values of the empno column. For simplicity, the rest of this discussion addresses only the empno and mgr columns of the emp table.

Consider the insertion of the first row into the emp table. No rows currently exist, so how can a row be entered if the value in the mgr column cannot reference any existing value in the empno column? Three possibilities for doing this are:

1.A null can be entered for the mgr column of the first row, assuming that the mgr column does not have a NOT NULL constraint defined on it. Because nulls are allowed in foreign keys, this row is inserted successfully into the table.

2.The same value can be entered in both the empno and mgr columns. This case reveals that Oracle performs its constraint checking after the statement has been completely run. To allow a row to be entered with the same values in the parent key and the foreign key, Oracle must first run the statement (that is, insert the new row) and then check to see if any row in the table has an empno that corresponds to the new row's mgr.

3.A multiple row INSERT statement, such as an INSERT statement with nested SELECT statement, can insert rows that reference one another. For example, the first row might have empno as 200 and mgr as 300, while the second row might have empno as 300 and mgr as 200.

This case also shows that constraint checking is deferred until the complete execution of the statement. All rows are inserted first, then all rows are checked for constraint violations. You can also defer the checking of constraints until the end of the transaction

Now, in reference to steps i provided in my Original Post about the trigger execution model
step 3 which says

Quote:
3.Complete deferred integrity constraint checking


will be executed if i issue a commit? what if i don't issue a commit, will step 3 be ommited? say i am executing 3 statements in a transaction, so if triggers for the statements are defined all the 3 statements will go through the "Trigger Execution Model" and hence for all the 3 statements Step 3 will be performed?

Can you explain the "Trigger Execution Model and constraint checking", i am really confused in understanding it, or if you can provide any link apart from Documentation Library. It will be of great help.




[Updated on: Tue, 30 March 2010 04:45]

Report message to a moderator

Re: Trigger Execution [message #449542 is a reply to message #449327] Tue, 30 March 2010 06:34 Go to previous messageGo to next message
s4.ora
Messages: 63
Registered: March 2010
Member
Hi...

i am enclosing some exmaples...there is something happning that i done understand..

SQL> create table emp
  2  (e_id number(10),
  3   name varchar2(10),
  4   constraint pk_emp primary key(e_id));

Table created.

SQL> insert into emp(e_id, name) values(10, 'A');

1 row created.

SQL> insert into emp(e_id, name) values(20, 'B');

1 row created.

SQL> create or replace trigger trig_emp_1
  2  before insert or update on emp
  3  for each row
  4  begin
  5   dbms_output.put_line('Before Row Executed.... OLD :'||:old.e_id||' NEW : '||:new.e_id);
  6  end;
  7  /

Trigger created.

SQL> create or replace trigger trig_emp_2
  2  after insert or update on emp
  3  for each row
  4  begin
  5   dbms_output.put_line('After Row Executed....OLD :'||:old.e_id||' NEW : '||:new.e_id);
  6  end;
  7  /

Trigger created.

SQL> set serveroutput on
SQL> update emp
  2  set e_id=100;
Before Row Executed.... OLD :10 NEW : 100
After Row Executed....OLD :10 NEW : 100
Before Row Executed.... OLD :20 NEW : 100
After Row Executed....OLD :20 NEW : 100
update emp
*
ERROR at line 1:
ORA-00001: unique constraint (SUVRO_TEST.PK_EMP) violated



Now, here goes my question

as par my understanding the following sequence is followed for row level triggers.

1. before row trigger executes.
2. constraints are checked.
3. after row statement executes.

at any point if any 3 of the steps generates an error the statement will be rolled back..so if a constraint is violated in step 2
then the after row trigger will not execute, in the example the first row is updated to 100, at this pint the constraint is not
violated so the after trigger executes and the data must me

100
20

but when the 2nd row is getting updated there should be constraint violation and step 2
should come up with an error and the after row trigger should not execute because again 100 is coming and the
data must be

100
100

and this would violate the PK.

...but that is not happning.. WHY?

can you give me an explanation?
Re: Trigger Execution [message #449646 is a reply to message #449542] Wed, 31 March 2010 03:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It happens because CHECK constraints are not the same as FOREIGN KEY constraints.

If you look in the Concepts manual, t has an explanation of how the different types work.

FK constraints get checked at a different point in the process to CHECK constraints - CHECK constraints are validated on a row by row basis, whereas FK constraints are checked at the end of the statement - if you consider the case of a Pigs Ear FK constraint where a FK points back to the PK of the same table, that's the only way it can happen.
Re: Trigger Execution [message #449844 is a reply to message #449646] Thu, 01 April 2010 06:43 Go to previous messageGo to next message
s4.ora
Messages: 63
Registered: March 2010
Member
Thank you for the explanation, I have gone through the Concepts manual of Documentation Library like you said, now i have come up with an understanding which goes as follows

1. Referencial Integrity constraint checking is deferred until the complete execution of the statement.
2. Check Conatraint is checked on a row by row basis.

so, if i go through the "Trigger Execution Model" [http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/triggers.htm#sthref3288] then i can say that in Step 2b, the constraint checking in row by row basis is performed,and in step 3 the integrity constraint checking which was deferred until the complete execution of the statement i.e. Referencial Integrity checking is performed.

But what about PK and UK. Here I provide some examples which uses the Primary Key and Unique Key.

SQL> drop table emp;

Table dropped.

SQL> create table emp
  2  (e_id number(10),
  3   name varchar2(10),
  4   constraint pk_emp primary key(e_id));

Table created.

SQL> 
SQL> insert into emp(e_id, name) values(10, 'A');

1 row created.

SQL> insert into emp(e_id, name) values(20, 'B');

1 row created.

SQL> insert into emp(e_id, name) values(30, 'C');

1 row created.

SQL> create or replace trigger trig_emp_1
  2  before insert or update on emp
  3  for each row
  4  begin
  5   dbms_output.put_line('Before Row Executed.... OLD :'||:old.e_id||' NEW : '||:new.e_id);
  6  end;
  7  /

Trigger created.

SQL> 
SQL> create or replace trigger trig_emp_2
  2  after insert or update on emp
  3   for each row
  4  begin
  5   dbms_output.put_line('After Row Executed....OLD :'||:old.e_id||' NEW : '||:new.e_id);
  6  end;
  7  /

Trigger created.

SQL> 
SQL> 
SQL> set serveroutput on
SQL> update emp
  2  set e_id=100;
Before Row Executed.... OLD :10 NEW : 100
After Row Executed....OLD :10 NEW : 100
Before Row Executed.... OLD :20 NEW : 100
After Row Executed....OLD :20 NEW : 100
Before Row Executed.... OLD :30 NEW : 100
update emp
*
ERROR at line 1:
ORA-00001: unique constraint (SUVRO_TEST.PK_EMP) violated

SQL> delete emp;

3 rows deleted.

SQL> commit;

Commit complete.

SQL> 
SQL> 
SQL> insert into emp(e_id, name)
  2  select 10, 'A' from dual
  3  union
  4  select 10, 'B' from dual
  5  union
  6  select 10, 'C' from dual;
Before Row Executed.... OLD : NEW : 10
After Row Executed....OLD : NEW : 10
Before Row Executed.... OLD : NEW : 10
insert into emp(e_id, name)
*
ERROR at line 1:
ORA-00001: unique constraint (SUVRO_TEST.PK_EMP) violated



While the 2nd row is getting updated to 100, the first row has already been updated to 100, so there should be a constraint violation and the after row trigger should not execute, instead the trigger is executing and while the 3rd row is getting updated to 100 then the constraint is geting violated where as that should happen while the 2nd row was updated.....WHY?

If if refer step 2b and 2c of "Trigger Execution Model" the constraint checking is done in 2b and then the after row trigger is executed in 2c if constraint checking succeds.. but here while the row 2 was updated there is a constraint violation as 100 is already been updated in row 1, so WHY does the after row trigger executes for row 2 ?

But the insert statement is working as expected...

the similar situation occures for Unique Key also


SQL> drop table emp;

Table dropped.

SQL> create table emp
  2  (e_id number(10),
  3   name varchar2(10),
  4   constraint uk_emp unique(e_id));

Table created.

SQL> insert into emp(e_id, name) values(10, 'A');

1 row created.

SQL> insert into emp(e_id, name) values(20, 'B');

1 row created.

SQL> insert into emp(e_id, name) values(30, 'C');

1 row created.

SQL> create or replace trigger trig_emp_1
  2  before insert or update on emp
  3  for each row
  4  begin
  5   dbms_output.put_line('Before Row Executed.... OLD :'||:old.e_id||' NEW : '||:new.e_id);
  6  end;
  7  /

Trigger created.

SQL> create or replace trigger trig_emp_2
  2  after insert or update on emp
  3  for each row
  4  begin
  5   dbms_output.put_line('After Row Executed....OLD :'||:old.e_id||' NEW : '||:new.e_id);
  6  end;
  7  /

Trigger created.

SQL> set serveroutput on
SQL> update emp
  2  set e_id=100;
Before Row Executed.... OLD :10 NEW : 100
After Row Executed....OLD :10 NEW : 100
Before Row Executed.... OLD :20 NEW : 100
After Row Executed....OLD :20 NEW : 100
Before Row Executed.... OLD :30 NEW : 100
update emp
*
ERROR at line 1:
ORA-00001: unique constraint (SUVRO_TEST.UK_EMP) violated

SQL> insert into emp(e_id, name)
  2  select 10, 'A' from dual
  3  union
  4  select 10, 'B' from dual
  5  union
  6  select 10, 'C' from dual;
Before Row Executed.... OLD : NEW : 10
After Row Executed....OLD : NEW : 10
Before Row Executed.... OLD : NEW : 10
insert into emp(e_id, name)
*
ERROR at line 1:
ORA-00001: unique constraint (SUVRO_TEST.UK_EMP) violated



[Updated on: Thu, 01 April 2010 07:04]

Report message to a moderator

Re: Trigger Execution [message #449848 is a reply to message #448777] Thu, 01 April 2010 07:17 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
I would suggest it's because update can cause temporary clashes that go away once the statement is completed and insert can't
SQL> create table emp
  2  (e_id number(10),
  3  name varchar2(10),
  4  constraint uk_emp unique(e_id));

Table created.

SQL> 
SQL> insert into emp(e_id, name) values(10, 'A');

1 row created.

SQL> insert into emp(e_id, name) values(20, 'B');

1 row created.

SQL> insert into emp(e_id, name) values(30, 'C');

1 row created.

SQL> commit;

Commit complete.

SQL> update emp set e_id = e_id + 10;

3 rows updated.

SQL> commit;

Commit complete.

SQL> 


If that update does the rows in ascending order of id then you'll get a clash as soon as the first row is updated (as id 10 becomes 20) but once all the rows are updated the clash goes away. So for updates the key has to be checked after the statement has completed.
You can't do anything like that with an insert so it can check the key on a row by row basis.
Re: Trigger Execution [message #450104 is a reply to message #449848] Sun, 04 April 2010 23:27 Go to previous messageGo to next message
s4.ora
Messages: 63
Registered: March 2010
Member
Quote:

but once all the rows are updated the clash goes away. So for updates the key has to be checked after the statement has completed.


in my example of Primary Key the statement was not completed when the error was raised, instead it stopped while the third row was getting updated


[Updated on: Sun, 04 April 2010 23:28]

Report message to a moderator

Re: Trigger Execution [message #450146 is a reply to message #448777] Mon, 05 April 2010 05:24 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
So it checks it while updating the final row.
Does it really matter?
Re: Trigger Execution [message #450252 is a reply to message #450146] Tue, 06 April 2010 00:45 Go to previous messageGo to next message
s4.ora
Messages: 63
Registered: March 2010
Member
Well, i think it is checking it while updating the 3rd row, not the final row.

SQL> drop table emp;

Table dropped.

SQL> create table emp
  2  (e_id number(10),
  3   name varchar2(10),
  4   constraint pk_emp primary key(e_id));

Table created.

SQL> insert into emp(e_id, name) values(10, 'A');

1 row created.

SQL> insert into emp(e_id, name) values(20, 'B');

1 row created.

SQL> insert into emp(e_id, name) values(30, 'C');

1 row created.

SQL> insert into emp(e_id, name) values(40, 'D');

1 row created.

SQL> insert into emp(e_id, name) values(50, 'E');

1 row created.

SQL> create or replace trigger trig_emp_1
  2  before insert or update on emp
  3  for each row
  4  begin
  5   dbms_output.put_line('Before Row Executed.... OLD :'||:old.e_id||' NEW : '||:new.e_id);
  6  end;
  7  /

Trigger created.

SQL> create or replace trigger trig_emp_2
  2  after insert or update on emp
  3   for each row
  4  begin
  5   dbms_output.put_line('After Row Executed....OLD :'||:old.e_id||' NEW : '||:new.e_id);
  6  end;
  7  /

Trigger created.

SQL> set serveroutput on
SQL> update emp
  2  set e_id=100;
Before Row Executed.... OLD :10 NEW : 100
After Row Executed....OLD :10 NEW : 100
Before Row Executed.... OLD :20 NEW : 100
After Row Executed....OLD :20 NEW : 100
Before Row Executed.... OLD :30 NEW : 100
update emp
*
ERROR at line 1:
ORA-00001: unique constraint (SUVRO_TEST.PK_EMP) violated


Re: Trigger Execution [message #450277 is a reply to message #450252] Tue, 06 April 2010 03:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This is because there are differences in the way that primary and unique constraints are enforced if they have unique indexes supporting them as opposed to if they have normal indexes supporting them.

If you run this version of my script, which creates a normal index and uses it to enforce the primary key, you will see that the constraint violation occurs at the end of the statement, as expected:
drop table test_155;
drop table test_155_log;
drop sequence test_155_seq;

create table test_155 (col_1  number);

create table test_155_log (id  number
                          ,log_col   varchar2(1000));
                          
create sequence test_155_seq;

create index test_155_norm on test_155(col_1);

alter table test_155 add constraint test_155_pk primary key (col_1) using index test_155_norm;

create or replace procedure test_155_do_log (p_text  in  varchar2) as
  pragma autonomous_transaction;
begin
  insert into test_155_log(id,log_col) values (test_155_seq.nextval,p_text);
  commit;
end;
/

create or replace trigger test_155_bi_trg
before insert or update on test_155
begin
  if inserting then
    test_155_do_log(' Before Insert Statement Level');
  elsif updating then
    test_155_do_log(' Before Update Statement Level');  
  end if;
end;
/

create or replace trigger test_155_bri_trg
before insert or update on test_155 for each row
begin
  if inserting then
    test_155_do_log(:new.col_1||' Before Insert Row Level');
  elsif updating then
    test_155_do_log(:new.col_1||' Before Update Row Level');  
  end if;
end;
/

create or replace trigger test_155_ari_trg
after insert or update on test_155 for each row
begin
  if inserting then
    test_155_do_log(:new.col_1||' After Insert Row Level');
  elsif updating then
    test_155_do_log(:new.col_1||' After Update Row Level');  
  end if;
end;
/

create or replace trigger test_155_ai_trg
after insert or update on test_155
begin
  if inserting then
    test_155_do_log(' After Insert Statement Level');
  elsif updating then
    test_155_do_log(' After Update Statement Level');  
  end if;
end;
/

insert into test_155 values (1);
insert into test_155 values (2);
insert into test_155 values (3);
insert into test_155 values (4);
insert into test_155 values (5);

update test_155 set col_1 = 7;

select * from test_155_log;


Now, why unique indexes only error on the 2nd duplicate, rather than the first, I don't know.
Re: Trigger Execution [message #450283 is a reply to message #448777] Tue, 06 April 2010 04:00 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
Looks suspiciously like an optimization based on the assumption that two rows clashing can be transitory, but 3 rows can't.

If I run your script above and then the following:
SQL> drop index test_155_norm;
 
Index dropped
 
SQL> create unique index test_155_unq on test_155(col_1);
 
Index created
 
SQL> delete from test_155_log;
 
11 rows deleted
 
SQL> commit;
 
Commit complete
 
SQL> update test_155 set col_1 = (case when col_1 = 1 then 2 else col_1 end);
 
update test_155 set col_1 = (case when col_1 = 1 then 2 else col_1 end)
 
ORA-00001: unique constraint (LIVE.TEST_155_UNQ) violated
 
SQL> select * from test_155_log order by id;
 
        ID LOG_COL
---------- --------------------------------------------------------------------------------
        65  Before Update Statement Level
        66 2 Before Update Row Level
        67 2 After Update Row Level
        68 2 Before Update Row Level
        69 2 After Update Row Level
        70 3 Before Update Row Level
        71 3 After Update Row Level
        72 4 Before Update Row Level
        73 4 After Update Row Level
        74 5 Before Update Row Level
        75 5 After Update Row Level
 
11 rows selected
 
SQL> delete from test_155_log;
 
11 rows deleted
 
SQL> commit;
 
Commit complete
 
SQL> update test_155 set col_1 = 7;
 
update test_155 set col_1 = 7
 
ORA-00001: unique constraint (LIVE.TEST_155_UNQ) violated
 
SQL> select * from test_155_log order by id;
 
        ID LOG_COL
---------- --------------------------------------------------------------------------------
        76  Before Update Statement Level
        77 7 Before Update Row Level
        78 7 After Update Row Level
        79 7 Before Update Row Level
        80 7 After Update Row Level
        81 7 Before Update Row Level
 
6 rows selected
 
SQL> 


You'll see that when I used the case statement to ensure only 2 records ended up with the same value it updated all of them before throwing the error. When you do all the rows it stops after the third.
Re: Trigger Execution [message #450284 is a reply to message #450283] Tue, 06 April 2010 04:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Actually, that makes sense - an update statement can only update each row once (otherwise you'd get all sorts of problems about which order the updates happened in).

So, you can temporarily have 2 rows with the same value in an Update, but if you ever have three, then that means that two rows have been updated to the same value, and aren't going to be updated again, and you can guarantee that you've got a constraint violation.
Re: Trigger Execution [message #450347 is a reply to message #448777] Tue, 06 April 2010 09:59 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
The real question here is why the pk doesn't do the optimisation if you create it the way you did.
Have to say I'm not sure why all this matters though. Properly written code shouldn't care at which point the constraint violation is thrown. You might need to care about defered constraints, but the rest of it shouldn't matter.
Re: Trigger Execution [message #450415 is a reply to message #450347] Wed, 07 April 2010 01:45 Go to previous messageGo to next message
s4.ora
Messages: 63
Registered: March 2010
Member
As par my knowledge Oracle deferrs constraint checking in either of the 2 scenarios
1. constraint checking is deferred until the complete execution of the statement.
2. constraint checking is deferred until the complete execution of the transaction.

which one is applicable in "Trigger Execution Model"? What i understand is that point 2 will not be application because at that time the transaction may not be over, there may be other statements executing after the current statement.

Can you provive me with any link of the Constraint Checking Process, i have gone through the Documentation Library, the description of the Declerative Constraints are good, but i need to know more about the Checking Process which is followed by Oracle, it'll be of great help.
Re: Trigger Execution [message #450426 is a reply to message #450415] Wed, 07 April 2010 03:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Ok, you're getting confused about the use of the word 'deferred', which is understandable, as it is used in two different meanings.

Some constraints, such as Not Null and Check constraints are evaluated immediately.

Some constraints, such as Unique and PK, and some FK constraints are checked when a statements has finished execution - oracle defers checking them until the end of the statement as that is the first point at which they can be checked - see above for descriptions of why.
This is the first meaning of 'defer'

The second meaning of 'defer' is that constraints can be defined as 'DEFERRABLE'.
A Deferrable constraint can either be set as IMMEDIATE, in which case it processes normally, or it can be set as DEFERRED.
If it is DEFERRED, then it is only evaluated at the end of the current transaction.

I have no idea what your question 'which one is applicable in "Trigger Execution Model"?' means. The operation of constraints is independent of any triggers on the table.

You can read more about constraints in the Concepts manual

[changed 'transaction' to statement - thanks CM]

[Updated on: Wed, 07 April 2010 04:04]

Report message to a moderator

Re: Trigger Execution [message #450433 is a reply to message #450426] Wed, 07 April 2010 03:54 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
JRowbottom wrote on Wed, 07 April 2010 09:04
Ok, Some constraints, such as Unique and PK, and some FK constraints are checked when a statements has finished execution - oracle defers checking them until the end of the transaction as that is the first point at which they can be checked - see above for descriptions of why.
This is the first meaning of 'defer'

That should be:
oracle defers checking them until it has finished processing the current dml statement as that is the first point at which they can be checked.

Otherwise you're talking about the 2nd (official) definition.
Re: Trigger Execution [message #450435 is a reply to message #450433] Wed, 07 April 2010 04:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Good catch - fixed it.
Re: Trigger Execution [message #450455 is a reply to message #450435] Wed, 07 April 2010 06:57 Go to previous messageGo to next message
s4.ora
Messages: 63
Registered: March 2010
Member
Quote:
I have no idea what your question 'which one is applicable in "Trigger Execution Model"?' means.


When i said 'which one is applicable in "Trigger Execution Model"?' meaning that in the trigger execution model
The Execution Model for Triggers and Integrity Constraint Checking there is a step 3, which says 'Complete deferred integrity constraint checking.', so what i understood is that the integrity constraint which are defered until the complete execution of the statement is checked here as at this point the statement has completed its execution. is that so?

Now, as per your words

Quote:
Not Null and Check constraints are evaluated immediately


and

Quote:
Unique and PK, and some FK constraints are checked when a statements has finished execution


i get to understand that NOT NULL and CHECK constraints are checked in step 2b and UNIQUE and PK and FK constraints are checked in step 3. But that happnes when PK or UK is enforced with normal index, in case of Unique index it is not happning, i would like to provide some examples

1. PK enforced with normal index

SQL> drop table tab_pk_test;

Table dropped.

SQL> create table tab_pk_test
  2  (id number(10),
  3   name varchar2(10));

Table created.

SQL> create index idx1_pk_test on tab_pk_test(id);

Index created.

SQL>  
SQL> alter table tab_pk_test add constraint pk_tab_pk_test primary key(id) using index idx1_pk_test;

Table altered.

SQL> 
SQL> insert into tab_pk_test(id, name) values(10, 'A');

1 row created.

SQL> insert into tab_pk_test(id, name) values(20, 'B');

1 row created.

SQL> insert into tab_pk_test(id, name) values(30, 'C');

1 row created.

SQL> insert into tab_pk_test(id, name) values(40, 'D');

1 row created.

SQL> commit;

Commit complete.

SQL> create or replace trigger trig_tab_pk_test_1
  2  before insert or update on tab_pk_test
  3  for each row
  4  begin
  5   dbms_output.put_line('Before Row Executed.... OLD :'||:old.id||' NEW : '||:new.id);
  6  end;
  7  /

Trigger created.

SQL> 
SQL> create or replace trigger trig_tab_pk_test_2
  2  after insert or update on tab_pk_test
  3   for each row
  4  begin
  5   dbms_output.put_line('After Row Executed....OLD :'||:old.id||' NEW : '||:new.id);
  6  end;
  7  /

Trigger created.

SQL> set serverout on 
SQL> update tab_pk_test
  2  set id=100;
Before Row Executed.... OLD :10 NEW : 100
After Row Executed....OLD :10 NEW : 100
Before Row Executed.... OLD :20 NEW : 100
After Row Executed....OLD :20 NEW : 100
Before Row Executed.... OLD :30 NEW : 100
After Row Executed....OLD :30 NEW : 100
Before Row Executed.... OLD :40 NEW : 100
After Row Executed....OLD :40 NEW : 100
update tab_pk_test
*
ERROR at line 1:
ORA-00001: unique constraint (SUVRO_TEST.PK_TAB_PK_TEST) violated



So, here PK constraints is checked when a statements has finished execution, but in case of Unique index it is somewhat diferent.

2. PK enforced with unique index

SQL> drop table tab_pk_test;

Table dropped.

SQL> create table tab_pk_test
  2  (id number(10),
  3   name varchar2(10),
  4   constraint pk_tab_pk_test primary key(id));

Table created.

SQL> insert into tab_pk_test(id, name) values(10, 'A');

1 row created.

SQL> insert into tab_pk_test(id, name) values(20, 'B');

1 row created.

SQL> insert into tab_pk_test(id, name) values(30, 'C');

1 row created.

SQL> insert into tab_pk_test(id, name) values(40, 'D');

1 row created.

SQL> commit;

Commit complete.

SQL> create or replace trigger trig_tab_pk_test_1
  2  before insert or update on tab_pk_test
  3  for each row
  4  begin
  5   dbms_output.put_line('Before Row Executed.... OLD :'||:old.id||' NEW : '||:new.id);
  6  end;
  7  /

Trigger created.

SQL> 
SQL> create or replace trigger trig_tab_pk_test_2
  2  after insert or update on tab_pk_test
  3   for each row
  4  begin
  5   dbms_output.put_line('After Row Executed....OLD :'||:old.id||' NEW : '||:new.id);
  6  end;
  7  /

Trigger created.

SQL> set serverout on 
SQL> update tab_pk_test
  2  set id=100;
Before Row Executed.... OLD :10 NEW : 100
After Row Executed....OLD :10 NEW : 100
Before Row Executed.... OLD :20 NEW : 100
After Row Executed....OLD :20 NEW : 100
Before Row Executed.... OLD :30 NEW : 100
update tab_pk_test
*
ERROR at line 1:
ORA-00001: unique constraint (SUVRO_TEST.PK_TAB_PK_TEST) violated



Now, what cookiemonster wrote

Quote:

That should be:
oracle defers checking them until it has finished processing the current dml statement as that is the first point at which they can be checked.


is what is done in the first exmaple, what about the 2nd example?

Quote:
So, you can temporarily have 2 rows with the same value in an Update, but if you ever have three, then that means that two rows have been updated to the same value, and aren't going to be updated again, and you can guarantee that you've got a constraint violation.

can you elaborate more on this?


[Updated on: Wed, 07 April 2010 07:00]

Report message to a moderator

Re: Trigger Execution [message #450458 is a reply to message #450455] Wed, 07 April 2010 07:12 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
s4.ora wrote on Wed, 07 April 2010 12:57

Quote:

That should be:
oracle defers checking them until it has finished processing the current dml statement as that is the first point at which they can be checked.

is what is done in the first exmaple, what about the 2nd example?

cookiemonster wrote on Tue, 06 April 2010 10:00

Looks suspiciously like an optimization based on the assumption that two rows clashing can be transitory, but 3 rows can't.

Suggest you re-read my post that I just quoted.

s4.ora wrote on Wed, 07 April 2010 12:57

Quote:
So, you can temporarily have 2 rows with the same value in an Update, but if you ever have three, then that means that two rows have been updated to the same value, and aren't going to be updated again, and you can guarantee that you've got a constraint violation.

can you elaborate more on this?


Seems obvious, especially with the exmaple from my post above. What exactly do you not understand?
Re: Trigger Execution [message #450465 is a reply to message #450455] Wed, 07 April 2010 07:37 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
Quote:
o, you can temporarily have 2 rows with the same value in an Update, but if you ever have three, then that means that two rows have been updated to the same value, and aren't going to be updated again, and you can guarantee that you've got a constraint violation.

can you elaborate more on this?


I agree with @CM - this seems pretty self explanitory.
However...

Consider this situation:
CREATE TABLE test (col_1  number);

ALTER TABLE test ADD CONSTRAINT test_uk UNIQUE (col_1);

insert into test values (1);
insert into test values (2);
insert into test values (3);
insert into test values (4);

UPDATE TEST SET col_1 = col_1 +1;


When the update runs, assume that it processes the rows in the order that they were added.
It processes the first row, and updates the value to 2. There is already another row with this value, but only one. As we have to allow for the possibility that this other row hasn't been updated yet, we don't raise an error.

It processes the second row, and updates it to 3. The duplicate value of 2 has gone, but we now have two rows with a value 3 in them.

It processes the third row, and updates it to 4. The duplicate value of 3 has gone, but we now have two rows with a value 4 in them.

It processes the fourth row, and updates it to 5. The duplicate value of 4 has gone.

We get to the end of the statement, we've got no duplicate values, and everything is fine.


Now consider this update:
UPDATE test SET col_1 = 4;


It processes the first row, and updates the value to 4. There is already another row with this value, but only one. As we have to allow for the possibility that this other row hasn't been updated yet, we don't raise an error.

It processes the second row, and updates the value to 4. There are now two other rows with this value, making three in total. The column was unique before the update, so at most one of these three has yet to be processed.
Therefore there are at least two rows that have been processed by the update, and have identical values.
We could continue processing to the end of the statement, but we now know that we are guaranteed to have a constraint violation, so we may as well save some time and I/O, and raise the exception now.

This only happens when there is a unique index on the constraint column, I would guess because there is something in the structure of a unique index that makes this check quick and easy to perform.

Re: Trigger Execution [message #450468 is a reply to message #450465] Wed, 07 April 2010 07:49 Go to previous messageGo to next message
s4.ora
Messages: 63
Registered: March 2010
Member
Thank you Sir, thanks for the explanation, this will obviously help.

As for Unique index it is perfectly understood, in case of Normal Index the checking will be deferred until the complete execution of the statement as i understand.

Thank you ones again to make things clear.

Re: Trigger Execution [message #450606 is a reply to message #450465] Thu, 08 April 2010 04:56 Go to previous messageGo to next message
s4.ora
Messages: 63
Registered: March 2010
Member
Sir,

the explanation which you provided was really good, the example you provided is understood, but i do have some question regarding the examples which i provide.

SQL> drop table TE_test;

Table dropped.

SQL> create table TE_test
  2  (id number(10) primary key);

Table created.

SQL> 
SQL> insert into TE_test(id) values(1);

1 row created.

SQL> insert into TE_test(id) values(2);

1 row created.

SQL> insert into TE_test(id) values(3);

1 row created.

SQL> insert into TE_test(id) values(4);

1 row created.

SQL> 
SQL> create or replace trigger trig_TE_test_1
  2  before update or insert on TE_test
  3  for each row
  4  begin
  5   dbms_output.put_line('Before Row OLD : '||:old.id||' NEW: '||:new.id);
  6  end;
  7  /

Trigger created.

SQL> 
SQL> create or replace trigger trig_TE_test_2
  2  after update or insert on TE_test
  3  for each row
  4  begin
  5   dbms_output.put_line('After Row OLD : '||:old.id||' NEW: '||:new.id);
  6  end;
  7  /

Trigger created.

SQL> set serveroutput on
SQL> update TE_test
  2  set id=2;
Before Row OLD : 1 NEW: 2
After Row OLD : 1 NEW: 2
Before Row OLD : 2 NEW: 2
After Row OLD : 2 NEW: 2
Before Row OLD : 3 NEW: 2
update TE_test
*
ERROR at line 1:
ORA-00001: unique constraint (SUVRO_TEST.SYS_C005541) violated


while the first row is updated to 2 , there is another row with the value 2, so at this point oracle has to allow the update because that row has not been updated yet. The second row is updated to 2, now at this point there are two rows with the value 2, now as i understand that if the update statement process any further then there will be constraint violation, so at this point the execution should stop as according to PK no 2 rows should have identical value, but instead it continues and then stopes at the third row... WHY?

Now take a look at the below example which is somewhat similar to what you provided

SQL> drop table TE_test;

Table dropped.

SQL> create table TE_test
  2  (id number(10) primary key);

Table created.

SQL> 
SQL> insert into TE_test(id) values(1);

1 row created.

SQL> insert into TE_test(id) values(2);

1 row created.

SQL> insert into TE_test(id) values(3);

1 row created.

SQL> insert into TE_test(id) values(4);

1 row created.

SQL> 
SQL> create or replace trigger trig_TE_test_1
  2  before update or insert on TE_test
  3  for each row
  4  begin
  5   dbms_output.put_line('Before Row OLD : '||:old.id||' NEW: '||:new.id);
  6  end;
  7  /

Trigger created.

SQL> 
SQL> create or replace trigger trig_TE_test_2
  2  after update or insert on TE_test
  3  for each row
  4  begin
  5   dbms_output.put_line('After Row OLD : '||:old.id||' NEW: '||:new.id);
  6  end;
  7  /

Trigger created.

SQL> set serveroutput on
SQL> update TE_test
  2  set id=4;
Before Row OLD : 1 NEW: 4
After Row OLD : 1 NEW: 4
Before Row OLD : 2 NEW: 4
update TE_test
*
ERROR at line 1:
ORA-00001: unique constraint (SUVRO_TEST.SYS_C005542) violated


while the first row is updated to 4, there is another row with the value 4, so at this point oracle has to allow the update because that row has not been updated yet. the second row gets updated to 4, now at this point there is a constraint violation as two rows have the same value, rather three.

Now my question is in both the cases after the second row gets updated there should be a constraint violation, then why not in the 1st case the execution stops after the second row gets updated like that in the 2nd case?

Quote:
Therefore there are at least two rows that have been processed by the update, and have identical values. We could continue processing to the end of the statement, but we now know that we are guaranteed to have a constraint violation, so we may as well save some time and I/O, and raise the exception now.


In both the cases as i understand the same this is happning which i quoted above.

i would like to quote again

Quote:
you can temporarily have 2 rows with the same value in an Update, but if you ever have three, then that means that two rows have been updated to the same value, and aren't going to be updated again, and you can guarantee that you've got a constraint violation.


as par my understanding while the update statement is execution oracle has to see that after each row gets updated no 2 row should have identical value, then Why Oracle has to check for the third row? as what you said from the Quote above 'two rows have been updated to the same value, and aren't going to be updated again' is what happnes in the 1st example where after the second row gets updated.

in the 1st example when the second row gets updated then step 2b is performed The Execution Model for Triggers and Integrity Constraint Checking, here at this point the row has already been updated to 2, and aren't going to be updated again. So here should be a constraint violation.

What i understand that at any point of time during the execution of the update statement if oracle finds 3 identical value then it comes up with a constraint violation error.

But Why 3? why not 2? as 2 row are already been updated and aren't going to be updated again.

Re: Trigger Execution [message #450611 is a reply to message #448777] Thu, 08 April 2010 05:23 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
Probably because if you update a column value to the value it already had (which is what happens with the 2nd row in the 1st example - it was 2 and you set it to 2) oracle doesn't bother modifying the index. And since it is the index that is used to check the constraint it doesn't notice there is a problem until you update the third row.
Re: Trigger Execution [message #450628 is a reply to message #450611] Thu, 08 April 2010 07:36 Go to previous messageGo to next message
s4.ora
Messages: 63
Registered: March 2010
Member
OK, but what about this?

SQL> drop table TE_test;

Table dropped.

SQL> create table TE_test
  2  (id number(10) primary key);

Table created.

SQL> insert into TE_test(id) values(1);

1 row created.

SQL> insert into TE_test(id) values(2);

1 row created.

SQL> insert into TE_test(id) values(3);

1 row created.

SQL> insert into TE_test(id) values(4);

1 row created.

SQL> create or replace trigger trig_TE_test_1
  2  before update or insert on TE_test
  3  for each row
  4  begin
  5  dbms_output.put_line('Before Row OLD : '||:old.id||' NEW: '||:new.id);
  6  end;
  7  /

Trigger created.

SQL> show err
No errors.
SQL> 
SQL> create or replace trigger trig_TE_test_2
  2  after update or insert on TE_test
  3  for each row
  4  begin
  5  dbms_output.put_line('After Row OLD : '||:old.id||' NEW: '||:new.id);
  6  end;
  7  /

Trigger created.

SQL> show err
No errors.

SQL> set serveroutput on
SQL> update TE_test
  2  set id=100;
Before Row OLD : 1 NEW: 100
After Row OLD : 1 NEW: 100
Before Row OLD : 2 NEW: 100
After Row OLD : 2 NEW: 100
Before Row OLD : 3 NEW: 100
update TE_test
*
ERROR at line 1:
ORA-00001: unique constraint (SUVRO_TEST.SYS_C005544) violated


Re: Trigger Execution [message #450630 is a reply to message #448777] Thu, 08 April 2010 07:40 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
What about it?
You update 1st row to 100 - no clashes.
You update 2nd row to 100 - clash, but could be transitory.
You update 3rd row to 100 - clash, can't be transitory, get error.

Re: Trigger Execution [message #450632 is a reply to message #448777] Thu, 08 April 2010 07:57 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
Actually, thinking about it, I'll revise my explanation for your two examples above.

1st exmaple

You update 1st row to 2 - clash, could be transitory
You update 2nd row to 2 (which it already was) - clash, could still be transitory as there are still only 2 rows with value 2.
You update 3rd row to 2 - clash, can't be transitory, get error.

2nd example
You update 1st row to 4 - clash, could be transitory
You update 2nd row to 4 - clash, can't be transitory as there are now three rows with this value - get error.


Re: Trigger Execution [message #450732 is a reply to message #450632] Fri, 09 April 2010 00:22 Go to previous messageGo to next message
s4.ora
Messages: 63
Registered: March 2010
Member
Quote:
You update 2nd row to 100 - clash, but could be transitory.


Quote:
You update 2nd row to 2 (which it already was) - clash, could still be transitory as there are still only 2 rows with value 2.


Why transitory? as the row has already been updated, and at that point there are 2 rows with the same value, which obviously violated PK, and moreover at that point the values would not be updated again.

Quote:
You update 2nd row to 4 - clash, can't be transitory as there are now three rows with this value - get error.


I would like to repeat the same question.

But Why 3? Why not 2? as 2 row are already been updated and aren't going to be updated again.

Can you elaborate more on that 'transitory' part.

Quote:
Probably because if you update a column value to the value it already had (which is what happens with the 2nd row in the 1st example - it was 2 and you set it to 2) oracle doesn't bother modifying the index. And since it is the index that is used to check the constraint it doesn't notice there is a problem until you update the third row.


But in case of update to 100, it did not update a column value to the value it already had, it changed 2 to 100.

I would like to refer your example

Quote:
Now consider this update:
UPDATE test SET col_1 = 4;


Sir, you gave a step by step explanation for this update statement,

Now can you explain the following statement the same way. It would be easy for me to understand, as your explanation was really good.

UPDATE test SET col_1 = 2;

[Updated on: Fri, 09 April 2010 00:37]

Report message to a moderator

Re: Trigger Execution [message #450755 is a reply to message #448777] Fri, 09 April 2010 02:00 Go to previous messageGo to next message
John Watson
Messages: 6566
Registered: January 2010
Location: Global Village
Senior Member
This topic has been fascinating, thank you to all.
@s4.ora, will it be possible for you to write it up, perhaps as a blog entry? I'm sure many members would be interested, in the experimental methods as well as the conclusions. Or write a paper for OpenWorld in San Francisco, and be world famous.
Re: Trigger Execution [message #450768 is a reply to message #448777] Fri, 09 April 2010 02:36 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
You do realise we're just guessing here since oracle doesn't appear to document this particular behavouir anywhere.

Anyway I'll quote JRowbottom from earlier:
Quote:

So, you can temporarily have 2 rows with the same value in an Update, but if you ever have three, then that means that two rows have been updated to the same value, and aren't going to be updated again, and you can guarantee that you've got a constraint violation.


Three rows with the same value is the point at which it throws the error.

So when you did:
UPDATE test SET col_1 = 100;
It errored on the third row because none of the rows started with that value.

UPDATE test SET col_1 = 2;

Errored on the third row because that was the 1st point that you had three rows the same.
Update row 1 to 2 - rows 1 and 2 now have value of 2 - 2 rows the same.
Update row 2 to 2 - rows 1 and 2 now have value of 2 - 2 rows the same.
Update row 3 to 2 - rows 1, 2 and 3 now have value of 2 - 3 rows the same - error.


This
UPDATE test SET col_1 = 4;
Errored on the second row because that was the 1st point that you had three rows the same.
Update row 1 to 4 - rows 1 and 4 now have value of 4 - 2 rows the same.
Update row 2 to 4 - rows 1, 2 and 4 now have value of 4 - 3 rows the same - error.

If you run your example and update everything to 3 it'll behave as the 4 example.
If you run your example and update everything to 1 it'll behave as the 2 example.
Re: Trigger Execution [message #450775 is a reply to message #450732] Fri, 09 April 2010 02:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
Quote:
ou update 2nd row to 2 (which it already was) - clash, could still be transitory as there are still only 2 rows with value 2.


Why transitory? as the row has already been updated, and at that point there are 2 rows with the same value, which obviously violated PK, and moreover at that point the values would not be updated again.

Quote:
ou update 2nd row to 4 - clash, can't be transitory as there are now three rows with this value - get error.


I would like to repeat the same question.

But Why 3? Why not 2? as 2 row are already been updated and aren't going to be updated again.

Can you elaborate more on that 'transitory' part.


Thinking about it, I very much doubt there is any easy way for the index to know which rows have and have not been updated.

What it can know is that in the middle of an update, there are valid ways of having 2 rows with the same value, and that this is only a problem if there are still two rows with the same value at the end of the statement.

What it can also know is that there is no way in which three rows can have the same value at any point without this still being the case at the end of the statement.



Re: Trigger Execution [message #450987 is a reply to message #450775] Mon, 12 April 2010 00:15 Go to previous message
s4.ora
Messages: 63
Registered: March 2010
Member
Well, i think i can figure it out that what is going on, here i detail my understanding... tell me if i am wrong.

PK and UK is enforced by Unique Index, so after the 2nd row gets updated then the index in also getting updated, and when at step 2b [Trigger Execution Model and Integrity Constraint Checking] constraint checking is done, Oracle is checking the index to see how many records are here for the value, if it finds two then it leaves it taking into consideration that the row may be updated as there in no information in the index that the row has been updated or not, but the moment it gets 3 then it becomes sure that at the end of the statement it will surely get a constraint violation, so at this point Oracle troughs the Constraint Violation error... Is that So?

Tell me if i have missed out anything...

[Updated on: Mon, 12 April 2010 00:19]

Report message to a moderator

Previous Topic: corellated query and inline view
Next Topic: Halp with the Query
Goto Forum:
  


Current Time: Mon Dec 05 18:56:50 CST 2016

Total time taken to generate the page: 0.32931 seconds