I realise the difficulties with this, that if I am changing a value of a column in a trigger that might re-trigger the trigger and be recursive.

However I am still curious whether there is a way to achieve this effect:

CREATE TRIGGER "UpdateEndDate" AFTER UPDATE ON "Events"
WHEN OPERATION() IN ('Update')
BEGIN
 IF NEWROW.DateStart IS NOT NULL
 AND NOT NEWROW.DateStart <> OLDROW.DateStart THEN
   SET NEWROW.DateEnd = NEWROW.DateStart + INTERVAL '1' HOUR;
   END IF;
END

What I am trying to make happen:

1. "Events" record is created (DateStart is NULL) Trigger is not Triggered.
2. User updates DateStart, Trigger is triggered, "DateEnd" is set to 1 hour after start date.
3. User wants to further update any field (including DateEnd) the Trigger is not Triggered, as NEWROW.DateStart = OLDROW.DateStart

I have code doing exactly this in Delphi in the "OnChange" event of the field. However, as users access my Database from Excel and other programmes I want to try to internalize this logic into the DB.

Is there a way of doing it?

Adam


Since I don't have a suitable table to play with I had to improvise - this works OK

CREATE TRIGGER "Trial" BEFORE UPDATE OF "TestDate" ON "ClientData"
WHEN NEWROW.TestDate IS NOT NULL
OR
(NEWROW.TestDate <> OLDROW.TestDate)
BEGIN
SET NEWROW.Name = 'XXXX';
END


I think its what you want - if not can you post the table definition and a bit of data so I can have a play.

One significant difference is that I set the test on wether the trigger should execute outside of the code of what the trigger does. I didn't try your version but it tests as part of the trigger code ie the trigger does execute and you then try and stop anything happening.

I think you messed up with your conditions as well

NOT NEWROW.DateStart <> OLDROW.DateStart

is

NEWROW.DateStart = OLDROW.DateStart

Roy Lambert