Search code examples
sqldatabasesql-updatederbytriggers

Derby Trigger on UPDATE: how to update a timestamp?

Short: How to create a trigger on a Apache Derby DB which updates the timestamps on changed records?

Adapting an existing project to use Apache Derby as database software, I have encountered several problems. Most I could solve using stackoverflow and a search engine, but for this one I ask your help.

With other DB software, I'd be doing something like this:

CREATE TABLE myTable (
…
`lastchange` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
…
)

Derby doesn't like ` and you have to create triggers separately. So I try this:

CREATE TRIGGER updateTimestamp AFTER UPDATE ON mytable FOR EACH ROW
(UPDATE mytable SET lastchange= CURRENT_TIMESTAMP() )

and I get an error. Oh, this update might cause the trigger to go off again... so

CREATE TRIGGER updateTimestamp NO CASCADE BEFORE UPDATE ON mytable FOR EACH ROW
(UPDATE mytable SET lastchange= CURRENT_TIMESTAMP() )

would be better, right?

Well, either gives me an error telling Derby doesn't like the second "UPDATE" in this statement. But I don't want to insert or delete anything, I want to update the timestamp on the rows that have been changed/will be changed.

How to do that?

------ Update ------

When there is no logical solution, trying some magic may help.

I removed the brackets (even though the Derby documentation and examples had them).

Derby tells me it doesn't like BEFORE on UPDATE triggers (wtf?), so I went back to AFTER, assuming Derby knows what it is doing there.

And finally, while CURRENT_TIMESTAMP is a function, Derby objected to the brackets, so I removed them as well. Looks weird, but if that's what Derby wants...

CREATE TRIGGER updateTimestamp AFTER UPDATE ON votes FOR EACH ROW UPDATE 
votes SET lastvoted = CURRENT_TIMESTAMP

Now this was accepted by Apache Derby. But executing it caused a trigger loop, as was to be expected with AFTER. So, still no solution.

How is this done? It's so simple on other Databases. There's got to be a solution for Derby, too.

Solution

  • To be precise, you have to specify every column in the table that you want to listen for updates (obviously excluding the lastvoted column).

    CREATE TRIGGER updateTimestamp
AFTER UPDATE OF col1, col2, col3... ON votes
REFERENCING OLD AS EXISTING
FOR EACH ROW MODE DB2SQL
    UPDATE votes SET lastvoted = CURRENT_TIMESTAMP
    WHERE pkcolumn = EXISTING.pkcolumn