Creating a Trigger

[Ivan Smahin]

Hello Beatrix,

Sunday, January 4, 2009, 8:02:44 PM, you wrote:

> Hi Ivan,

> thanks for the as usual fast answer.

> On 04.01.2009, at 18:33, Ivan Smahin wrote:

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

> No, doesn't work. I still get the same error.

CREATE TRIGGER MailboxnameChange AFTER UPDATE ON Mailbox
FOR EACH STATEMENT
BEGIN
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).

> With this I get recursive SQL queries, don't I?
Sure, I meant it.

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

> Could you explain in a bit more detail what you mean with this?  
> Haven't used stored procedures before.
It's better to read wiki and other sources.
In short - there is a way to place the business logic to the database self.
Usually there are a lot of things which should be checked and
performed instead of just insert/update/delete some rows.
Here is two ways - some app which utilize the database performs
such things self and finally operates with the database in that simple
manner (insert/update/delete). Another way is using stored procedures
which makes the app free of that logic In this case the app must not
do inserts/updates/deletes but calls appropriate procedures.
There are a lot of reasons to choose the second way.
1. You are free to change behavior of any procedure. And there is no
need to track done app versions and so.
2. You are free to change database schema.
3. Any app even new one, even app which will be written by another
person will do the thing in right way.
...


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

> Will try this tomorrow.

> Mit freundlichen Grüßen/Regards

> Trixi Willius

> http://www.mothsoftware.com
> Mail Archiver X: archive, clean and search email
> http://www.beatrixwillius.de
> Fractals, 3d landscapes etc.

> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina



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