problem in trigger

[anuja pokharel]

hello,
i have a problem in trigger. My trigger is

CREATE OR REPLACE TRIGGER duplicate_deptn o
BEFORE INSERT OR UPDATE OF deptno ON DEPT1
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
x NUMBER ;


BEGIN

SELECT COUNT(deptno)IN TO x FROM DEPT1 WHERE deptno=:NEW.dep tno;

IF INSERTING OR UPDATING THEN
IF x>0 THEN
RAISE_APPLICATI ON_ERROR(-20101,'duplicat e deptno found');
END IF ;
END IF;
End;

My problem is
when i insert new deptno it work.if there is already that deptno then it send message.But when i update table error message come like

table....is mutating, trigger/function may not see it ............... ...............

i try to do by using autonomous transaction .i solve the mutation error but same deptno are inserted & updated.so how can i solve this problem in trigger.

[debasisdas]

The mutating trigger error comes when the user tries to update the base table from which it is reading data.

Please find the details of triggers here and mutating triggers here.

[QVeen72]

hello,
i have a problem in trigger. My trigger is

CREATE OR REPLACE TRIGGER duplicate_deptn o
BEFORE INSERT OR UPDATE OF deptno ON DEPT1
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
x NUMBER ;


BEGIN

SELECT COUNT(deptno)IN TO x FROM DEPT1 WHERE deptno=:NEW.dep tno;

IF INSERTING OR UPDATING THEN
IF x>0 THEN
RAISE_APPLICATI ON_ERROR(-20101,'duplicat e deptno found');
END IF ;
END IF;
End;

My problem is
when i insert new deptno it work.if there is already that deptno then it send message.But when i update table error message come like

table....is mutating, trigger/function may not see it ............... ...............

i try to do by using autonomous transaction .i solve the mutation error but same deptno are inserted & updated.so how can i solve this problem in trigger.

Hi,

Just Intrested to know this.. Why Use a Trigger to avoid duplicate..?
Why not Declare Unique Contsraint on that Column ..?
It is meant to avoid duplicates..
Use objects, what they are best for..

Regards
Veena

[Saii]

Yes, just create constraint and handle dup_val_on_inde x exception wherever needed.