MySQL Forums
Forum List  »  Triggers

trigger with dynamic column name
Posted by: LEANDRO AREVALO
Date: November 02, 2020 06:29AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
trigger with dynamic column name
1062
November 02, 2020 06:29AM
571
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.