CODEWITHSUNDEEP
sqloracle-database
Weenhallo
Weenhallo

Reputation: 364

How to insert an ID into another table

I'm practising triggers on Oracle and I'm having troubles with this trigger:

CREATE OR REPLACE TRIGGER MODIFICACIONES_SALARIOS AFTER
    UPDATE OF salario ON empleados_pac
    FOR EACH ROW
DECLARE
    v_username VARCHAR(10);
    v_hora VARCHAR2(10);
BEGIN
    SELECT user INTO v_username FROM dual;
    SELECT to_char(sysdate, 'HH24:MI:ss') INTO v_hora FROM dual;
    
    INSERT INTO audita_salarios (id_emp, salario_antiguo, salario_nuevo, fecha, hora, username) VALUES (id_empleado, :old.salario, :new.salario, sysdate, v_hora, v_username);
END;

I'm doing an historic table where when the salary of the employeer changes the trigger inserts in the historic his ID, the old salary, the new one, the date and the user who changed the salary. All works perfectly except for the ID insert. Oracle says that the column is not allowed.

I think the problem is because I want to insert an unique ID into a new column where the ID is going to be repeated if the salary changes two or more times. How could I fix this?

Upvotes: 0

Views: 68

Answers (1)

MT0
MT0

Reputation: 168001

I think the problem is because I want to insert an unique ID into a new column where the ID is going to be repeated if the salary changes two or more times. How could I fix this?

Do not have a UNIQUE constraint on audita_salarios.id_emp; instead have a composite UNIQUE constraint on id_emp and fecha (if you need a UNIQUE constraint at all).

Also:

  • use a virtual column for HORA (or remove that column as it is just duplicating the time component of FECHA);
  • use :NEW.id_empleado rather than id_empleado in the trigger.
CREATE TABLE empleados_pac (
  id_empleado NUMBER(2),
  salario     NUMBER(10,2)
);

CREATE TABLE audita_salarios (
  id_emp          NUMBER(2),
  salario_antiguo NUMBER(10,2),
  salario_nuevo   NUMBER(10,2),
  fecha           DATE, 
  hora            VARCHAR2(8)
                  GENERATED ALWAYS AS ( CAST( TO_CHAR( fecha, 'HH24:MI:SS' ) AS VARCHAR2(8) ) ),
  username        VARCHAR2(60),
  CONSTRAINT      audita_salarios__pk PRIMARY KEY ( id_emp, fecha )
);

CREATE TRIGGER MODIFICACIONES_SALARIOS
  AFTER UPDATE OF salario ON empleados_pac
  FOR EACH ROW
DECLARE
BEGIN
    INSERT INTO audita_salarios (
      id_emp, salario_antiguo, salario_nuevo, fecha, username
    ) VALUES (
      :new.id_empleado, :old.salario, :new.salario, sysdate, USER
    );
END;
/

INSERT INTO empleados_pac ( id_empleado, salario ) VALUES ( 1, 10 );

UPDATE empleados_pac
SET    salario = 20
WHERE  id_empleado = 1;

BEGIN
  DBMS_SESSION.SLEEP(1);
END;
/

UPDATE empleados_pac
SET    salario = 30
WHERE  id_empleado = 1;

-- You do not actually need to constraint on the audit table unless you
-- want to enforce that salary updates must be on different times.

ALTER TABLE audita_salarios DROP CONSTRAINT audita_salarios__pk;

UPDATE empleados_pac
SET    salario = 40
WHERE  id_empleado = 1;

Then:

SELECT * FROM audita_salarios;

Outputs:

ID_EMP | SALARIO_ANTIGUO | SALARIO_NUEVO | FECHA               | HORA     | USERNAME                   
-----: | --------------: | ------------: | :------------------ | :------- | :--------------------------
     1 |              10 |            20 | 2021-04-01 13:02:42 | 13:02:42 | FIDDLE_XRWUFWRAYDJNIGNCOQFP
     1 |              20 |            30 | 2021-04-01 13:02:43 | 13:02:43 | FIDDLE_XRWUFWRAYDJNIGNCOQFP
     1 |              30 |            40 | 2021-04-01 13:02:43 | 13:02:43 | FIDDLE_XRWUFWRAYDJNIGNCOQFP

db<>fiddle here

Upvotes: 1

Related Questions