Actually, I really need to use MERGE INTO in this case. 1. This table is used by an audit trigger. As such, it will get invoked multiple times in the same transaction.
2. The first time the trigger is invoked, I want it to insert a new row with the current TRANSACTION_ID() and time. 3. The subsequent times the trigger is invoked, I expect it to return the existing "id" (I invoke Statement.getGeneratedKeys() to that end) without altering "uuid" or "time". I'm fairly sure my existing design alters "time" the subsequent times the trigger is invoked. How do I fix both these problems? Thanks, Gili On Jun 8, 8:45 pm, cowwoc <[email protected]> wrote: > Hi, > > When I execute: > > MERGE INTO audit_transaction_ids (uuid, time) KEY(id) VALUES > (TRANSACTION_ID(), NOW()) > > on the following schema: > > CREATE TABLE audit_transaction_ids (id IDENTITY PRIMARY KEY, uuid > VARCHAR UNIQUE NOT NULL, `time` TIMESTAMP NOT NULL); > > I get this error: > > org.h2.jdbc.JdbcSQLException: Column "ID" contains null values; SQL > statement: > MERGE INTO audit_transaction_ids (uuid, time) KEY(id) VALUES > (TRANSACTION_ID(), NOW()) [90081-155] > > In the above case the MERGE operation should always insert. Granted I > can (and will) replace it with an INSERT statement but I'm curious: > why is this failing? What is the correct way of invoking MERGE INTO > for a table containing an IDENTITY or AUTO_INCREMENT column? > > Thanks, > Gili -- 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.
