Detect if a row was modified by an INSERT OR REPLACE?

Question

Say we have a table like:

CREATE TABLE example (
animal VARCHAR NOT NULL,
color VARCHAR NOT NULL,
name VARCHAR,
UNIQUE(colA, colB))

And we then do command like:

INSERT OR REPLACE INTO example (animal, color, name)
VALUES ("animal", "cat", "mittens")

Is there a way to tell if the value of the entire row changed at all? For example, if before this command, the table had the following row:

animal = "cat", color = "orange", name = NULL

It would have changed, since name changed.

But if the row before the command was already:

animal = "cat", color = "orange", name = "mittens"

Then there would be no changes.

Sadly, it seems that the changes() function returns 1 in both of these cases. Is there something that would indicate that some value actually changed, or what column(s) changed?

You would need to write a trigger to calculate this information for each table. — Gordon Linoff, May 22 '17 at 12:04
Possible reference/duplicate for : http://stackoverflow.com/questions/6560000/sql-selecting-rows-where-column-value-changed-from-previous-row — Prabhat G, May 22 '17 at 12:15

score 0 · Answer 1 · answered May 22 '17 at 12:30

0

An INSERT commands does not return any information, except for the rowid of the new row, which does not help here.

So you have to do a SELECT to get the old values.

answered May 22 '17 at 12:30

CL.

173,858
17
217
259

Detect if a row was modified by an INSERT OR REPLACE?

1 Answers1