AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |
Back to Blog
Sqlite update join12/30/2023 Then redirecting the actual DELETE effectively being executed.Īs you surely remember, I'd announced in the previous step that an Updatable View can even support the RW Enabled Table #2 option. This Trigger intercepts any DELETE statement affecting the View. Then redirecting the actual UPDATE effectively being executed.ĭELETE FROM "store" WHERE ROWID = OLD.ROWID This Trigger intercepts any UPDATE statement affecting the View. "st_contact" = NEW."st_contact", "st_ip_addr" = NEW."st_ip_addr", UPDATE "store" SET "st_id" = NEW."st_id", "st_name" = NEW."st_name", INSTEAD OF UPDATE OF "st_id", "st_name", "st_contact", "st_ip_addr", "hq_id", "geom" The INSERT statement effectively being executed targets the appropriate Table and not the View itself. This Trigger intercepts any INSERT statement affecting the View.Īs you can see by reading the SQL code, what actually happens simply is a It's now time to get a quick glance at few technical details supporting Updatable Views: as we've yet anticipated, all the magic is in the Triggers defined for this View: WOW it really works !Īs you can easily verify, all changes requested by the previous SQL statements are now permanently saved into the Database even if performed on behalf of a View. Now we'll execute yet another SELECT * FROM store_view in order to check. UPDATE store_view SET st_contact = 'Paulette Laloux' INSERT INTO store_view (st_id, st_name, st_contact, st_ip_addr, hq_id, geom) Let's go to verify by actually executing few SQL statements. Just a very trivial SELECT * FROM store_view there is nothing interesting to be noted here.Īnyway you can easily notice that this VIEW is supported by three Triggers and are exactly these Triggers who magically transform a read-only View into an Updatable View. we'll examine this too, but in a following step.įor now we'll simply test the simpler case. Please note well: there is yet another supported option, i.e. Please note: we have checked the RW Enabled Main Table option, so the View being created will be an Updatable VIEW. We'll simply use the Query composer dialog available on the spatialite_gui tool.įirst we'll select all required columns form both tables, and we'll set the relational JOIN connecting the two tables.Īnd finally we'll then create a Spatial View. INSERT INTO store (st_id, st_name, st_contact, st_ip_addr, hq_id, geom) SELECT AddGeometr圜olumn('store', 'geom', 4326, 'POINT', 'XY') VALUES (3, 'Paris', 'Paul Dupont', '34.17.258.259') ĬONSTRAINT fk_store_hq FOREIGN KEY (hq_id) REFERENCES headquarter (hq_id)) INSERT INTO headquarter (hq_id, hq_name, hq_manager, hq_ip_addr) Just few SQL commands useful to initialize our sample dataset: will be represented by a corresponding POINT geometry
0 Comments
Read More
Leave a Reply. |