Sure thing.
The primary keys are not used anywhere in the adapter so they can just
be removed.
I think I have needed that info in some of my triggers and thus put it
there for easy access.
Although primary keys do not change often it is still theoretically
possible and to have them
there would need a more robust solution to keep them up-to-date.
Further I can always extend H2's adapter to my specific needs.
DbObjectName is just a simple class that contains schemaname and
dbobject name
and helps me dealing with parsing, formatting, default values etc.
Simplest way to remove them is just explode them to 4 strings:
triggerSchema, triggerName, tableSchema, tableName
and since h2 requires that triggerSchema and tableSchema be always the same
the list becomes:
schema, triggerName, tableName
(although I have to say there is no good reason for this limitation).
Other than that there is just the updateColumn oneliner.
Some helper methods to deal with trigger type but as the documentation
explains they can not deal gracefully with triggers that have more than
one type.
And as discussed on this list earlier, the type should not be defined in
the init method
but rather given as a parameter to fire method in which case only one
type would be meaningful
at a time.
Finally there are 2 methods for checking whether anything between new
and old row has changed.
Useful information in many cases when you don't want to start big
operations if nothing that would
make a difference has actually changed.
Finally I propose that we could implement a simple example trigger into
h2 examples
that extends the adapter and updates last modified if something in the
row has changed.
This way Christian gets good sample code for his book :-)
It's best that you Thomas formulate the patch and commit but I would be
happy to review it
and sugar it with my comments!
- rami
30.11.2011 20:09, Thomas Mueller kirjoitti:
Hi,
To Rami Ojares: those additions look nice! There might be a few
smaller changes I would like to do (probably remove primaryKeyColumns,
DbObjectName; add test cases), but I'm sure we can work that out.
Would you be willing to contribute it to H2? If yes then I suggest we
work on a patch and once we both agree on a solution I will give you
the rights to commit it to H2. Or I can commit it if you like.
I think the "last modified" is quite a common problem. For H2, there
is a simpler solution that doesn't require a trigger: computed
columns. Example (the "@sleep 2" only works in the H2 Console tool):
create table test(
id int primary key,
name varchar(255),
last_modfied timestamp as now()
);
insert into test(id, name) values(1, 'Hello');
select * from test;
@sleep 2;
update test set name = 'Hallo';
select * from test;
drop table test;
For details about computed columns, see
http://h2database.com/html/features.html#computed_columns
I will extend the documentation a bit:
"
A computed column is a column whose value is calculated before
storing. The formula is evaluated when the row is inserted, and
re-evaluated every time the row is updated. One use case is to
automatically update the last-modification time:
CREATE TABLE TEST(ID INT, NAME VARCHAR, LAST_MOD TIMESTAMP AS NOW());
"
However of course using a trigger also works. Please note computed
columns are not supported by most other databases (MS SQL Server
supports them, but the behavior is different there).
One case were a trigger is required is to write an audit log / a
history of changes. But in that case you usually don't replace any
values in the row but insert a row into another table. Except if you
only keep a short history in the same table, say to remember the last
few state changes or so (I saw that in a bug-tracking tool many years
ago).
in a whole 800 page book I'm writing about persistence
Please tell me if you need someone to review.
Regards,
Thomas
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
