creating triggers with a stored procedure

[Danny Lewkin]

Hi Ivan,

our db is in full development, and we create a lot of tables...
Every time we have to insert those triggers by hand, and the fact is,  
that almost ALL tables have those field to fill in (we want to know  
each time somebody creates a record, modifies it and when it happens,  
but that's another trigger we also need to create this way...)
So, thanks for your quick reply, we'll try it this way!

Greetz,

Danny


On 01 Aug 2008, at 13:22, Ivan Smahin wrote:

> 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
>
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina