Can triggers execute before constraint checks?

[David Cuthbert]

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.)

[Rami Ojares]

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

[David Cuthbert]

On Jun 9, 2:30 am, Rami Ojares <rami.oja...@gmail.com> wrote:
> 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"

Ah, I missed that. Where is this bit? I've been looking in these
spots:
http://www.h2database.com/html/features.html#triggers
http://www.h2database.com/javadoc/org/h2/api/Trigger.html

and otherwise browsing the source.

> How about defining your table like this
>
> CREATE TABLE foo(
>    id NUMBER(38) PRIMARY KEY NOT NULL,
>    user VARCHAR(8) DEFAULT 'TestUser' NOT NULL
> )

Ah, hadn't thought about using a default value here to get around
this. Thanks!

[Rami Ojares]

The quote was from the Sql Grammar - CREATE TRIGGER
http://www.h2database.com/html/grammar.html#create_trigger

- rami

[Thomas Mueller]

Hi,

I will add a feature request for: "Triggers: NOT NULL checks should be
done after running triggers (Oracle behavior, maybe others)."

I don't know currently how other databases behave.

Regards,
Thomas

[Rami Ojares]

Hi,

I think before trigger should run either before all constraint checks or
after all constraint checks.
Otherwise it would be hard to keep track of what checks are done when.
Maybe if before trigger is desired to be run before there could be an an
extra trigger type BEFORE_CONSTRAINTS

- rami