Is it possible to insert a row but only if a value has changed?

Question

I've already read this:

How do I insert into a database only if a value has changed?

So my question is very close, but it's not exaclty the same.

I have a table with 4 "main" columns (ie I have those 4 same columns on all my tables):

id - int - autoinc
id_origin - int - id when the first record was created
date_v_start - datetime - start date of validity of the record
date_v_end - datetime - end of validity of the record (if NULL it's the current 'valid' record)

Here are some other fields specific to this table: - summary - text - detail - text

knowing this, when I have something that has been changed into that table, here's what I do:

I update date_v_end of the current record to "now()"
I insert a new record, with date_v_start = date_v_end of the previous, id_origin = the same of the previous, - date_v_end = NULL and (of course) the new values of summary and detail.

My problem is that if the values of summary and detail haven't changed I don't want to insert that new record, it's a waste of time, and hard drive space.

Is there a way to do (in one shot if possible) something like:

"if summary<>'a value' or detail<>'another value' then update this record and insert this new one"

?

So, the difference between my question and the link I've provided above is: - there are more than one field to compare - there are two requests - i'd like to make it in one request if possible.

You can either write the logic in your app, use a stored procedure, or use a trigger. Which of these would be OK? — Jon, Apr 21 '12 at 19:07
maybe use a trigger, or use a stored procedure. I'd like to be compatible if possible with both `mysql` and `postgresql` — Olivier Pons, Apr 21 '12 at 19:08

score 0 · Accepted Answer · answered Apr 27 '12 at 09:17

Here's how I did it in one "php query" = one string with commas in the middle (it's very time consuming, but I'll change it one day if my webserver can't handle it anymore):

CREATE TEMPORARY TABLE tmp
SELECT * FROM table
WHERE id=:id;
ALTER TABLE tmp drop ID;
INSERT INTO table
SELECT 0,tmp.* FROM tmp;
DROP TABLE tmp:
UPDATE table
SET date_v_fin=NOW()
WHERE id=LAST_INSERT_ID();

Is it possible to insert a row but only if a value has changed?

1 Answers1