Insert new row into a table with values from another table mysql-CodePudding

I'm new to triggers in MySQL, so sorry for any question that seems "really easy".

I have two tables: orders and orders_log

orders:

order_id	(...)	product_id	(...)
201	(...)	103	(...)

oders_log:

log_id	action	table_name	action_time	product_id
NULL	NULL	NULL	NULL	NULL

I want to create a trigger that when we INSERT a new row into "orders" table, it will generate a new row into orders_log with the log_id = 1...2..3... etc; and product_id = to product_id in table "orders". So that the orders_log would look like this:

log_id	action	table_name	action_time	product_id
1	insert	orders	"now()"	103

I'm trying to do this code:

DROP TRIGGER IF EXISTS addrowlog;
DELIMITER $$
CREATE TRIGGER addrowlog
AFTER INSERT ON orders
FOR EACH ROW
BEGIN 
    insert into orders_log (`log_id`, `action`, `table_name`, `action_time`, `product_id`)
    VALUES  (NEW.log_id, 'insert', 'orders', NOW(), NEW.product_id);
END$$
DELIMITER;

But is gaves this error: Error Code: 1054. Unknow column 'log_id' in 'NEW.

And even when i just do this code:

DROP TRIGGER IF EXISTS addrowlog;
DELIMITER $$
CREATE TRIGGER addrowlog
AFTER INSERT ON orders
FOR EACH ROW
BEGIN 
    insert into orders_log (`log_id`, `action`, `table_name`, `action_time`, `product_id`)
    VALUES  (log_id, 'insert', 'orders', NOW(), product_id);
END$$
DELIMITER;

When i try to add a row into 'orders' it will give me error "Cannot add or update a child row: a foreign key constraint fails".

Can someone help me?

*EDIT: Creation of the log table:

CREATE TABLE `orders_log` (
    `log_id` INTEGER NOT NULL,
    `action` VARCHAR(255) DEFAULT NULL,
    `table_name` VARCHAR(255) DEFAULT NULL,
    `action_time` TIME DEFAULT NULL,
    `product_id` INTEGER NOT NULL,
    PRIMARY KEY (`log_id`)
    );

(trigger created in the middle)

plus

ALTER TABLE `orders_log`
ADD CONSTRAINT `fk_orders_log_3`
  FOREIGN KEY (`product_id`)
  REFERENCES `product` (`product_id`)
  ON DELETE RESTRICT
  ON UPDATE CASCADE;
```*

CodePudding user response：

After adding the auto_increment, you can remove the log_id from your insert or add NULL as value

CREATE TABLe orders (product_id int)

CREATE TABLE `orders_log` (
    `log_id` INTEGER NOT NULL AUTO_INCREMENT,
    `action` VARCHAR(255) DEFAULT NULL,
    `table_name` VARCHAR(255) DEFAULT NULL,
    `action_time` TIME DEFAULT NULL,
    `product_id` INTEGER NOT NULL,
    PRIMARY KEY (`log_id`)
    );

CREATE TRIGGER addrowlog
AFTER INSERT ON orders
FOR EACH ROW
BEGIN 
    insert into orders_log ( `action`, `table_name`, `action_time`, `product_id`)
    VALUES  ( 'insert', 'orders', NOW(), new.product_id);
END

INSERt INTO orders VALUEs(1)

SELECT * FROM orders_log

log_id	action	table_name	action_time	product_id
1	insert	orders	12:10:43	1

fiddle