Trigger for insert after

0
votes

I need to insert a 'regist' in Historic data table , when I insert something in 'ALUGUER' the trigger should insert one row in 'HISTORICO':

My Trigger:

create or replace 
trigger ADD_HISTORICO
AFTER INSERT
   ON ALUGUER
   FOR EACH ROW

DECLARE
   cod_aluguer_p NUMBER(6,0);
   cod_veiculo_p NUMBER(6,0);
BEGIN

   SELECT ID_ALUGUER INTO cod_aluguer_p
   FROM ALUGUER;
   SELECT COD_VEICULO INTO cod_veiculo_p
   FROM ALUGUER;

   INSERT INTO HISTORICO(ID_ENTRADA,DESCRICAO,DATA_REGISTO,NOVO_VEICULO,NOVO_ALUGUER)
   VALUES(SEQ_HISTORICO.nextval,'NOVA DESCRIÇÃO','21/11/2013',cod_veiculo_p,cod_aluguer_p);

END;

Error report:

SQL Error:
ORA-04091: BDDAD_DL1.ALUGUER table is mutating, trigger can not read or modify
ORA-06512: at "BDDAD_DL1.ADD_HISTORICO", line 6
ORA-04088: error during execution of trigger 'BDDAD_DL1.ADD_HISTORICO' 04091. 00000 -. "Table% s% s is mutating, trigger / function may not see it"
* Cause: A trigger (or a user defined plsql function that is referenced in
            this statement) attempted to look at (or modify) a table que was
            in the middle of being modified by the statement Which fired it.
* Action: Rewrite the trigger (or function) so it does not read that table.

2
oracleplsql

2 Answers

1
votes

No SELECT is required, just refer to the :NEW values in the trigger. Also, you can use TRUNC(SYSDATE) to get the current date assuming that's what you intended:

create or replace 
trigger ADD_HISTORICO
AFTER INSERT
   ON ALUGUER
   FOR EACH ROW

BEGIN

   INSERT INTO HISTORICO(ID_ENTRADA,DESCRICAO,DATA_REGISTO,
           NOVO_VEICULO,NOVO_ALUGUER)
   VALUES(SEQ_HISTORICO.nextval,'NOVA DESCRIÇÃO',
          TRUNC(SYSDATE),:NEW.COD_VEICULO ,:NEW.ID_ALUGUER );

END;
2
votes

You are trying to read a record from the table that the trigger has fired on. This is a no-no. So, this code:

   SELECT ID_ALUGUER INTO cod_aluguer_p
   FROM ALUGUER;
   SELECT COD_VEICULO INTO cod_veiculo_p
   FROM ALUGUER;

is not allowed. Also, it makes no sense, as there is no WHERE clause on the select, so all rows would be returned. Moot point, as you can't do this anyway. What you want to do is reference the :NEW values of the triggered row istead. Example:

   cod_aluguer_p := :new.ID_ALUGUER ;
   cod_veiculo_p L= :NEW.COD_VEICULO;

Further, you don't even needs those local variables, and just use :new directly.

   INSERT INTO HISTORICO(ID_ENTRADA,DESCRICAO,DATA_REGISTO,NOVO_VEICULO,NOVO_ALUGUER)
   VALUES(SEQ_HISTORICO.nextval,'NOVA DESCRIÇÃO','21/11/2013',:NEW.COD_VEICULO,:new.ID_ALUGUER );

I would recommend to read the Oracle docs on triggers