oRACLE_TRG

constraint first...
then if update/insert succeeds, then row level triggers, then statement level triggers...

I believe a before trigger will fire before a constraint check.

if constraint or index fails, then DML fails, so there wont be any trigger...

@Netminder

:) Strange... what happens if you say

create or replace trigger tab1_before_trig
before insert on tab1
for each row
begin
  dbms_output.put_line('Before Fired');
  :new.col1='X';
end;
/

Select allOpen in new window

and

insert into tab1 values('Y')

Select allOpen in new window

I get a syntax error on: :new.col1='X';

After fixing that, no real change that I can see.  What am I supposed to see?

sorry, it should be ":="

 :new.col1:='X';

Yes, I said I corrected it but received the same output.  What was I supposed to see?

dont know :)
just wondered if it will insert or fail because of constraint...
and you said, no change, so constraint will take affect after all and it will be rejected...

I figured you were testing things on your side and wanted to see if my results mirrored yours.

I'm not sure how to interpret "so constraint will take affect after all and it will be rejected... "

The before trigger fires, then the check constraint caused the insert to fail.


I'll give you one better:  Foreign Key Constraint.

Before and After triggers fire then insert fails:

drop table tab1 purge;
create table tab1(col1 char(1) primary key);

drop table tab2 purge;
create table tab2(col1 char(1), col2 char(1),
	constraint tab1_col1_fk foreign key (col2) 
		references tab1(col1)
);


create or replace trigger tab2_before_trig
before insert on tab2
for each row
begin
	dbms_output.put_line('Before Fired');
end;
/

show errors

create or replace trigger tab2_after_trig
after insert on tab2
for each row
begin
	dbms_output.put_line('After Fired');
end;
/

show errors

insert into tab2 values('X','X');

Select allOpen in new window

@Netminder

I dont have any oracle right now, and I tried to understand what will happen when we insert a record, that is ok for constraint, and change value in trigger which is against contraint... :)

You made such confident statements.  I was hoping you had evidence to support them.

MIHIR,

I hope that by all the discussion you realize there isn't a simple answer to your question.  There are many types of triggers and many types of constraints and each combination will behave differently.

If you wish to know how a certain combination behaves, do as I did and set up a simple test case and see it working for yourself.

does it really matter ?

constraint conditions have to be met
and the trigger can't throw an error

otherwise the record is not inserted

the issue is,

constraints are ok
triggers modify inserted data
constraints are not ok anymore

at this point what happens? accept / reject?

also, if you add before and after triggers, row level / statement level triggers into action, then what?

better, do all check in constraints
if you cannot do in constraints, do it in trigger, and dont do anything against constraints in trigger

 :)

Thanks a lot expert

So i belive it's depends on what type of trigger it is.

constraints have to be met, no matter what you do in your trigger

Netminder's quick test shows that...  constraints are checked last...

that's what i'm saying, you like to repeat everybody else ?

>>constraints are checked last...

I don't believe I showed that.  My first example shows the check constraint is before an after trigger.

>>So i belive it's depends on what type of trigger it is.

It also depends on the constraint type.  I posted an example with a check and foreign key constraint.  They behave differently.