... OK - my mistake - UPDATE is a row trigger and since I did not have a FOR EACH ROW it will be a statement trigger by default. Adding FOR EACH ROW did the trick : I.e.

            s.execute("CREATE TRIGGER A_TRIGGER AFTER UPDATE OF NAME, REMARK ON " + testTable1 + " REFERENCING OLD AS UPDATEDROW FOR EACH ROW UPDATE " + testTable1 + " SET LAST_UPDATED=CURRENT_TIMESTAMP WHERE ID=UPDATEDROW.ID");


B-)

On Feb 13, 2008 10:40 PM, bruehlicke <bruehlicke@gmail.com> wrote:
OK new to this and I have tried quite a lot of permutations but cannot get it work.

The following gives ERROR 42Y92: STATEMENT triggers may only reference table transition variables/tables.

Any idea what is wrong in my TRIGGER creation ?


String testTable1 = "A";
Statement s = _connection.createStatement();

s.execute("CREATE TABLE " + testTable1 + "( ID INT NOT NULL PRIMARY KEY NOT NULL, NAME VARCHAR(80), REMARK VARCHAR(80), LAST_UPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP)");

s.execute("CREATE TRIGGER A_TRIGGER AFTER UPDATE OF NAME, REMARK ON " + testTable1 + " REFERENCING OLD AS UPDATEDROW UPDATE " + testTable1 + " SET LAST_UPDATED=CURRENT_TIMESTAMP WHERE ID=UPDATEDROW.ID");

B-)