Re: HSQLDB Trigger issue (newbie)
Andrew Richardson <[email protected]> Sat, 14 May 2022 08:25:23 -0400
| Newsgroups | gmane.comp.java.hsqldb.user |
|---|---|
| Message-ID | <CAAeTkouy6X+YQdr-=uJ+JZaAvrOrE8+0rRDXqM9q=stHsGQYdg@mail.gmail.com> |
--===============2664562603614873076== Content-Type: multipart/alternative; boundary="0000000000003e915505def7e3e9" --0000000000003e915505def7e3e9 Content-Type: text/plain; charset="UTF-8" Hi Fred Thank you for the prompt response. Using the CREATE TABLE variant is a better solution, works like a charm. Thank again, Andrew On Sat, May 14, 2022 at 4:16 AM Fred Toussi via Hsqldb-user < [email protected]> wrote: > 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 > > > _______________________________________________ > Hsqldb-user mailing list > [email protected] > https://lists.sourceforge.net/lists/listinfo/hsqldb-user > --0000000000003e915505def7e3e9 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable <div dir=3D"ltr"><div>Hi Fred</div><div>Thank you for the prompt response.= =C2=A0 Using the CREATE TABLE variant is a better solution, works like a ch= arm.<br></div><div>Thank again,</div><div>Andrew<br></div></div><br><div cl= ass=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Sat, May 14, 2= 022 at 4:16 AM Fred Toussi via Hsqldb-user <<a href=3D"mailto:hsqldb-use= [email protected]">[email protected]</a>> wrote:<b= r></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex= ;border-left:1px solid rgb(204,204,204);padding-left:1ex"><u></u><div><div = style=3D"font-family:Arial">This won't work because it repeatedly updat= es by calling the trigger (the change made in the trigger results in the fi= ring 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 t= ype=3D"cite" id=3D"gmail-m_-2955025720533562066qt"><div dir=3D"ltr"><div><d= iv><div><div id=3D"gmail-m_-2955025720533562066qt-m_7031195207257466180gmai= l-:1ph"><div id=3D"gmail-m_-2955025720533562066qt-m_7031195207257466180gmai= l-:1pg"><div dir=3D"ltr"><div><span><span style=3D"font-family:monospace">C= REATE TRIGGER "WaterResUpdMod" BEFORE UPDATE ON PUBLIC."Wate= rfrontReservations"<br>=C2=A0 =C2=A0REFERENCING NEW ROW AS newrow<br>= =C2=A0 =C2=A0FOR EACH ROW<br>=C2=A0=C2=A0 SET NEWROW."WaterfrontReserv= ations" =3D NOW();</span></span></div><div><br></div></div></div></div= ></div></div></div></div></blockquote><div style=3D"font-family: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_sql" target=3D"_blank">ht= tp://hsqldb.org/doc/2.0/guide/triggers-chapt.html#trc_trigger_action_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 styl= e=3D"font-family:Arial"><br></div><div style=3D"font-family:Arial">"Mo= dified" TIMESTAMP ON UPDATE CURRENT_TIMESTAMP<br></div><div style=3D"f= ont-family:Arial"><br></div><div style=3D"font-family:Arial">See <a href=3D= "http://hsqldb.org/doc/2.0/guide/databaseobjects-chapt.html" target=3D"_bla= nk">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">Fr= ed<br></div><div style=3D"font-family:Arial"><br></div><div style=3D"font-f= amily:Arial"><br></div><div>On Sat, May 14, 2022, at 01:10, Andrew Richards= on wrote:<br></div><blockquote type=3D"cite" id=3D"gmail-m_-295502572053356= 2066qt"><div dir=3D"ltr"><div><div><div><div id=3D"gmail-m_-295502572053356= 2066qt-m_7031195207257466180gmail-:1ph"><div id=3D"gmail-m_-295502572053356= 2066qt-m_7031195207257466180gmail-:1pg"><div dir=3D"ltr"><div>Hello<br></di= v><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 w= ith=20 current timestamp.=C2=A0 I am a newbie in this area.<br></div><div>Below is= =20 the table definition and the trigger specification.=C2=A0 My test results i= n=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 doin= g wrong?=C2=A0 Might this be a row lock issue?<br></div><div>Thanks<br></di= v><div>Andrew Richardson<br></div><div><br></div><div><span><span style=3D"= font-family:monospace">sql> \d PUBLIC."WaterfrontReservations"= <br>NAME =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0DATATYPE =C2=A0 WIDTH =C2=A0NO-N= ULLS =C2=A0PRECISION =C2=A0SCALE<br>------------ =C2=A0--------- =C2=A0----= - =C2=A0-------- =C2=A0--------- =C2=A0-----<br>ID =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0INTEGER =C2=A0 =C2=A0 =C2=A0 11 =C2=A0* =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A032<br>CustomerID =C2=A0 =C2=A0INTEGER= =C2=A0 =C2=A0 =C2=A0 11 =C2=A0* =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A032<br>Notes =C2=A0 =C2=A0 =C2=A0 =C2=A0 VARCHAR =C2=A0 =C2=A0 = =C2=A0128 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0128= <br>ResDate =C2=A0 =C2=A0 =C2=A0 DATE =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A010 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 10<br>NumPeo= ple =C2=A0 =C2=A0 TINYINT =C2=A0 =C2=A0 =C2=A0 =C2=A04 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A08<br>NumKayaks =C2=A0 =C2= =A0 TINYINT =C2=A0 =C2=A0 =C2=A0 =C2=A04 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A08<br>NumDblKayaks =C2=A0TINYINT =C2=A0 = =C2=A0 =C2=A0 =C2=A04 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A08<br>NumCanoes =C2=A0 =C2=A0 TINYINT =C2=A0 =C2=A0 =C2=A0 = =C2=A04 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A08<br>NumSUPs =C2=A0 =C2=A0 =C2=A0 TINYINT =C2=A0 =C2=A0 =C2=A0 =C2=A04 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A08<br>N= umTubes =C2=A0 =C2=A0 =C2=A0TINYINT =C2=A0 =C2=A0 =C2=A0 =C2=A04 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A08<br>NumDblTubes= =C2=A0 TINYINT =C2=A0 =C2=A0 =C2=A0 =C2=A04 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A08<br>NumCoolers =C2=A0 =C2=A0TINYI= NT =C2=A0 =C2=A0 =C2=A0 =C2=A04 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A08<br>NumDingys =C2=A0 =C2=A0 TINYINT =C2=A0 =C2= =A0 =C2=A0 =C2=A04 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A08<br>PutIn =C2=A0 =C2=A0 =C2=A0 =C2=A0 VARCHAR =C2=A0 =C2=A0 = =C2=A0100 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0100= <br>TakeOut =C2=A0 =C2=A0 =C2=A0 VARCHAR =C2=A0 =C2=A0 =C2=A0100 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0100<br>LocalInfo =C2=A0= =C2=A0 VARCHAR =C2=A0 =C2=A0 =C2=A0100 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0100<br>LocalPhone =C2=A0 =C2=A0VARCHAR =C2=A0 = =C2=A0 =C2=A0100 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0100<br>TripDate =C2=A0 =C2=A0 =C2=A0DATE =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A010 =C2=A0* =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A010<= br>TripTime =C2=A0 =C2=A0 =C2=A0TIME =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 8 = =C2=A0* =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 8<br>NumCCh= airs =C2=A0 =C2=A0TINYINT =C2=A0 =C2=A0 =C2=A0 =C2=A04 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A08<br>NumPaddles =C2=A0 =C2= =A0TINYINT =C2=A0 =C2=A0 =C2=A0 =C2=A04 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A08<br>NumPFDs =C2=A0 =C2=A0 =C2=A0 TINYINT= =C2=A0 =C2=A0 =C2=A0 =C2=A04 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A08<br>NumDays =C2=A0 =C2=A0 =C2=A0 TINYINT =C2=A0 = =C2=A0 =C2=A0 =C2=A04 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A08<br>BookType =C2=A0 =C2=A0 =C2=A0VARCHAR =C2=A0 =C2=A0 = =C2=A0 12 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 12= <br>Cancelled =C2=A0 =C2=A0 BOOLEAN =C2=A0 =C2=A0 =C2=A0 =C2=A05<br>OfficeN= otes =C2=A0 VARCHAR =C2=A0 =C2=A0 =C2=A0128 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0128<br>Modified =C2=A0 =C2=A0 =C2=A0TIMESTAM= P =C2=A0 =C2=A0 26 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 26 =C2=A0 =C2=A0 =C2=A06</span></span></div><div><span><span style= =3D"font-family:monospace"></span></span><br></div><div><span><span style= =3D"font-family:monospace">sql> CREATE TRIGGER "WaterResUpdMod"= ; AFTER UPDATE ON PUBLIC."WaterfrontReservations"<br>=C2=A0 =C2= =A0REFERENCING NEW ROW AS newrow<br>=C2=A0 =C2=A0FOR EACH ROW<br>=C2=A0 =C2= =A0UPDATE PUBLIC."WaterfrontReservations" SET "Modified"= ; =3D NOW();</span></span></div><div><span><span style=3D"font-family:monos= pace"></span></span><br></div><div><span><span style=3D"font-family:monospa= ce">***TEST***</span></span><br></div><div><span><span style=3D"font-family= :monospace"><div>sql> UPDATE PUBLIC."WaterfrontReservations" S= ET "CustomerID" =3D 5289 where ID =3D 4234;<br></div><div>SEVERE = =C2=A0SQL Error at '<stdin>' line 60:<br></div><div>"UPD= ATE 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><di= v><br></div><div>_______________________________________________<br></div><= div>Hsqldb-user mailing list<br></div><div><a href=3D"mailto:Hsqldb-user@li= sts.sourceforge.net" target=3D"_blank">[email protected]</a= ><br></div><div><a href=3D"https://lists.sourceforge.net/lists/listinfo/hsq= ldb-user" target=3D"_blank">https://lists.sourceforge.net/lists/listinfo/hs= qldb-user</a><br></div><div><br></div></blockquote><div style=3D"font-famil= y:Arial"><br></div></div>_______________________________________________<br= > Hsqldb-user mailing list<br> <a href=3D"mailto:[email protected]" target=3D"_blank">Hsql= [email protected]</a><br> <a href=3D"https://lists.sourceforge.net/lists/listinfo/hsqldb-user" rel=3D= "noreferrer" target=3D"_blank">https://lists.sourceforge.net/lists/listinfo= /hsqldb-user</a><br> </blockquote></div> --0000000000003e915505def7e3e9-- --===============2664562603614873076== Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Disposition: inline --===============2664562603614873076== 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 --===============2664562603614873076==--