Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>One solution is to create a view for the <code>locations</code> table with a INSTEAD OF trigger on the view, then insert into the view. Here's what that would look like:</p> <p>View:</p> <pre><code>CREATE VIEW locations_view AS SELECT * FROM locations; </code></pre> <p>Trigger:</p> <pre><code>CREATE TRIGGER update_location INSTEAD OF INSERT ON locations_view FOR EACH ROW BEGIN INSERT OR REPLACE INTO locations (_id, name, latitude, longitude) VALUES ( COALESCE(NEW._id, (SELECT _id FROM locations WHERE latitude = NEW.latitude AND longitude = NEW.longitude)), NEW.name, NEW.latitude, NEW.longitude ); END; </code></pre> <p>Instead of inserting into the <code>locations</code> table, you insert into the <code>locations_view</code> view. The trigger will take care of providing the correct <code>_id</code> value by using the sub-select. If, for some reason, the insert already contains an <code>_id</code> the <code>COALESCE</code> will keep it and override an existing one in the table.</p> <p>You'll probably want to check how much the sub-select affects performance and compare that to other possible changes you could make, but it does allow you keep this logic out of your code.</p> <p>I tried some other solutions involving triggers on the table itself based on INSERT OR IGNORE, but it seems that BEFORE and AFTER triggers only trigger if it will actually insert into the table.</p> <p>You might find <a href="https://stackoverflow.com/a/4330694/857618">this answer</a> helpful, which is the basis for the trigger.</p> <p>Edit: Due to BEFORE and AFTER triggers not firing when an insert is ignored (which could then have been updated instead), we need to rewrite the insert with an INSTEAD OF trigger. Unfortunately, those don't work with tables - we have to create a view to use it.</p>
    singulars
    1. This table or related slice is empty.
    1. POAndroid/SQLite: Insert-Update table columns to keep the identifier
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USblazeroni
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI tested it and it works as far as I can see. Though, I do not understand what the value of `NEW.id` would be at an `INSERT`. I wonder, if it could be `NULL` instead. But this would contradict with that `_id` cannot be `NULL`?! And, why did you include `OR REPLACE` - is it just necessary in order to use `COALESCE`?
      singulars
      1. PO
      1. USJJD
    2. COCould you please add to your answer why is it necessary to **create a view** for the table to work on?
      singulars
      1. PO
      1. USJJD
    3. CODue to the change that each `INSERT` now runs on the `VIEW` I can no longer use **another trigger** that limits the number of rows. [Here](http://stackoverflow.com/questions/11818828/before-insert-trigger-on-a-view) is the problem.
      singulars
      1. PO
      1. USJJD
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload