May 21, 2009

Trigger (Insertion on Same Table)

Trigger to insert a field from sequence at user insertion itself by avoiding mutating table error. For example ; for a table as 'TABLE_NAME' with fields N_FIELDNAME,N_CODE,N_ID,VC_NAME,DT_DATE here excluding N_FIELDNAME field all other fields are user input; N_FIELDNAME field value is retrieved from SEQUENCE.If we use
select query on same table 'TABLE_NAME' it will result into Mutating table error
.So this piece of trigger code is used to do the above.

CREATE OR REPLACE TRIGGER TRG_NMAE
BEFORE INSERT ON TABLE_NAME
FOR EACH ROW
DECLARE
N_VARIABLE NUMBER(10):=0;
BEGIN
SELECT SEQUENCE_NAME.NEXTVAL INTO N_VARIABLE FROM DUAL;
:NEW.N_FIELDNAME :=N_VARIABLE;
END ;

No comments: