I have a configuration table:
Table:
refdatecol
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*
**Update**
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;
Asked by tempusfugit
(141 rep)
Aug 8, 2014, 02:09 PM
Last activity: Feb 19, 2025, 05:05 AM
Last activity: Feb 19, 2025, 05:05 AM