Eval() function? Dynamically evaluating DDL in a procedure (e.g., create trigger)
I *almost" have a procedure which will dyanamically create a set of triggers that will log a history trail for a given table.
I need to be able to execute a dynamic DDL statement (e.g., create trigger...) where the body is created on the fly. Sadly create trigger doesn't like variables. I thought the benchmark function might work, but sadly ... no.
Is there someway to create a trigger inside a procedure where parts of the trigger come from a variable? Is there an "eval" function in MySQL which can execute DDL like "create trigger"?
The following is sooooo close to working:
drop function genHistTrigger|
create function genHistTrigger (tableName varchar(255)) returns text
begin
declare done int default 0;
declare col varchar(255);
declare valueString text;
declare triggerString text;
declare colcur cursor for select column_name from information_schema.columns where table_name = tableName and table_schema = Database() order by ordinal_position;
declare continue handler for sqlstate '02000' set done = 1;
declare continue handler for sqlstate '01360' set done = 1;
open colcur;
repeat
fetch colcur into col;
if not done then
if valueString is null
then set valueString = concat("set ", col, " = NEW.", col);
else set valueString = concat(valueString, ", ", col, " = NEW.", col);
end if;
end if;
until done end repeat;
close colcur;
select benchmark(1, concat("drop trigger ", tableName, "Insert")) into done;
select benchmark(1, concat("drop trigger ", tableName, "Update")) into done;
select benchmark(1, concat("drop trigger ", tableName, "Delete")) into done;
set triggerString = concat("create trigger ", tableName, "Insert after insert on ", tableName, " for each row begin insert into ", tableName, "_hist ", valueString, "; end");
select benchmark(1, triggerString) into done;
return triggerString;
end
|
select genHistTrigger('param')|
select * from information_schema.triggers\G