On 10/19/09 20:30, Andrew Jensen wrote:
Hi Ocke,
What you think I actually read that SQL statement before executing
it....come on it was late. Yes, the tigger in the sample does work
correctly. Great 2 tick marks..
;-)
Ok - the bad trigger.
First - my statement is wrong...but here it is.
Create a new base file.
Open the SQL window
Apply these two statements - separately and without closing the SQL
window in between:
CREATE TABLE category (
category_id IDENTITY PRIMARY KEY,
name VARCHAR(25) NOT NULL,
last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
)
*execute succeeds*
CREATE TRIGGER category_last_upate
BEFORE UPDATE ON category
REFERENCING NEW ROW AS newrow OLD ROW AS oldrow
FOR EACH ROW SET newrow.last_update = CURRENT_TIMESTAMP
*execute succeeds*
Ok now you can close the SQL window, add data to the table, etc. The
trigger doesn't work, but doesn't complain either.
Close the file.
Try to reopen - error in the Create trigger statement - my guess is
because I declared an OLDROW variable and never used it.
I fixed the problem and send a patch to hsqldb.
The problem is in the script file. Hsqldb writes a comma between old row
and new row which is wrong.
A new upload of hsqldb.jar can be found on qa-upload.
-oj
-or-
maybe it is the use of the set keyword..but, if I use this (also
wrong) trigger statement
CREATE TRIGGER category_last_upate
BEFORE UPDATE ON category
REFERENCING NEW ROW AS newrow
FOR EACH ROW SET newrow.last_update = CURRENT_TIMESTAMP
It is accepted, doesn't work, but doesn't stop the HSQLdb engine from
restarting next time it's opened.
Attaching a file created under Win 7, just now with the above steps.
Drew,
ps Ctrl-C instead of screen shots...lol...like I said it was late..
------------------------------------------------------------------------
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org
For additional commands, e-mail: dev-h...@dba.openoffice.org
--
Ocke Janssen Tel: +49 40 23646 661, x66661
Dipl. Inf(FH) Fax: +49 40 23646 550
Sun Microsystems Inc.
Nagelsweg 55 mailto:ocke.jans...@sun.com
D-20097 Hamburg http://www.sun.com/staroffice
Sitz der Gesellschaft:
Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten
Amtsgericht Muenchen: HRB 161028
Geschaeftsfuehrer: Thomas Schr?der, Wolfgang Engels, Wolf Frenkel
Vorsitzender des Aufsichtsrates: Martin Haering