I have a configuration table:
id int(11) startdatecol varchar(100) enddatecol varchar(100) datecolTable varchar(100)
This table holds the user configured column names and table names.
I am building a query run time using the data from
refdatecol and updating another table
project_wbs. I am using dynamic SQL.
This works fine inside the procedure.
Problem is: I want to fetch the current date values into the
project_wbs on insert OR update. Naturally, I thought of using trigger. But triggers do not support dynamic SQL in MySQL.
So I thought of calling the procedure from the trigger. Then I learnt it is a strictly no-no.
Is there any way I can achieve this?
I am also open to considering a complete redesign of this user-configurable dates
Here is the stored procedure:
create procedure sp_project_date (_id int) begin set @_startdate =''; set @_enddate =''; set @_projectId = (select project_wbs.proj_projectId from project_wbs project_wbs where id = _id); set @q = (select concat('select ', startdatecol, ' , ', enddatecol , ' into @_startdate, @_enddate from ' , datecolTable , ' where project_ProjectId = ' , @_projectId) as 'query' from refdatecol where id = (select p.cogsDateId from project p where p.projectId = @_projectId) ); prepare stmt from @q; execute stmt; UPDATE project_wbs SET project_startdate = @_startdate, project_enddate = @_enddate WHERE id = _id; DEALLOCATE PREPARE stmt;