Forum Stats

  • 3,827,736 Users
  • 2,260,814 Discussions
  • 7,897,363 Comments

Discussions

Ayuda con Trigger

AlfonsoAlmonte
AlfonsoAlmonte Member Posts: 32
edited Jun 14, 2015 10:29PM in SQL & PL/SQL

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

Pantallazo-2.png

Pantallazo-1.png

Pantallazo-3.png

Tagged:
TPD-Opitz

Answers

  • User_6XD9J
    User_6XD9J Member Posts: 618 Bronze Badge
    edited Jun 14, 2015 2:16AM

    Hi

    What is yout question please ?

  • Zlatko Sirotic
    Zlatko Sirotic Member Posts: 2,134 Gold Trophy
    edited Jun 14, 2015 6:52AM

    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
    Solomon Yakobson Member Posts: 19,488 Red Diamond
    edited Jun 14, 2015 7:41AM
    Zlatko Sirotic wrote:
    
     Replace with
    
    

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

    SY.

  • Zlatko Sirotic
    Zlatko Sirotic Member Posts: 2,134 Gold Trophy
    edited Jun 14, 2015 10:16AM

    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

    TPD-Opitz
  • AlfonsoAlmonte
    AlfonsoAlmonte Member Posts: 32
    edited Jun 14, 2015 12:52PM

    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
    Solomon Yakobson Member Posts: 19,488 Red Diamond
    edited Jun 14, 2015 1:24PM

    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
  • AlfonsoAlmonte
    AlfonsoAlmonte Member Posts: 32
    edited Jun 14, 2015 10:29PM

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

This discussion has been closed.