Mysql – Update query in a trigger

dynamic-sqlMySQL

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;

Best Answer

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.

Related Question