Tim

I've created two triggers, one works one doesn't

CREATE TRIGGER "biRemoveTrailingBlanks" BEFORE INSERT
ON "Accounts"
BEGIN
SET NEWROW._ID = RTRIM(NEWROW._ID);
END

works (ie _ID is RTRIMed and I get a PK violation)

CREATE TRIGGER "buRemoveTrailingBlanks" BEFORE UPDATE OF "_ID"
ON "Accounts"
BEGIN
SET NEWROW._ID = RTRIM(NEWROW._ID);
END

doesn't work - nothing happens. I tried SET NEWROW._ID = RTRIM(OLDROW._ID); but still nothing. Spaces are left exactly where they were

Roy Lambert

Tim


I'm assuming that before insert means after post/save has been clicked and before the data gets to the database.

Roy Lambert

Roy,

<< CREATE TRIGGER "buRemoveTrailingBlanks" BEFORE UPDATE OF "_ID"
ON "Accounts"
BEGIN
SET NEWROW._ID = RTRIM(NEWROW._ID);
END

doesn't work - nothing happens. I tried SET NEWROW._ID = RTRIM(OLDROW._ID);
but still nothing. Spaces are left exactly where they were >>

For some strange reason (probably a bug), it seems that the trimming of
trailing spaces doesn't work *if* the trigger is defined with the OF
<UpdateColumns> clause.

Try to define your trigger as follows:

CREATE TRIGGER "buRemoveTrailingBlanks" BEFORE UPDATE
ON "Accounts"
BEGIN
SET NEWROW._ID = RTRIM(NEWROW._ID);
END

Ole Willy Tuv

Ole


Brilliant. I never would have thought of that.  In fact I had to stare at your code for a couple of minutes before I spotted the difference.

In case you hadn't guessed you were right, it worked.

Roy Lambert

Roy,

<< In case you hadn't guessed you were right, it worked. >>

That's good.

Your original trigger should have worked also, of course. AFAICS, the
problem/bug is that a trigger defined with OF <UpdateColumns> doesn't fire
at all.

Ole Willy Tuv

Ole,

<< Your original trigger should have worked also, of course. AFAICS, the
problem/bug is that a trigger defined with OF <UpdateColumns> doesn't fire
at all. >>

Yep, a flag isn't getting set properly when the modified column values are
examined, and they are skipped.

--
Tim Young
Elevate Software
www.elevatesoft.com