I have some table :

create table people
(  
    id             bigint        not null    default autoincrement,
    first_name     varchar(50)   not null,
    last_name      varchar(50)   not null,
    gender         char(1)       not null    default 'M', 
    last_update    timestamp     not null    default timestamp  
);

What happens in a replicating environment if remote user 1 updates a row, and then replicates the change to the main site? Does the last_update get updated AGAIN at the main site when the change arrives and is processed?

Please tell me it doesn't.

asked 27 Jan '10, 18:35

Calvin%20Allen's gravatar image

Calvin Allen
1.5k232638
accept rate: 25%

edited 27 Jan '10, 19:39

Your example is somewhat lacking a "real" column: You wouldn't want to update the id (as a PK field) in a SQL Remote setup, otherwise last_update wouldn't be the problem:)

(27 Jan '10, 18:48) Volker Barth

Example updated.

(27 Jan '10, 19:40) Calvin Allen

Calvin,

it doesn't.

When you look at the SQL statements that are sent to the other side (by running DBREMOTE -v -o C:\┬┤MyLog.txt) you will notice that the receiving database gets a complete statement with the *last_update* column set. And the particular DEFAULT TIMESTAMP default will only be applied when the column's value is not set explicetely. So no need to worry, we have used this DEFAULT in a SQL Remote setup for years.


As to your new example: If the remote exeucutes the following SQL statement (say, in a client app):

UPDATE people SET last_name = 'Smith' WHERE id = 123450000

then last_update will be set automatically to current timestamp (say, to '2010-01-28 11:24:23.123456'). The transaction log will then contain something like (you could check by using DBTRAN):

UPDATE people SET last_name = 'Smith', last_update = '2010-01-28 11:24:23.123456'  WHERE id = 123450000

And it's that statement that is sent to the consolidated. Obviously, the statement contains a value for the DEFAULT TIMESTAMP column, so it doesn't update *last_update* again.

permanent link

answered 27 Jan '10, 18:45

Volker%20Barth's gravatar image

Volker Barth
29.9k294446654
accept rate: 32%

edited 28 Jan '10, 10:03

Awesome, thanks! We're just looking to implement this default and had concerns of the affects replication might bring.

(27 Jan '10, 18:48) Calvin Allen

Just to add: We have included 3 columns in all published tables: one "dtCreated" with DEFAULT CURRENT TIMESTAMP. one "dtLastUpdated" with DEFAULT TIMESTAMP and one "origin" with DEFAULT CURRENT PUBLISHER. That makes tracking the source of inserts/updates quite easy.

(28 Jan '10, 10:06) 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:

×113
×75
×37
×9

question asked: 27 Jan '10, 18:35

question was seen: 963 times

last updated: 28 Jan '10, 10:03