Write the order of precedence for validation of a column in a table ?

I. done using Database triggers. ii. done using Integarity Constraints. I & ii. Exception :

Interview Candidate
Sep 9th, 2004
2
5382

Showing Answers 1 - 2 of 2 Answers

prashant

Jul 6th, 2006

ii. done using Integarity Constraints

Srinatha Reddy
Profile Answers by Srinatha Reddy

May 21st, 2008

create table a ( b number, c number check (c >100) );

create or replace trigger t1
before insert on a
begin
dbms_output.put_line('this is before insert trigger');
end;

create or replace trigger t2
after insert on a
begin
dbms_output.put_line('this is after insert trigger');
end;

insert into a values (1,99);
/

this is before insert trigger

insert into a values (1,99)
*
ERROR at line 1:
ORA-02290: check constraint (XXINV.SYS_C00106711) violated

insert into a values (1,199);
/
this is before insert trigger
this is after insert trigger
1 row created.

So, if it is before insert trigger then the trigger will run first before the constriants in the table. if it is after insert trigger , contraints are checked first and then trigger will run.