Re: [dba-dev] triggers
On 10/19/09 20:30, Andrew Jensen wrote: Hi Ocke, What you think I actually read that SQL statement before executing itcome 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, x1 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
Re: [dba-dev] triggers
One last comment - This is what I think the trigger should look like: CREATE TRIGGER category_last_upate BEFORE UPDATE ON category REFERENCING new ROW AS newrow FOR EACH ROW set newrow.last_update = CURRENT_TIMESTAMP but it seems to do nothing. Anyway - I sent this along as a question to the HSQL-users mailing list, hopefully someone can show me what I'm overlooking here. Drew - To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org For additional commands, e-mail: dev-h...@dba.openoffice.org
Re: [dba-dev] triggers
Hi Ocke, What you think I actually read that SQL statement before executing itcome 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. -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
Re: [dba-dev] triggers
Hi Drew, On 10/18/09 08:40, Drew Jensen wrote: Drew Jensen wrote: Right now then I'll go check the trigger statement against a stand alone HSQLdb engine...and then maybe use the HSQLdb supplied sample for triggers in the Base file (Table and Trigger def). Ok - so it is my trigger statement. Tried the supplied trigger definition in the hsql testrun file, it works just fine under Base. [Almost - ] I added a PK to the test table so that the table can be edited in a dataview. The first 2 times a record is added to the TEST the trigger fails to fire. *note*..for me, if I insert data via the SQL window only the first insert fails...LOL..I love software. It's strange that it works for the first time when inserting it by SQL. That the 2 first inserts doesn't trigger inserts into LOG is correct. When you have a look at the table in script you'll see that the increment starts at zero and the trigger is called when id > 1. So id=0 and id=1 doesn't call the trigger. then l - there is a problem when I can run a statement, Base says it runs successfully and then creates an unusable script file. Could you send me the sql statement to reproduce it. All I could finger out so far is that when the database is read-only it has a problem with the trigger statement. I'll notify the developer of hsqldb. - oj *smile*...if nothing else an issue BTKATC... Anyway I'm attaching the Base file with the AFTER INSERT trigger on the TEST table, after insert a copy is placed in the log table...least that is the idea. Drew - 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, x1 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
[dba-dev] triggers (was: Re: 1.9 feature support? was ( HSQLDB 1.9 Testing needed ))
Drew Jensen wrote: Right now then I'll go check the trigger statement against a stand alone HSQLdb engine...and then maybe use the HSQLdb supplied sample for triggers in the Base file (Table and Trigger def). Ok - so it is my trigger statement. Tried the supplied trigger definition in the hsql testrun file, it works just fine under Base. [Almost - ] I added a PK to the test table so that the table can be edited in a dataview. The first 2 times a record is added to the TEST the trigger fails to fire. *note*..for me, if I insert data via the SQL window only the first insert fails...LOL..I love software. then l - there is a problem when I can run a statement, Base says it runs successfully and then creates an unusable script file. *smile*...if nothing else an issue BTKATC... Anyway I'm attaching the Base file with the AFTER INSERT trigger on the TEST table, after insert a copy is placed in the log table...least that is the idea. Drew Trigger_testrun.odb Description: application/vnd.oasis.opendocument.database - To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org For additional commands, e-mail: dev-h...@dba.openoffice.org
