which will fire first ? Trigger or Constraint

Categories >> Software >> Databases >> SQL PLSQL
Suggest New Category

Question Posted / parag tyagi

24 Answers
51314 Views
i2, IBM, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

which will fire first ? Trigger or Constraint..

Answer / rajesh

if it is before insert or update trigger, trigger wil fire
first, if it is after insert or upadate constraints will
execute first.

Is This Answer Correct ?

26 Yes

2 No

which will fire first ? Trigger or Constraint..

Answer / parag tyagi

always constraint will fire first.

Is This Answer Correct ?

31 Yes

15 No

which will fire first ? Trigger or Constraint..

Answer / vamsi krishna

we can't say which is executed first among the trigger and
constraraint .
the sequence of execution is
(i) before statement level
(ii) before row level
(iii) after row level
(iv) constraint
(v) after statement level

Is This Answer Correct ?

19 Yes

8 No

which will fire first ? Trigger or Constraint..

Answer / vijay kumar s

create table T_CHK (a number check (A < 99));

CREATE TRIGGER CHKT BEFORE INSERT ON T_CHK
FOR EACH ROW
BEGIN
IF :NEW.A >100 THEN
RAISE_APPLICATION_ERROR(-20011,'VALUES SHOULD BE < 100');
END IF;
END;

SQL> INSERT INTO T_CHK VALUES(100);
INSERT INTO T_CHK VALUES(100)
*
ERROR at line 1:
ORA-02290: check constraint (SVR.SYS_C00301152) violated

ABOVE ONE CLEARLY TELLS

contraints will fire first.

Is This Answer Correct ?

14 Yes

7 No

which will fire first ? Trigger or Constraint..

Answer / radha sri seshu.kolla

I THINK TRIGGER FIRES FIRST, BCZ WHEN U WRITE A TRIGGER
LIKE 'BEFORE INSERT ON EMP' EVENT. THIS TRIGGER WILL FIRE
IMMEDEATELY BEFORE THE DATA IS ABOUT TO INSERT IN THE
TABLE.WHERE AS IN THE CASE OF CONSTRAINTS, WHEN YOU INSERT
THE DATA,THE DATA GOES FOR CONSTRAINT VALIDATION THEN THE
DATA WILL BE REJECTED(IF THE DATA VIOLATES THE CONSTRAINT
RULE). I AM NOT 100% SURE. THIS IS JUST MY IMAGINATION.

Is This Answer Correct ?

11 Yes

5 No

which will fire first ? Trigger or Constraint..

Answer / vijay kumar s

Hi all,

leave th 9th answer ............

ignore 9 th answer....

see below one

create table T_CHK (a number check (A < 99))

CREATE TRIGGER CHKT BEFORE INSERT ON T_CHK
FOR EACH ROW
BEGIN
IF :NEW.A >100 THEN
RAISE_APPLICATION_ERROR(-20011,'VALUES SHOULD BE < 100');
END IF;
END;

SQL> INSERT INTO T_CHK VALUES(101);
INSERT INTO T_CHK VALUES(101)
*
ERROR at line 1:
ORA-20011: VALUES SHOULD BE < 100
ORA-06512: at "SVR.CHKT", line 3
ORA-04088: error during execution of trigger 'SVR.CHKT'

Trigger will fire Before insert trigger only
then constraints
then after insert trigger

Is This Answer Correct ?

12 Yes

6 No

which will fire first ? Trigger or Constraint..

Answer / anilbabu makkena

It is on the based situation.
1st situation CONSTRAINT and BEFORE STATEMENT Level TRIGGER
and BEFORE ROW Level TRIGGER Created.

1.BEFORE STATEMENT Level TRIGGER Fires.
2.BEFORE ROW Level TRIGGER Fires.
3.CONSTRAINT Fires.

2nd situation CONSTRAINT and AFTER STATEMENT Level TRIGGER
and AFTER ROW Level TRIGGER Created.

1.CONSTRAINT fires.
2.AFTER ROW Level TRIGGER Fires.
3.AFTER STATEMENT Level TRIGGER Fires.

3rd situation CONSTRAINT and BEFORE STATEMENT Level TRIGGER
and BEFORE ROW Level TRIGGER and AFTER ROW Level TRIGGER and
AFTER STATEMENT Level TRIGGER Created.

1.BEFORE STATEMENT Level TRIGGER Fires.
2.BEFORE ROW Level TRIGGER Fires.
3.CONSTRAINT Fire.
4.AFTER ROW Level TRIGGER Fires.
5.AFTER STATEMENT Level TRIGGER Fires.

Is This Answer Correct ?

4 Yes

0 No

which will fire first ? Trigger or Constraint..

Answer / p.j.mohana rao

Constraint will fire first bcz these are restrict the
column value
before enter into the table these value is valid or not
that why constrint will fire first

Is This Answer Correct ?

9 Yes

6 No

which will fire first ? Trigger or Constraint..

Answer / srikanth k

The sequence of execution is:
1) before statement level
2) before row level
3) constraint
4) after row level
5) after statement level

Is This Answer Correct ?

1 Yes

0 No

which will fire first ? Trigger or Constraint..

Answer / keshavenas

The sequence of execution as follows :
1) before statement level
2) before row level
3) constraint
4) after row level
5) after statement level

Is This Answer Correct ?

1 Yes

0 No

Post New Answer