which will fire first ? Trigger or Constraint
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
Interchange the value of a column Gender in a table where values are Male and Female. So, where the value is Male, it should changed to Female and Female to Male.
How to add new employee details in an employee_details table with the following details
Why do we use procedures in pl sql?
What does closing a cursor do?
What is cross join example?
Can a view be updated/inserted/deleted?If Yes under what conditions?
Why is nosql good?
i have table T!. A B C D NULL 1 2 3 4 NULL 5 6 7 8 NULL 9 10 11 12 NULL. I WANT COUNT OF NULL VALUES IN TABLE. WRITE A QUERY.
what is the difference between to_char and to_date functions?
What is sql entity?
what are the different type of sql's statements ? : Sql dba
What are the different ddl commands in sql?