creating triggers with a stored procedure

[Ivan Smahin]

Hello Danny,

Friday, August 1, 2008, 1:37:30 PM, you wrote:

> Hi List,

> we want to do this :

> CREATE OR REPLACE PROCEDURE sp_max_CreateTriggers( IN table_name  
> STRING )
> BEGIN

> DECLARE tbl_name, trigger_name1 STRING;
> SET trigger_name1 = SELECT CONCAT( '"trigger_', table_name, '_BI"' );


> "CREATE OR REPLACE TRIGGER  trigger_name1   BEFORE INSERT ON    
> table_name  FOR EACH ROW
> BEGIN
> IF @user_id IS NOT NULL THEN
>   NEW.objptr_created_by = @user_id;
>   NEW.objptr_modified_by = @user_id;
> END IF
> NEW.creation_dt = NOW();
> NEW.modification_dt = NOW();
> END;"

> END;"

> But we get error that table "table_name" was not found... So I'm  
> guessing the variable name is not replaced with the value of it, and  
> of course, if it would work, it would create trigger "trigger_name1"...
> So we want a stored procedure that will create a trigger on the table
> we pass in the arguments...
> Any ideas on a workaround?
> Or is this a bug??

The text of procedure is prepared once - before execution or just
after "CREATE PROCEDURE" command. On this stage table_name must be
resolved. So you get an predictable result.

The workaround for runtime-creating trigger is:

CREATE PROCEDURE sp1
BEGIN
SET @a = 'CREATE TRIGGER .....' || table_name || '....';
EXECUTE @a;
END;

But I wonder to know - what is the reason to build such flexible data-model?

-- 
Best regards,
Ivan Smahin 
Senior Software Engineer
Paradigma Software, Inc
Valentina - The Ultra-Fast Database
http://www.valentina-db.com