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 |
Explain clause in sql?
What are the different types of a subquery?
I have one table and column is c1 pk, c2 not null,c3 not null and 200 row in it and i add c4 column and value, how can is possible
Practice 1: Changes to data will only be allowed on tables during normal office hours of 8.45 in the morning until 5.30 in the afternoon, MONDAY through FRIDAY. A. Create a procedure called SECURE_DML that prevents the DML statement from executing outside of normal office hours, returning the message: “you may only make changes during normal office hours” b. Create a statement trigger on the PRODUCT table which calls the above procedure. c. Test it by inserting a new record in the PRODUCT table.
What is ON DELETE CASCADE?
Write a query to display the current date in sql?
What is pl sql variable?
can i write pl/sql code in a package
Can we use threading in pl/sql?
diff b/w function and procedure?
What is the usage of the distinct keyword?
what is the difference between where clause and having clause? : Sql dba