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  |
s4.ora
Messages: 71 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   |
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   |
s4.ora
Messages: 71 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   |
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   |
s4.ora
Messages: 71 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   |
s4.ora
Messages: 71 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   |
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   |
s4.ora
Messages: 71 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   |
cookiemonster
Messages: 13964 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   |
s4.ora
Messages: 71 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 #450252 is a reply to message #450146] |
Tue, 06 April 2010 00:45   |
s4.ora
Messages: 71 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   |
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   |
cookiemonster
Messages: 13964 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   |
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   |
cookiemonster
Messages: 13964 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   |
s4.ora
Messages: 71 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   |
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   |
cookiemonster
Messages: 13964 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
JRowbottom wrote on Wed, 07 April 2010 09:04Ok, 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 #450455 is a reply to message #450435] |
Wed, 07 April 2010 06:57   |
s4.ora
Messages: 71 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   |
cookiemonster
Messages: 13964 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   |
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   |
s4.ora
Messages: 71 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   |
s4.ora
Messages: 71 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   |
cookiemonster
Messages: 13964 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   |
s4.ora
Messages: 71 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   |
cookiemonster
Messages: 13964 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   |
cookiemonster
Messages: 13964 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   |
s4.ora
Messages: 71 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   |
John Watson
Messages: 8964 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   |
cookiemonster
Messages: 13964 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   |
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  |
s4.ora
Messages: 71 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
|
|
|
Goto Forum:
Current Time: Thu Feb 13 15:46:50 CST 2025
|