Is there a database option or workaround that changed a column with default timestamp, only when a column in the row is really changed?

For example: UPDATE employees SET name = 'Mr. John' WHERE id = 1 ; That update shows in a translated log, as expected and ok: UPDATE name = 'Mr. John', my_timestamp_col = '2015-xxxxxxx...' WHERE id = 1 ;

When the same SQL update is executed another time I see this in the translated log: UPDATE my_timestamp_col = '2015-xxxxxxx...' WHERE id = 1 ;

Because the row is not changed at all the timestamp should not be set. Now we get a lot of updates that only set the timestamp column and a much more uncomitted rows.

asked 20 Oct '15, 09:59

HansTSD's gravatar image

HansTSD
1304813
accept rate: 50%


There is no option to change the behaviour of DEFAULT TIMESTAMP.

Your alternative solution would be to change the column to be DEFAULT CURRENT TIMESTAMP and then write a BEFORE UPDATE trigger that checks if any of the values are being changed, and if yes then sets the timestamp column.

permanent link

answered 20 Oct '15, 10:04

Mark%20Culp's gravatar image

Mark Culp
23.0k9129270
accept rate: 40%

edited 20 Oct '15, 10:05

Thanks!, we will think about it.

(20 Oct '15, 10:07) HansTSD

I guess you could also leave the default as DEFAULT TIMESTAMP and use a before update trigger to set the column to its previous value if all other (relevant) columns are left unchanged, something like

   ...
   referencing old as o new as n
   for each row
   begin
      if     n.col1 is not distinct from o.col1
         and n.col2 is not distinct from o.col2
         and ...
         and n.colX is not distinct from o.colX
      then
         set n.my_timestamp_col = o.my_timestamp_col;
      end if;
   end;

That might be more fitting if you usually expect "real" updates (i.e. those that do modify data).

(20 Oct '15, 10:39) Volker Barth
Replies hidden

I had thought of this solution as an alternative but I'm not sure it will work - the server might set the timestamp column after the trigger is executed. Some testing is required to check if this will work?

(20 Oct '15, 10:44) Mark Culp
1

Well, I agree, and I have not tested that (therefore my usage of "I guess you could..." and "something like").

FWIW, several years ago, Glenn had given some detailed insights (as expected...) on the internal steps a trigger has to consider - cf. that old NNTP article "Before update trigger and computed column " - I'm not sure whether it is still true (well, I'm aware that something relevant has changed with computed columns in v11), however, according to Glenn's statements, normal defaults would be evaluated before any triggers are called. I'm not sure whether DEFAULT TIMESTAMP counts as a normal default.

So yes, some testing might be required:)

(20 Oct '15, 11:13) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×6

question asked: 20 Oct '15, 09:59

question was seen: 245 times

last updated: 20 Oct '15, 11:14