I have a simple change log trigger on a number of tables eg for Companies the trigger is:
-----------------------------------------------------------------------------------------------------------------
CREATE TRIGGER "ChangeLog" BEFORE ALL ON "Companies"
BEGIN  
DECLARE ExistingChangeLog VARCHAR DEFAULT '';
IF OPERATION <> 'Delete' THEN
IF OPERATION() <> 'Insert' THEN
 SET NEWROW._ModDate = CURRENT_DATE;
 IF OLDROW._ChangeLog IS NOT NULL THEN
  SET ExistingChangeLog = OLDROW._ChangeLog + #13;
 END IF;
END IF;
SET NEWROW._ChangeLog = ExistingChangeLog
                        + CAST(CURRENT_TIMESTAMP AS VARCHAR(24) DATE FORMAT 'dd/mm/yyyy' TIME FORMAT 'hh:mm:ss')
                        + '¬'
                        + CURRENT_USER;
END IF;
END
----------------------------------------------------------------------------------------------------------------

I want to add a condition which says "if only these columns have changed don't bother" and I'm trying, and failing, to think of a way of setting the condition which avoids having 20+ if oldcolumn<>newcolumn type statements.

I wouldn't mind doing this for just one table but there are several with upto 60 columns

Roy Lambert

Roy,

<< I want to add a condition which says "if only these columns have changed
don't bother" and I'm trying, and failing, to think of a way of setting the
condition which avoids having 20+ if oldcolumn<>newcolumn type statements.

I wouldn't mind doing this for just one table but there are several with
upto 60 columns >>

Are you wanting this to only happen on updates, or on inserts and updates ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

Updates only.

Roy Lambert

Roy

> I have a simple change log trigger on a number of tables eg for Companies the trigger is:

I thought change logs were the work of the devil <bg>

John

John

>> I have a simple change log trigger on a number of tables eg for Companies the trigger is:
>
>I thought change logs were the work of the devil <bg>

Nah - they're not evil enough for that - it was one of his imps that designed them. Anyway mine is almost saintly - all it does is say something was changed not give a full audit trail or journal.

Roy Lambert

Roy,

<< Updates only. >>

A condition is any valid Boolean expression that references any of the
columns in the current table, the OLDROW/NEWROW values, or any function/UDF
that is present in the database.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


But what I want is to be able to say

If only these columns have changed forget it but if any of the others have changed go ahead

without having to put in 60 tests. Is there a way?


Roy Lambert

Roy,

<< But what I want is to be able to say

If only these columns have changed forget it but if any of the others have
changed go ahead

without having to put in 60 tests. Is there a way? >>

Not currently, no.  You can specify the set of columns to fire the trigger
on when they're updated, but not the opposite.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>Not currently, no. You can specify the set of columns to fire the trigger
>on when they're updated, but not the opposite.

Sigh

I'll have to see what impact 60 tests make on a post versus disabling & re-enabling the trigger

Roy Lambert

Tim


I solved it! Moved the code back into Delphi and linked it through the various tables OnBeforePost events. Part of my problem was that if the system was updating it I didn't want a record but if a user did I wanted the record.

Roy Lambert