Ayuda con Trigger

AlfonsoAlmonte

Estoy tratando de insertar en una tabla llamada cuotas, con este triggers, al momento que me realicen una inserción en la tabla transacciones

User_6XD9J

Hi

What is yout question please ?

Zlatko Sirotic

1.See this:

CREATE OR REPLACE TRIGGER tr_ins_cuotas

   BEFORE INSERT ON transacciones

   FOR EACH ROW

...

   SELECT ... FROM transacciones ...

Read about "Mutating-Table Restriction" in:

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/triggers.htm#LNPLS759

2. ROLLBACK command will never  be executed:

WHEN OTHERS THEN

   RAISE_APPLICATION_ERROR ...

   ROLLBACK;

END;

Replace with:

WHEN OTHERS THEN

   ROLLBACK;

   RAISE_APPLICATION_ERROR ...

END;

Regards,

Zlatko

Solomon Yakobson

Zlatko Sirotic wrote:

 Replace with

You can't rollback/commit in non-autonomous transaction a trigger. ROLLBACK must be removed.

SY.

Zlatko Sirotic

Yes, of course.

I found two mistakes, and I did not see the third.

I have to start wearing glasses.

Or is it better to leave the Informatics and start to get involved in politics.

Regards,

Zlatko

AlfonsoAlmonte

i change all, but did´t works.... where is my error

CREATE OR REPLACE TRIGGER TR_INS_CUOTAS

  AFTER INSERT ON transacciones

  FOR EACH ROW

  DECLARE

    v_quincena NUMBER := 15;

    v_mensual NUMBER := 1;

    v_cuota NUMBER := 1;

    v_montocuota NUMBER(18,4);

    v_fechacuota DATE;

  BEGIN

    IF (:new.metodoplazos = 'D') THEN

      v_fechacuota := :new.fecha + v_quincena;

      v_montocuota := ((:new.monto * :new.tasa)/100)/2;

    ELSIF (:new.metodoplazos = 'M') THEN

      v_fechacuota = add_months(:new.fecha, -v_mensual)

      v_montocuota = (:new.monto * :new.tasa)/100

    END IF;

    INSERT INTO cuotas (cuotaid, transaccionid, nocuota, montocuota, fechacuota)

      VALUES (s_transacciones.NextVal, :new.transaccionid, v_cuota, v_montocuota, v_fechacuota);

END TR_INS_CUOTAS;

Solomon Yakobson

CREATE OR REPLACE

  TRIGGER TR_INS_CUOTAS

    AFTER INSERT

    ON transacciones

    FOR EACH ROW

    DECLARE

        v_quincena NUMBER := 15;

        v_mensual NUMBER := 1;

        v_cuota NUMBER := 1;

        v_montocuota NUMBER(18,4);

        v_fechacuota DATE;

    BEGIN

        IF :new.metodoplazos = 'D'

          THEN

            v_fechacuota := :new.fecha + v_quincena;

            v_montocuota := ((:new.monto * :new.tasa)/100)/2;

        ELSIF :new.metodoplazos = 'M'

          THEN

            v_fechacuota := add_months(:new.fecha, -v_mensual); -- missing colon and semi-colon

            v_montocuota := (:new.monto * :new.tasa)/100; -- missing colon andsemi-colon

        END IF;

        INSERT

          INTO cuotas(cuotaedid,transaccionid,nocuota,montocuota,fechacuota)

          VALUES(s_transacciones.NextVal,:new.transaccionid,v_cuota,v_montocuota,v_fechacuota);

END TR_INS_CUOTAS;

/

SY.

P.S. Are you sure sequence s_transactiones should be used for table cuotas?

AlfonsoAlmonte

THANKS FOR ALL, IT'S WORKING. I DIDN'T SEE THAT THE MISSING.