Create a trigger to insert the old data to a new table

Question

Here is the table I created.

USE my_guitar_shop;

DROP TABLE IF EXISTS Products_Audit;

CREATE TABLE Products_Audit ( 
audit_id INT PRIMARY KEY, 
category_id INT REFERENCES categories(category_id), 
product_code VARCHAR ( 10 ) NOT NULL UNIQUE , 
product_name VARCHAR ( 255 ) NOT NULL, 
list_price INT NOT NULL, 
discount_percent INT NOT NULL DEFAULT 0.00 , 
date_updated DATETIME NULL); 

Create a trigger named products_after_update. This trigger should insert the old data about the product into the Products_Audit table after the row is updated. Then, test this trigger with an appropriate UPDATE statement.

Here is the trigger I created but the data is not showing up in the Products_Audit table it is showing all null.

USE my_guitar_shop;

DROP TRIGGER IF EXISTS products_after_update;

DELIMITER $$

CREATE TRIGGER products_after_update
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
  INSERT INTO products_audit (audit_id, product_id, category_id, product_code, 
  product_name, list_price, discount_percent, date_updated)

  SELECT audit_id, products.product_id, products.category_id, products.product_code, 
  products.product_name,products.list_price, products.discount_percent, date_updated

  FROM products JOIN products_audit
  ON products_audit.audit_id = (SELECT audit_id FROM inserted);
END $$

DELIMITER ;

EDIT with the INSERT INTO

  USE my_guitar_shop;

  DROP TRIGGER IF EXISTS products_after_update;

  DELIMITER $$

  CREATE TRIGGER products_after_update
BEFORE UPDATE ON products
FOR EACH ROW
  BEGIN

  INSERT INTO products_audit (audit_id, product_id, category_id,product_code, 
  product_name, list_price, discount_percent, date_updated)

  VALUES (OLD.audit_id, OLD.product_id, OLD.category_id, OLD.product_code, 
  OLD.product_name, OLD.list_price, OLD.discount_percent, OLD.date_updated)

  DELIMITER ;

Answer 1

You are overcomplicating the insert. As mysql documentation on triggers says:

In an UPDATE trigger, you can use OLD.col_name to refer to the columns of a row before it is updated and NEW.col_name to refer to the columns of the row after it is updated.

Therfore, use the OLD.column_name format in the insert. Also, I would set the audit_id field to auto increment and leave it out of the insert:

INSERT INTO products_audit (product_id, category_id, product_code, 
product_name, list_price, discount_percent, date_updated)
VALUES (OLD.product_id, OLD.category_id, OLD.product_code, 
OLD.product_name, OLD.list_price, OLD.discount_percent, OLD.date_updated)

Answer 2

Here's an example of how I do it:

CREATE OR REPLACE EDITIONABLE TRIGGER E_TABLE_TRG
  before insert or update or delete on e_table 
  for each row 
declare
  l_seq    number;
begin 
  -- Get a unique sequence value to use as the primary key
  select s_seq.nextval
    into l_seq
    from dual;
    
  if inserting then
    :new.date_opened := sysdate;
    :new.last_txn_date := null;
    :new.status := 'A';
  end if;
   
  if inserting then
    insert into e_table_history
    (
     t_seq,
     user_id,
     date_opened,
     last_txn_date,
     status,
     insert_update_delete,
     insert_update_delete_date
    )
    values
    (
     l_seq,
     :new.user_id,
     :new.date_opened,
     :new.last_txn_date,
     :new.status,
     'I',
     sysdate
    );
  elsif updating then 
    insert into e_table_history
    (
     t_seq,
     date_opened,
     last_txn_date,
     status,
     insert_update_delete,
     insert_update_delete_date
    )
    values
    (
     l_seq,
     :new.date_opened,
     :new.last_txn_date,
     :new.status,
     'U',
     sysdate
    );
  else
    insert into e_table_history
    (
     t_seq,
     date_opened,
     last_txn_date,
     status,
     insert_update_delete,
     insert_update_delete_date
    )
    values
    (
     l_seq,
     :old.date_opened,
     :old.last_txn_date,
     :old.status,
     'D',
     sysdate
    );
  end if;
end; 
/

ALTER TRIGGER E_TABLE_TRG ENABLE;
/