Creating a Trigger

[Ivan Smahin]

Hello Ivan,

Sunday, January 4, 2009, 7:33:59 PM, you wrote:

> Hello Beatrix,

> Sunday, January 4, 2009, 6:37:47 PM, you wrote:

>> Hi,

>> I’m trying to create a trigger without any success so far. The  
>> database has a table called Mailbox with a field called MailboxPath.  
>> The table contains path data like the following:

>> aaaa
>> aaaa:test
>> aaaa:test:ssss
>> aaaa:test:xxxxx
>> xxxx:xxxx
>> yyyy

>> If I change the row aaaa:test into aaaa:something in my app I would  
>> like to change in all the children rows the data, too, and do this  
>> with a trigger.


>> Is what I want to do possible with a trigger? If yes, how?

> Something like this:

> begin
>     DECLARE query STRING;
>     SET query = 'UPDATE Mailbox SET MailboxPath = REPLACE(
>         OLD.MailboxPath, NEW.MailboxPath ) WHERE MailboxPath like
>         NEW.MailboxPath ';
>     EXECUTE query;
> end;


> It is hardly possible with triggers. Because of you are going to
> update the same table on 'update' event. FOR EACH ROW or
> FOR EACH STATEMENTS will be fired trigger again and again.

One more "solution"
You can do the trick inside the trigger

first line is:
ALTER TRIGGER trigger1 DISABLE;
then do update the childs (trigger1 is disabled - so it will not be
fired on this statement)

and finally
ALTER TRIGGER trigger1 ENABLE;


But I'm not sure it could be considered as solution.


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