Re: HSQLDB Trigger issue (newbie)
Fred Toussi via Hsqldb-user <[email protected]> Sat, 14 May 2022 09:15:14 +0100
| Newsgroups | gmane.comp.java.hsqldb.user |
|---|---|
| Message-ID | <[email protected]> |
--===============7369188259147182308== Content-Type: multipart/alternative; boundary=130ae280a43c40ffab18c1235fa880f0 --130ae280a43c40ffab18c1235fa880f0 Content-Type: text/plain This won't work because it repeatedly updates by calling the trigger (the change made in the trigger results in the firing of the same trigger). This kind of change is done with a BEFORE UPDATE trigger. > CREATE TRIGGER "WaterResUpdMod" BEFORE UPDATE ON PUBLIC."WaterfrontReservations" > REFERENCING NEW ROW AS newrow > FOR EACH ROW > SET NEWROW."WaterfrontReservations" = NOW(); > See http://hsqldb.org/doc/2.0/guide/triggers-chapt.html#trc_trigger_action_sql Alternatively, you can define the "Modified" table column as automatically updated in the CREATE TABLE statement. "Modified" TIMESTAMP ON UPDATE CURRENT_TIMESTAMP See http://hsqldb.org/doc/2.0/guide/databaseobjects-chapt.html Fred On Sat, May 14, 2022, at 01:10, Andrew Richardson wrote: > Hello > Using: HSQLDB 2.6.1 > I am trying to create a trigger such that when a row is changed, regardless of the client, the table "Modified" field to updated with current timestamp. I am a newbie in this area. > Below is the table definition and the trigger specification. My test results in the SQLTool session "hanging" for a short while (~30 seconds) and then a General error is reported. > Can someone advise what I am doing wrong? Might this be a row lock issue? > Thanks > Andrew Richardson > > sql> \d PUBLIC."WaterfrontReservations" > NAME DATATYPE WIDTH NO-NULLS PRECISION SCALE > ------------ --------- ----- -------- --------- ----- > ID INTEGER 11 * 32 > CustomerID INTEGER 11 * 32 > Notes VARCHAR 128 128 > ResDate DATE 10 10 > NumPeople TINYINT 4 8 > NumKayaks TINYINT 4 8 > NumDblKayaks TINYINT 4 8 > NumCanoes TINYINT 4 8 > NumSUPs TINYINT 4 8 > NumTubes TINYINT 4 8 > NumDblTubes TINYINT 4 8 > NumCoolers TINYINT 4 8 > NumDingys TINYINT 4 8 > PutIn VARCHAR 100 100 > TakeOut VARCHAR 100 100 > LocalInfo VARCHAR 100 100 > LocalPhone VARCHAR 100 100 > TripDate DATE 10 * 10 > TripTime TIME 8 * 8 > NumCChairs TINYINT 4 8 > NumPaddles TINYINT 4 8 > NumPFDs TINYINT 4 8 > NumDays TINYINT 4 8 > BookType VARCHAR 12 12 > Cancelled BOOLEAN 5 > OfficeNotes VARCHAR 128 128 > Modified TIMESTAMP 26 26 6 > > sql> CREATE TRIGGER "WaterResUpdMod" AFTER UPDATE ON PUBLIC."WaterfrontReservations" > REFERENCING NEW ROW AS newrow > FOR EACH ROW > UPDATE PUBLIC."WaterfrontReservations" SET "Modified" = NOW(); > > ***TEST*** > sql> UPDATE PUBLIC."WaterfrontReservations" SET "CustomerID" = 5289 where ID = 4234; > SEVERE SQL Error at '<stdin>' line 60: > "UPDATE PUBLIC."WaterfrontReservations" SET "CustomerID" = 5289 where ID = 4234" > General error > > > _______________________________________________ > Hsqldb-user mailing list > [email protected] > https://lists.sourceforge.net/lists/listinfo/hsqldb-user > --130ae280a43c40ffab18c1235fa880f0 Content-Type: text/html Content-Transfer-Encoding: quoted-printable <!DOCTYPE html><html><head><title></title><style type=3D"text/css">p.Mso= Normal,p.MsoNoSpacing{margin:0}</style></head><body><div style=3D"font-f= amily:Arial;">This won't work because it repeatedly updates by calling t= he trigger (the change made in the trigger results in the firing of the = same trigger). This kind of change is done with a BEFORE UPDATE trigger.= <br></div><div style=3D"font-family:Arial;"><br></div><blockquote type=3D= "cite" id=3D"qt" style=3D""><div dir=3D"ltr"><div><div><div><div id=3D"q= t-m_7031195207257466180gmail-:1ph"><div id=3D"qt-m_7031195207257466180gm= ail-:1pg"><div dir=3D"ltr"><div><span style=3D""><span class=3D"font" st= yle=3D"font-family:monospace;">CREATE TRIGGER "WaterResUpdMod" BEFORE UP= DATE ON PUBLIC."WaterfrontReservations"<br> REFERENCING NEW = ROW AS newrow<br> FOR EACH ROW<br> SET NEWROW."W= aterfrontReservations" =3D NOW();</span></span></div><div><br></div></di= v></div></div></div></div></div></div></blockquote><div style=3D"font-fa= mily:Arial;"><br></div><div style=3D"font-family:Arial;">See <a href=3D"= http://hsqldb.org/doc/2.0/guide/triggers-chapt.html#trc_trigger_action_s= ql">http://hsqldb.org/doc/2.0/guide/triggers-chapt.html#trc_trigger_acti= on_sql</a></div><div style=3D"font-family:Arial;"><br></div><div style=3D= "font-family:Arial;">Alternatively, you can define the "Modified" table = column as automatically updated in the CREATE TABLE statement.<br></div>= <div style=3D"font-family:Arial;"><br></div><div style=3D"font-family:Ar= ial;">"Modified" TIMESTAMP ON UPDATE CURRENT_TIMESTAMP<br></div><div sty= le=3D"font-family:Arial;"><br></div><div style=3D"font-family:Arial;">Se= e <a href=3D"http://hsqldb.org/doc/2.0/guide/databaseobjects-chapt.html"= >http://hsqldb.org/doc/2.0/guide/databaseobjects-chapt.html</a></div><di= v style=3D"font-family:Arial;"><br></div><div style=3D"font-family:Arial= ;">Fred<br></div><div style=3D"font-family:Arial;"><br></div><div style=3D= "font-family:Arial;"><br></div><div>On Sat, May 14, 2022, at 01:10, Andr= ew Richardson wrote:<br></div><blockquote type=3D"cite" id=3D"qt" style=3D= ""><div dir=3D"ltr"><div><div><div><div id=3D"qt-m_7031195207257466180gm= ail-:1ph"><div id=3D"qt-m_7031195207257466180gmail-:1pg"><div dir=3D"ltr= "><div>Hello<br></div><div>Using: HSQLDB 2.6.1<br></div><div>I am trying to create a trigger such that when a row is changed,=20 regardless of the client, the table "Modified" field to updated with=20 current timestamp. I am a newbie in this area.<br></div><div>Below= is=20 the table definition and the trigger specification. My test result= s in=20 the SQLTool session "hanging" for a short while (~30 seconds) and then a General error is reported.<br></div><div>Can someone advise what I am d= oing wrong? Might this be a row lock issue?<br></div><div>Thanks<b= r></div><div>Andrew Richardson<br></div><div><br></div><div><span style=3D= ""><span class=3D"font" style=3D"font-family:monospace;">sql> \d PUBL= IC."WaterfrontReservations"<br>NAME DA= TATYPE WIDTH NO-NULLS PRECISION SCALE<br>------= ------ --------- ----- -------- --------- = -----<br>ID INTEGER &nbs= p; 11 * &n= bsp;32<br>CustomerID INTEGER 11 = * 32<br>Notes &nb= sp; VARCHAR 128 &= nbsp; 128<br>ResDate &nb= sp; DATE 10  = ; 10<br>NumPeople  = ; TINYINT 4  = ; 8<br>NumKayaks TINYINT= 4 = 8<br>NumDblKayaks TINYINT = 4 = 8<br>NumCanoes TINYINT &= nbsp;4 &n= bsp;8<br>NumSUPs TINYINT  = ;4 = 8<br>NumTubes TINYINT 4 &= nbsp; 8<br= >NumDblTubes TINYINT 4 &= nbsp; 8<br>NumCoolers &n= bsp; TINYINT 4 &nb= sp; 8<br>NumDingys  = ; TINYINT 4  = ; 8<br>PutIn &nbs= p; VARCHAR 100 &n= bsp; 100<br>TakeOut VARCHAR &nb= sp; 100 &n= bsp; 100<br>LocalInfo VARCHAR 10= 0 100<br>L= ocalPhone VARCHAR 100 &nb= sp; 100<br>TripDate &nbs= p; DATE 10 * = 10<br>TripTime &= nbsp;TIME 8 * &nb= sp; 8<br>NumCChairs TINY= INT 4 &nbs= p; 8<br>NumPaddles TINYINT  = ; 4 = 8<br>NumPFDs TINYINT &n= bsp; 4 &nb= sp; 8<br>NumDays TINYINT = 4 = 8<br>BookType VARCHAR &nb= sp; 12 12= <br>Cancelled BOOLEAN 5<br>Offi= ceNotes VARCHAR 128 &nbs= p; 128<br>Modified  = ;TIMESTAMP 26 &n= bsp; 26 6</span></span></div><div><spa= n style=3D""><span class=3D"font" style=3D"font-family:monospace;"></spa= n></span><br></div><div><span style=3D""><span class=3D"font" style=3D"f= ont-family:monospace;">sql> CREATE TRIGGER "WaterResUpdMod" AFTER UPD= ATE ON PUBLIC."WaterfrontReservations"<br> REFERENCING NEW R= OW AS newrow<br> FOR EACH ROW<br> UPDATE PUBLIC.= "WaterfrontReservations" SET "Modified" =3D NOW();</span></span></div><d= iv><span style=3D""><span class=3D"font" style=3D"font-family:monospace;= "></span></span><br></div><div><span style=3D""><span class=3D"font" sty= le=3D"font-family:monospace;">***TEST***</span></span><br></div><div><sp= an style=3D""><span class=3D"font" style=3D"font-family:monospace;"><div= >sql> UPDATE PUBLIC."WaterfrontReservations" SET "CustomerID" =3D 528= 9 where ID =3D 4234;<br></div><div>SEVERE SQL Error at '<stdin&= gt;' line 60:<br></div><div>"UPDATE PUBLIC."WaterfrontReservations" SET = "CustomerID" =3D 5289 where ID =3D 4234"<br></div><div>General error<br>= </div><div><br></div></span></span></div></div></div></div></div></div><= /div></div><div><br></div><div>_________________________________________= ______<br></div><div>Hsqldb-user mailing list<br></div><div><a href=3D"m= ailto:[email protected]">[email protected]= et</a><br></div><div><a href=3D"https://lists.sourceforge.net/lists/list= info/hsqldb-user">https://lists.sourceforge.net/lists/listinfo/hsqldb-us= er</a><br></div><div><br></div></blockquote><div style=3D"font-family:Ar= ial;"><br></div></body></html> --130ae280a43c40ffab18c1235fa880f0-- --===============7369188259147182308== Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Disposition: inline --===============7369188259147182308== Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Disposition: inline _______________________________________________ Hsqldb-user mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/hsqldb-user --===============7369188259147182308==--