Creating a Trigger

[Ivan Smahin]

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.

I guess you mean any value starting from 'aaaa:test' or something - right?
Anyway - do you have some link on them? Seems no.

> After reading about the triggers in the Wiki I came
> up with a first try

> create trigger MailboxnameChange
>         after update
>         on Mailbox
>         for each row
>         referencing New as NewRow Old as OldRow
>         set @NewValue = NewRow.MailboxPath
>         begin
>                 newRow.MailboxPath = Replace(OldRow.MailboxPath, @NewValue)
>         end;

> This, however, only gives a kernel error 71000, which isn’t even in  
> the error list or I'm blind.

ERR_SQL_PARSER_ERROR


> Even a quite simple trigger like



> create trigger MailboxnameChange after update on Mailbox
>         begin
>         end;

It should be
create trigger MailboxnameChange after update on Mailbox
FOR EACH ROW
or FOR EACH STATEMENT



> 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.

It could be powered by the link of any type (most nature way).

One more solution is stored procedures.
So you will not call update for this table but some SP which will do
all changes self.



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