[Hsqldb-user] Triggers

[Andrew K. <aks...@nt...>]

Been doing some more testing on triggers.

It seems to me that the row data passed to the before update trigger
reflects the data currently stored in the database. It does not contain the
modified data as per the SET clause of the UPDATE statement. As I could not
find a way, so far, of aborting the update from the before trigger and I do
not seem to have access to the new data that is going to be updated to the
row then the before update trigger is totally useless for my purposes. I
cannot validate the data.

I then looked at the after update trigger thinking that I can use some
mechanism to use the before update trigger to store the pre-update data and
do my validation in the after update trigger and, if necessary, connect to
the database and correct any errors by updating the same record again (to
remove/correct the bad data). Unfortunately, the row object passed to the
after update trigger fire() method is NULL!!! Now the only way I can
determine which row was the subject of the after update trigger is by
querying the object I stored using the before update trigger so I can find
the row ID to query the database in the after update trigger to get the
latest row data, then do my validation, etc. etc. That must be the
workaround of all workarounds....

I'm sure that there must be some bugs or 'to be implemented' features here.
As far as I know the triggers in SQL Server and Oracle can effectively abort
an insert or update operation. I believe the before update trigger should be
capable of doing the same by throwing SQLException. It would also make sense
to me for the row data passed to the before update method to contain the
data that is about to be updated to the database, not the data that is
currently in the database (you can query that if you need to). If this
doesn't happen the before update trigger can never be used to validate data
because you don't know what data is about to be changed. The after update
trigger is too late to abort the update but the row data passed should be
the data that has just been updated to the database (and is now, in fact, in
the database).

Triggers seem to have problems. Can somebody elucidate please?

Andrew Knight.