Recursion on TimeStamp field in update trigger

[HedleySohn]

We have ASA 9.0. We have to keep a DATE field updated with the current time. We CANNOT define the field with DEFAULT TIMESTAMP because the user applications sometimes issue UPDATE statements when nothing about the row has changed. This then causes a new TIMESTAMP but the data in the record hasn't really changed. We have a busines rule that the DATE field is only updated when the record is inserted or when some field within the record has actually been changed.

So, we were forced to implement this with INSERT and UPDATE triggers. The problem we have is that the insert after trigger we use is written as an update to it's table:

ALTER TRIGGER "TIME_TRIG" AFTER INSERT
ORDER 1 ON "DBA"."DOCS"
REFERENCING NEW as new_insert
FOR EACH ROW
BEGIN
update DBA.DOCS set CHANGED_DATE = getdate(*)
WHERE DOCS.PKEY = new_insert.PKEY
END

The Update trigger:
ALTER TRIGGER "TIME_TRIG" AFTER UPDATE
ORDER 1 ON "DBA"."DOCS"
REFERENCING OLD AS old_record
FOR EACH ROW
BEGIN
update DBA.DOCS set CHANGED_DATE = getdate(*)
WHERE DOCS.PKEY = old_record.PKEY
END

then has it's own update statement.

The problem is that the update trigger can sometimes get into an extreme recursive loop which then causes an exception. This is also very inefficient.

Can anyone suggest a better way to handle this using triggers. Remember, we simply cannot define the COLUMN as a DEFAULT TIMESTAMP (although we'd love to)

Thanks,

-HS