Update query in a trigger

Question

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;

Answer 1

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.

Answer 2

Use your dynamic SQL and/or stored procedure to insert your records in project_wbs.

Add one or more triggers to project_wbs on update and/or on insert. Within the trigger, set the project_wbs.Current_Date_Column_Name value to now().

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).