... 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 <[EMAIL PROTECTED]> 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-)
>
>
