CODEWITHSUNDEEP
sqloracle-databaseplsql
Lodi
Lodi

Reputation: 580

PL/SQL Trigger - Save a log of updated column, how to set PK of insert

I'm trying to code a trigger that logs old values of a table when a certain column of that table is updated.

This is what I coded so far:

CREATE OR REPLACE TRIGGER log_valor_produto
AFTER UPDATE OF VALOR_VENDA ON PRODUTO 
FOR EACH ROW
BEGIN
  INSERT INTO PRODUTO_HISTORICO (IDPRODUTO, DATA_ALTERACAO, VALOR_ANTERIOR, USUARIO) 
  VALUES (:OLD.IDPRODUTO, SYSDATE, :OLD.VALOR_VENDA, USER);
END;
/

(At the moment, I'm not able to test this code, unfortunately...)

My question is: How can INSERT into PRODUTO_HISTORICO table without setting something to it's PK value? The table has a PK named IDPRODUTO_HISTORICO, and I'm not setting nothing to it on my triggers's INSERT...

Upvotes: 1

Views: 358

Answers (1)

Littlefoot
Littlefoot

Reputation: 142723

Which column (in PRODUTO_HISTORICO table) makes the primary key?

Unless you handle it manually (via trigger) or - if you're on 12c - created an identity column - nothing is being done "automatically".

The simplest option is, probably, to use a sequence. For example:

CREATE SEQUENCE seq_historico;

CREATE OR REPLACE TRIGGER log_valor_produto
   AFTER UPDATE OF valor_venda
   ON produto
   FOR EACH ROW
BEGIN
   INSERT INTO produto_historico (primary_key_column,     --> I added this ...
                                  idproduto,
                                  data_alteracao,
                                  valor_anterior,
                                  usuario)
        VALUES (seq_historico.NEXTVAL,                    --> ... and this
                :old.idproduto,
                SYSDATE,
                :old.valor_venda,
                USER);
END;

Upvotes: 1

Related Questions