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;
Perhaps you can use some user defined variables. I once wrote an answer to this : Disable trigger for just one table. I suggested setting up a user defined variable to disable a trigger.
In your case, dynamic SQL is not necessary if you are simply changing parameter values for a SQL statement. If you are changing table names and column names for the query, you got to construct some good
IF...THEN blocks in the trigger like my post demonstrates. If there are too many tables or too many column names to make
IF...THEN blocks, then stick with the Stored Procedure approach.
Use your dynamic SQL and/or stored procedure to insert your records in
Add one or more triggers to
on update and/or
on insert. Within the trigger, set the
project_wbs.Current_Date_Column_Name value to
That is all.
Your dynamic SQL / stored procedure takes care of updating the business data and your trigger takes care only of updating the “current date / time” column in that table – any time the record is inserted/updated (whatever meets your requirements).
Note – this will update the column when any SQL updates your data in
project_wbs. Is this acceptable?
There is some simple SQL in another question on stack exchange that shows you how to construct the trigger (assuming no other logic than to set the current time on any/every update/insert).