which will fire first ? Trigger or Constraint

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

Question Posted / parag tyagi

24 Answers
44583 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 / dheerendra pawaiya

it totaly depends on the condition.
after check the condition we can say which will fire first
constartint or trigger.

Is This Answer Correct ?

3 Yes

3 No

which will fire first ? Trigger or Constraint..

Answer / vikas

Threre is a transaction flow which determine which will
execute first-

Identity
Null
Data type
Instead of trigger
Primary key
check constraint
foreigh key
DML Statement
After trigger
Commit
Write on Disk

Is This Answer Correct ?

1 Yes

1 No

which will fire first ? Trigger or Constraint..

Answer / vanujit

sequence of execution is as follows

Before statement level trigger
Before row level trigger
After row level trigger
Constraint
After statement level trigger

Is This Answer Correct ?

1 Yes

1 No

which will fire first ? Trigger or Constraint..

Answer / gourvendra singh

This is for the people who says 'No' to the answer no 7.
As i still believe that it totally depends upon the timings of the trigger.
Consider the below example:
Example1: If i created a trigger as Before insert/update/delete on a table. Whenever user will perform any DML operation on the said table, the trigger will always executes first then it will check the constraint.
Example2: If I created a trigger as After Insert/Update/Delete on a table. Whenever user will perform a DML operation on the said table, the constraint will execute first then the trigger will be executed.
Please mail me if I am wrong on the below mail id:
raviindian2114@gmail.com

Is This Answer Correct ?

0 Yes

0 No

which will fire first ? Trigger or Constraint..

Answer / lakkireddy ashok

Always trigger will fire first

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

INSERT INTO T_CHK VALUES(101);

ORA-02290: check constraint (APPS.SYS_C00207769) violated

if u create trigger
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;

INSERT INTO T_CHK VALUES(101);

ORA-20011: VALUES SHOULD BE < 100
ORA-06512: at "APPS.CHKT", line 3
ORA-04088: error during execution of trigger 'APPS.CHKT'

if u don't create trigger then constraint will be first otherwise if u create trigger to the same table with same DML operations here trigger will fire first instead of constraint firing

Is This Answer Correct ?

0 Yes

0 No

which will fire first ? Trigger or Constraint..

Answer / suthakar

It is depend upon the condition, as if trigger is written
on after insert and update then constraint will fire first
and if you make a trigger as on before insert or update
then the trigger will fire first.

Is This Answer Correct ?

0 Yes

0 No

which will fire first ? Trigger or Constraint..

Answer / radha sri seshu.kolla

NOW I AM 100% SURE THAT TRIGGER WILL FIRE FIRST.
TRY THIS CODE YOU WILL UNDERSTAND

------------------------------------------------------------
CREATE OR REPLACE TRIGGER EMPTRIGGER BEFORE INSERT ON EMP
FOR EACH ROW
BEGIN
FOR I IN (SELECT EMPNO FROM EMP)
LOOP
IF I.EMPNO=:NEW.EMPNO THEN
RAISE_APPLICATION_ERROR(-20201,'INVALID NUMBER');
END IF;
END LOOP;
END;
/
INSERT INTO EMP(EMPNO,DEPTNO) VALUES(7788,10)
---------------------------------------------------------
FEEL FREE TO TALK WITH ME ON 9966409914. IF NOT RINGING
THEN TRY 9966112520

Is This Answer Correct ?

3 Yes

4 No

which will fire first ? Trigger or Constraint..

Answer / raj dhar

Is This Answer Correct ?

4 Yes

5 No

which will fire first ? Trigger or Constraint..

Answer / shathar khan

trigger will always executes second.

that is trigger is only to execute when something happens to
the table

Even though trigger automatically executes, it's automatic
execution starts when modification of the table occurs

Is This Answer Correct ?

0 Yes

1 No

which will fire first ? Trigger or Constraint..

Answer / kaushik

If Trigger is written on the table for before insert on
each row then
Trigger will fire first
else
Constraints will fire first.

Vijay Kumar is perfectly right.

Is This Answer Correct ?

0 Yes

1 No

Post New Answer