trigger with dynamic column name
Good morning to all
At this moment I have the need to create a trigger, but according to the value returned by the month (coldate) function, it updates a specific column in another table.
I explain:
Table orders
order_id INT auto_increment
client_id INT
order_date DATE
city_id INT
Table summary_month_orders
consecutive INT auto_increment
client_id INT
month1 INT
month2 INT
month3 INT
month4 INT
.....
month12 INT
I need to create a TRIGGER that when inserting a record in the orders summary table adds 1 to the corresponding month field of the month_summary_orders table.
Which means that if I add an order that has the date '2020-10-01', +1 must be added to the month October from the summary_orders_month table.
I try in the following way but so far without success
delimiter //
CREATE TRIGGER add_order AFTER INSERT ON orders
FOR EACH ROW
BEGIN
DECLARE mthdb CHAR(5);
SELECT CONCAT("month",month(NEW.order_date)) INTO mthdb;
UPDATE summary_month_ordersSET mthdb=mthdb +1 WHERE client_id =NEW.client_id
END IF;
END;//
delimiter;
It returns syntax error
Subject
Views
Written By
Posted
trigger with dynamic column name
1408
November 02, 2020 06:29AM
774
November 02, 2020 10:29AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.