This was asked before, but didn't get resolved. I'd like to create a trigger and have it run against the entire table. I can't do update foo set bar=bar because Elevate is too smart for that. I don't want to create a procedure because I'll be adding quite a number of triggers and I don't want to double up on that, and because it will also double the cost, because the trigger would also call the proc.

Jeff

>This was asked before, but didn't get resolved. I'd like to create a trigger and have it run against the entire table. I can't do update foo set bar=bar because Elevate is too smart for that. I don't want to create a procedure because I'll be adding quite a number of triggers and I don't want to double up on that, and because it will also double the cost, because the trigger would also call the proc.

I asked it before (I wanted to reformat phone numbers throughout the database) and Tim's response was basically ElevateDB isn't that smart. ie update foo set bar=bar should work

Roy Lambert [Team Elevate]

Jeff,

<< This was asked before, but didn't get resolved. I'd like to create a
trigger and have it run against the entire table. I can't do update foo set
bar=bar because Elevate is too smart for that. I don't want to create a
procedure because I'll be adding quite a number of triggers and I don't want
to double up on that, and because it will also double the cost, because the
trigger would also call the proc. >>

Hmm, the best solution to this would probably be a statement that forces the
triggers to be executed in a certain "mode" (insert, update, or delete)
against all rows in a table, so I'll have to add this to the list.  Luckily
I'm almost done with the last round of improvements (reverse-engineering
improvements are the last of the bunch), and am about to start on selecting
the next round to put on the roadmap for 2011.

--
Tim Young
Elevate Software
www.elevatesoft.com

Jeff,

UPDATE FOO SET BAR = BAR does fire the Before Update and After Update triggers.  I think all SQL databases will do the same.

Richard Harding

To clarify, I did test set foo = foo before I posted, have tested it again, and it does not fire a trigger in 2.04b5.

I think what I'll do for now is just create a column Twiddle and not document it. Add the trigger, set Twiddle = RANDOM(0,1), problem solved. Hide the name in char foo[] = {'T','w','i','d','d','l','e',0} so they can't find where it gets set. It'll be our project's great mystery.

Jeff


I've just tested in EDBManager (easier than modding the app) and it works here in 2.04b4

I disabled the trigger ran

update career set _mainphone = replace(' ' with '' in _mainphone)

checked - all phone numbers now lack spaces

enabled the trigger and ran

update career set _mainphone = _mainphone

checked - all phone numbers are now "properly" formatted.

Where did you test - in your app or EDBManager. If the former can you try in EDBManager.


Roy Lambert [Team Elevate]

Jeff,

<< To clarify, I did test set foo = foo before I posted, have tested it
again, and it does not fire a trigger in 2.04b5. >>

Do you mean 2.05 ?  I just tried it here and it works fine in 2.05.  Is it
possible that your "foo" is NULL ?  If so, then it won't do anything because
NULL <> NULL.

--
Tim Young
Elevate Software
www.elevatesoft.com