Glenn,
well, I should have known that it would take me more than a
few days until I get time to respond to your suggestions;-)
As to the three suggestions, I still think the current
implementation (i.e., option a)) is alright as soon as it is
a somehow documented feature.
IMHO, option b) is problematic from a user's point of view:
It makes me think of a row "still under a construction -
handle with care". The situation that a computed column is
completely unaccessable in a before trigger seems to be more
unexpected than the current behaviour is. And it would lead
to a further difference between before and after triggers.
(There are a lot following Breck's book;-).)
To find a good solution, I'm tempted to use the notion of a
C++ object here:
Within a constructor, an object is not complete, and
therefore particular rules must be followed. For example,
you can not use dynamic binding here.
Additionally, you can establish any kind of invariant. An
invariant should be valid after the object is fully
constructed, and before and after any public function is
called. However, within private functions, it's the
implementor's responsibility whether invariants are valid or
not.
In other words: The implementor of a class must act with
particular care. In contrast, the users of the class (and
the implementors of any derived classes) should be able to
rely on a valid object state.
With that notion in mind, a COMPUTEd column should be
treated as an invariant.
Each "user" of an according row should be able to rely on
the fact that a) the computed column exists and b) that its
value is valid.
(That is already given in the current implementation.)
And as a trigger programmer, I would think of myself as some
kind of "derived class implementor": Writing triggers
resembles writing hooks - and they seem to be a kind of
"overridable functions".
In other words, as a trigger programmer, I think I should
take more care than an ordinary SQL user but I think I
should need much, much less knowledge than the classes'
implementor - and the latter seems to be YOUR part...
Therefore, IMHO option c) would be best from a
"philosophical" point of view: It if had already been
implemented, my original question would not have been
asked...
I cannot, however, evaluate the general effect of the
possible performance penalties you refer to:
In my case, only one before trigger is defined per table,
and that would mean only one more COMPUTE re-evaluation than
now. Since with option a), I have to enforce the
re-evaluation in the trigger's code, too (at least under
certain conditions), option c) should not lead to a
noticable performance decrease here.
Sorry for any "too philosophical statements" - I found it
quite hard to express my thoughts.
Best regards
Volker
Post by Glenn PaulleyThis issue is very interesting, and we've been discussing
it this afternoon amongst my Engineering team. At present
, for an INSERT statement, the engine is performing the
1. Evaluate all columns specified in the INSERT statement
2. Check that the row attribute match the table schema
(nullability) 3. Evaluate computed columns
4. Fire row-level before triggers
5. Check for NULL in not-NULL column
6. Check constraints and articles
7. insert the row
8. fire row level after triggers
9. fire set level after triggers
Step (2) is required to ensure at all times that the "row
image" seen by a trigger matches the schema of the
database. For example, if a column is declared NOT NULL,
any trigger cannot "see" that column with a NULL value
(if, for example, that is what the application inserted).
That is particularly true for COMPUTEd columns, because
the evaluation of the COMPUTE expression may implicitly
take advantage of schema information (such as
nullability) that cannot be violated.
In a nutshell, and despite our warnings to the contrary,
your only reasonable solution to the problem is to
explicitly SET the computed column directly within the
BEFORE INSERT trigger, since the computed column will not
be subsequently re-evaluated regardless of the changes
made by any BEFORE trigger.
For a later release, we are considering three potential
a) do nothing, and leave the semantics as they are.
b) remove COMPUTEd columns from the schema for all BEFORE
TRIGGERS, and move the evaluation of any COMPUTEd column
to immediately before inserting the row into the
database. This will eliminate semantic problems and
inconsistencies caused by the actions of BEFORE triggers,
with the tradeoff of potentially breaking existing
applications. c) retain the ability for BEFORE triggers to
"see" computed columns as part of the schema of the row,
but re-evaluate all COMPUTEd columns prior to each BEFORE
trigger, with one additional evaluation prior to
inserting the row (ie. for N triggers, evaluate the
COMPUTE expression N+1 times). There are some potential
optimizations that can be applied, but the problem is
very difficult since the COMPUTEd column definition can
be
COMPUTE ( FOO() )
and there is no way to easily determine if the
user-defined function FOO() has any direct or indirect
attribute references that are affected by a trigger. This
approach has obvious performance penalties, along with
yet another potential semantic issue: it may be
undesireable to re-evaluate a COMPUTE expression multiple
times if that function has side-effects (such as to
save/compute the next surrogate key value for a table).
I hope this helps to explain the server's behaviour. I
would be interested, Volker, in hearing your reaction to
the alternatives listed above.
Glenn
Post by Volker BarthI have to set the computed column in the trigger.
Guess I didn't think of this at first as I thought
computed columns must not be set manually. But the docs
only state, "they should not":)
So in the sample below, I added
set T_N.col3 = T_N.col2 * 2;
after
set T_N.col2 = T_O.col2;
Still I feel that this is somewhat errorprone (I have to
retype the expression used in the computed column
definition) and is not the preferred way to go.
So, my question remains if this theses is correct and is
by design: "The recalculation of a computed column only
takes place before a before trigger is fired."
Can someone from iAnywhere clarify on that, please?
Thanks
Volker
Hi all,
I'm using SA 10.0.1.3551.
I have a table with a computed column based on a
user-defined function that
is applied on another column, say col_c = fn(col_b).
This has worked fine in the past.
Now I have to forbid to change the base column under
certain circumstances.
I try to do this in a before update trigger that sets
the new value back to
the old value.
But when the new value is reset; the computed column is
not recalculated. In
contrast, it still is calculated based on the new (and
now rejected) value.
So it seems that the calculation of a computed column
takes place *before* >> the actions of a before trigger.
Is that by design? >> And if so, how can I overcome this
restriction? >> A rollback is not applicable, as I do not
Post by Volker Barthwant to reject all changes to the
row but have to reject changes to particular columns.
TIA
Volker
create table T_Test
(
pk int default autoincrement primary key,
col2 int not null,
col3 int not null compute (col2 * 2),
col4 int null
);
insert T_Test (col2, col4) values (2, 22);
insert T_Test (col2, col4) values (12, null);
select * from T_Test;
-> displays
1 2 4 22
2 12 24 null
create trigger TUB_Test before update on T_Test
referencing old as T_O new as T_N
for each row
begin
-- reject changes to col2 if col4 has a particular
value >> if T_O.col2 <> T_N.col2 and T_N.col4 = 22
then >> message 'col2 before: (old/new): ' ||
T_O.col2 || '/' || T_N.col2; >> message 'col3
before: (old/new): ' || T_O.col3 || '/' || T_N.col3; >>
set T_N.col2 = T_O.col2; >> message 'col2
after: (old/new): ' || T_O.col2 || '/' || T_N.col2; >>
message 'col3 after: (old/new): ' || T_O.col3 || '/'
|| T_N.col3; >> end if;
Post by Volker Barthend;
update T_Test
set col2 = 3, col4 = 23 where pk = 2;
-> no particular action
select * from T_Test;
-> displays
1 2 4 22
2 3 6 23
update T_Test
set col2 = 23 where pk = 1;
-> particular action
col2 before: (old/new): 2/23
col3 before: (old/new): 46/46
col2 after: (old/new): 2/2
col3 after: (old/new): 46/46
select * from T_Test;
-> displays
1 2 46 22 <- col 3 is not (2 * col2) anymore
2 3 6 23
--
Glenn Paulley
Director, Engineering (Query Processing)
iAnywhere Solutions Engineering
EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all
To Submit Bug Reports: http://case-express.sybase.com
SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288
Whitepapers, TechDocs, and bug fixes are all available
through the iAnywhere Developer Community at
www.ianywhere.com/developer