The documentation clearly states that
"'BEFORE' triggers are called after data conversion is made, default
values are set, null and length constraint checks have been made"
How about defining your table like this
CREATE TABLE foo(
id NUMBER(38) PRIMARY KEY NOT NULL,
user VARCHAR(8) DEFAULT 'TestUser' NOT NULL
)
- rami
On 9.6.2010 7:56, David Cuthbert wrote:
I've searched around a bit on both the groups and the wider web, but
haven't seen this question posed; apologies if I've missed something.
I have a table which contains audit columns which, on the Oracle side,
are populated by a trigger. Logically, these columns should always be
populated; thus, we put NOT NULL constraints on them. The H2 DDL
looks like:
CREATE TABLE foo(
id NUMBER(38) PRIMARY KEY NOT NULL,
user VARCHAR(8) NOT NULL)
CREATE TRIGGER audit_foo
BEFORE INSERT
ON foo
FOR EACH ROW
CALL "org.kanga.h2.AuditTrigger"
AuditTrigger just blindly injects a dummy username into the Object[]
newRow values in its fire() method:
public void fire(Connection conn, Object[] oldRow, Object[]
newRow)
throws SQLException
{
newRow[1] = "TestUser";
}
Alas, H2 rejects any insertion without user:
INSERT INTO foo(id) VALUES(1)
NULL not allowed for column "USER"; SQL statement:
insert into foo(id) values(1) [90006-136] 90006/90006 (Help)
org.h2.jdbc.JdbcSQLException: NULL not allowed for column "USER"; SQL
statement:
insert into foo(id) values(1) [90006-136]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:
327)
at org.h2.message.DbException.get(DbException.java:167)
at org.h2.message.DbException.get(DbException.java:144)
at org.h2.table.Column.validateConvertUpdateSequence(Column.java:
294)
at org.h2.table.Table.validateConvertUpdateSequence(Table.java:
608)
...
If I drop the NOT NULL on user, it works as expected:
INSERT INTO foo(id) VALUES(1);
COMMIT;
SELECT * FROM foo;
ID USER
1 TestUser
I've also tried "SET MODE Oracle" to try to get the Oracle behavior,
to no avail.
Any ways around this short of dropping the NOT NULL constraint? (Note
that this workaround is not a big deal for me; I'm just using H2 as a
base for our unit tests, and it's been wonderful in this regard.)
--
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.
