"P Kishor" wrote... > On 12/20/08, jose isaias cabrera <cabr...@wrc.xerox.com> wrote: >> >> Greetings! >> >> Imagine these rows in a table named LSOpenJobs: >> >> id, PID,subject, bdate, edate, lang,job >> 1, 232,2008-01-01,2008-01-10,es,trans >> 2, 232,2008-01-01,2008-01-10,fr,trans >> 3, 232,2008-01-01,2008-01-10,it,trans >> 4, 232,2008-01-01,2008-01-10,es,val >> 5, 232,2008-01-01,2008-01-10,fr,val >> 6, 232,2008-01-01,2008-01-10,it,val >> >> What I would like to do is to create a trigger to update the bdate for >> the >> 'val' job of the same lang and same PID, with the edate of the 'trans' >> job >> of same lang and same PID. For example, in this case above, let's take >> id >> 1; the bdate for the 'val' job with the 'es' lang with the same PID, id >> 4, >> should be updated with the edate of id 1. So, the trigger should UPDATE >> the >> table to this, >> >> id, PID,subject, bdate, edate, lang,job >> 1, 232,2008-01-01,2008-01-10,es,trans >> 2, 232,2008-01-01,2008-01-10,fr,trans >> 3, 232,2008-01-01,2008-01-10,it,trans >> 4, 232,2008-01-10,2008-01-10,es,val >> 5, 232,2008-01-10,2008-01-10,fr,val >> 6, 232,2008-01-10,2008-01-10,it,val >> >> The trigger example in the site expects to change the same row. This >> UDPATE >> is based on other rows of the same table and same PID. >> >> Any help is greatly appreciated. >> > > > José, > > Consider the following > > [04:43 PM] ~$sqlite3 > SQLite version 3.5.9 > Enter ".help" for instructions > sqlite> CREATE TABLE foo (id, PID, bdate, edate, lang,job); > sqlite> INSERT INTO foo VALUES (1, > 232,'2008-01-01','2008-01-10','es','trans'); > sqlite> INSERT INTO foo VALUES (2, > 232,'2008-01-01','2008-01-10','fr','trans'); > sqlite> INSERT INTO foo VALUES (3, > 232,'2008-01-01','2008-01-10','it','trans'); > sqlite> INSERT INTO foo VALUES (4, > 232,'2008-01-01','2008-01-10','es','val'); > sqlite> INSERT INTO foo VALUES (5, > 232,'2008-01-01','2008-01-10','fr','val'); > sqlite> INSERT INTO foo VALUES (6, > 232,'2008-01-01','2008-01-10','it','val'); > sqlite> UPDATE foo SET bdate = (SELECT f2.edate FROM foo f1 JOIN foo > f2 ON f1.PID = f2.PID AND f1.lang = f2.lang WHERE f1.job = 'val' AND > f2.job = 'trans' AND f1.id = foo.id) WHERE foo.job = 'val'; > sqlite> SELECT * FROM foo; > 1|232|2008-01-01|2008-01-10|es|trans > 2|232|2008-01-01|2008-01-10|fr|trans > 3|232|2008-01-01|2008-01-10|it|trans > 4|232|2008-01-10|2008-01-10|es|val > 5|232|2008-01-10|2008-01-10|fr|val > 6|232|2008-01-10|2008-01-10|it|val > sqlite> > > The UPDATE statement above seems to do what you want. Convert that to > a TRIGGER if you so want, but realize that the TRIGGER is supposed to, > well, trigger on some event such as UPDATE or INSERT or DELETE. Other > than that, the above should get you going. >
Thanks, Puneet. I can work with this. josé _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
