SQL Trigger Inserting

Question

I need to insert the NRO_BOLETA, MONTO_BOLETA * 0,15 (VALOR_COMISION in table COMISION_VENTA) values in the COMISION_VENTA table when a new BOLETA is inserted into the database

BOLETA table:

CREATE TABLE BOLETA 
(
      nro_boleta NUMBER(10) CONSTRAINT PK_BOLETA PRIMARY KEY, 
      fecha_boleta DATE  NOT NULL, 
      monto_boleta NUMBER(15)  NOT NULL, 
      id_cliente NUMBER(10) NOT NULL,
      numrut_emp NUMBER(10) NOT NULL,

      CONSTRAINT FK_FACTURA_VENDEDOR 
          FOREIGN KEY(numrut_emp) REFERENCES empleado(numrut_emp)
);

COMISION_VENTA table:

CREATE TABLE COMISION_VENTA
(
      nro_boleta NUMBER(10) NOT NULL CONSTRAINT PK_COMISION_VENTA PRIMARY KEY ,
      valor_comision  NUMBER(10) NOT NULL,

      CONSTRAINT FK_COMISION_VENTA_BOLETA 
          FOREIGN KEY(nro_boleta) REFERENCES BOLETA(nro_boleta)
);

Trigger:

CREATE OR REPLACE TRIGGER asignacion
BEFORE INSERT OR DELETE OR UPDATE ON BOLETA
FOR EACH ROW
BEGIN
    IF INSERTING THEN
        INSERT INTO COMISION_VENTA(NRO_BOLETA, VALOR_COMISION) 
        VALUES(:NEW.NRO_BOLETA, ROUND(:NEW.MONTO_BOLETA*0,15));
    END IF;
END;

Insert and error:

INSERT INTO BOLETA(NRO_BOLETA, FECHA_BOLETA, MONTO_BOLETA, ID_CLIENTE, NUMRUT_EMP)
VALUES (28, '26/06/18', 258999, 3000, 12456905)

Informe de error
ORA-02291: integrity constraint (ADMIN_ENS.FK_COMISION_VENTA_BOLETA) violated - parent key not found
ORA-06512: at "ADMIN_ENS.ASIGNACION", line 3
ORA-04088: error during execution of trigger 'ADMIN_ENS.ASIGNACION'

You first need to insert into empleado or make sure empleado already has a row with NUMRUT_EMP = 12456905 — shrek

shrek shrek · Accepted Answer · 2018-06-28T17:53:50

Update your trigger code to be a AFTER TRIGGER -

CREATE OR REPLACE TRIGGER asignacion
**after** INSERT OR DELETE OR UPDATE ON BOLETA
FOR EACH ROW
BEGIN
    IF INSERTING THEN
        INSERT INTO COMISION_VENTA(NRO_BOLETA, VALOR_COMISION) 
        VALUES(:NEW.NRO_BOLETA, ROUND(:NEW.MONTO_BOLETA*0,15));
    END IF;
END;
/

This should work.

SQL Trigger Inserting

1 Answers