SQL Anywhere database - Is there a clean way of knowing which rows have changed?

Author

Is there a clean way of knowing which rows have changed?

Edgard L. Riba

2005-07-08, 9:23 am

Hi,

I would like to implement a table that contains all rows that have changed.

The table would be something like:

CREATE TABLE dbChangedRows(
tableName CHAR(40),
changeDate DATE,
changePK INT, --PK of changed row
PRIMARY KEY (changeDate,tableNam
e,changePK),
)

And every time a record is changed, this table gets updated.

An alternative would be to add a "changeDate" column to all the tables, and
update that every time a record is changed. Doesn't seem like the ideal
solution, but would work more-or-less for what I need.

The first and obvious solution would be to create a trigger for each table
to do this. But this is such an ugly solution (the list of triggers on
Sybase Central the list of triggers would be unmanageable) that I hope there
is a more elegant/clean way of doing this...

Reading the transaction log seems out of the question....

Is there an elegant way of doing something like this?

Thanks in advance,
Edgard

Glenn Paulley

2005-07-08, 11:23 am

You haven't specified enough detail of your requirements for me to give
an informed answer.

Adding a "last changed" column to a table doesn't give you the old values
of the row, whereas with a "shadow" table you would have the keys of the
rows that have been altered (and possbily all of the other columns as
well). So:

- do you require saving the old values?
- does this information need to be "on-line" (ie. do you need to *query*
it) - or do you need to (simply) generate a log of changes for offline
audit?

Glenn

"Edgard L. Riba" <elriba at rimith dot com> wrote in
news:42ce92ca$1@foru
ms-2-dub:

> Hi,
>
> I would like to implement a table that contains all rows that have
> changed.
>
> The table would be something like:
>
> CREATE TABLE dbChangedRows(
> tableName CHAR(40),
> changeDate DATE,
> changePK INT, --PK of changed row
> PRIMARY KEY (changeDate,tableNam
e,changePK),
> )
>
> And every time a record is changed, this table gets updated.
>
> An alternative would be to add a "changeDate" column to all the
> tables, and update that every time a record is changed. Doesn't
> seem like the ideal solution, but would work more-or-less for what I
> need.
>
>
> The first and obvious solution would be to create a trigger for each
> table to do this. But this is such an ugly solution (the list of
> triggers on Sybase Central the list of triggers would be unmanageable)
> that I hope there is a more elegant/clean way of doing this...
>
> Reading the transaction log seems out of the question....
>
> Is there an elegant way of doing something like this?
>
> Thanks in advance,
> Edgard
>
>
>

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Edgard L. Riba

2005-07-08, 11:23 am

Hi Glenn,

Thanks for responding.

Sorry if I wasn't clear.

All I need to know is which rows have changed. So that I can list all the
primary keys of the rows that changed "today June 8, 2005" in table1,
table2, ..... (about 150 tables).

Don't need to store the "old values", but again, only a way of finding out
which rows changed between a range of dates. Also, I need to be able to
query this online, as I want to do some reports on these rows that have
changed.

The "last-changed-date" column would satisfy this requirement, but it would
be easier to do the reports with a separate table that only contains the
primary-keys of the rows that changed (along with the table-name and
date-of-change as in my original message).

Best regards,
Edgard

"Glenn Paulley" <paulley@ianywhere.com> escribió en el mensaje
news:Xns968D751669DB
4paulleyianywherecom
@10.22.241.106...
> You haven't specified enough detail of your requirements for me to give
> an informed answer.
>
> Adding a "last changed" column to a table doesn't give you the old values
> of the row, whereas with a "shadow" table you would have the keys of the
> rows that have been altered (and possbily all of the other columns as
> well). So:
>
> - do you require saving the old values?
> - does this information need to be "on-line" (ie. do you need to *query*
> it) - or do you need to (simply) generate a log of changes for offline
> audit?
>
> Glenn
>
> "Edgard L. Riba" <elriba at rimith dot com> wrote in
> news:42ce92ca$1@foru
ms-2-dub:
>
>
>
>
> --
> Glenn Paulley
> Research and Development Manager, Query Processing
> iAnywhere Solutions Engineering
>
> EBF's and Patches: http://downloads.sybase.com
> choose SQL Anywhere Studio >> change 'time frame' to all
>
> To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm
>
> SQL Anywhere Studio Supported Platforms and Support Status
> http://my.sybase.com/detail?id=1002288

David Kerber

2005-07-08, 1:23 pm

In article <42ce92ca$1@forums-2-dub>, "Edgard L. Riba" <elriba at rimith
dot com> says...
> Hi,
>
> I would like to implement a table that contains all rows that have changed.
>
> The table would be something like:
>
> CREATE TABLE dbChangedRows(
> tableName CHAR(40),
> changeDate DATE,
> changePK INT, --PK of changed row
> PRIMARY KEY (changeDate,tableNam
e,changePK),
> )
>
> And every time a record is changed, this table gets updated.
>
> An alternative would be to add a "changeDate" column to all the tables, and
> update that every time a record is changed. Doesn't seem like the ideal
> solution, but would work more-or-less for what I need.
>
>
> The first and obvious solution would be to create a trigger for each table
> to do this. But this is such an ugly solution (the list of triggers on
> Sybase Central the list of triggers would be unmanageable) that I hope there
> is a more elegant/clean way of doing this...
>
> Reading the transaction log seems out of the question....
>
> Is there an elegant way of doing something like this?

Would adding a datetime column with DEFAULT TIMESTAMP (which updates
when the row is changed) serve your purposes?

--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).

Edgard L. Riba

2005-07-08, 1:23 pm

Hi David,

> Would adding a datetime column with DEFAULT TIMESTAMP (which updates
> when the row is changed) serve your purposes?

Only for INSERTs, but I need also for UPDATEs at least. Preferably
DELETEs as well.

I wonder if there is a way of setting up some sort of "universal" trigger
<g> that fires whenever any row changes...

Best regards,
Edgard

David Kerber

2005-07-08, 8:25 pm

In article <42cecbb5$1@forums-1-dub>, "Edgard L. Riba" <elriba at rimith
dot com> says...
> Hi David,
>
>
> Only for INSERTs, but I need also for UPDATEs at least. Preferably
> DELETEs as well.

DEFAULT TIMESTAMP updates when UPDATEs are performed on that row, but
obviously won't handle DELETEs.

>
> I wonder if there is a way of setting up some sort of "universal" trigger
> <g> that fires whenever any row changes...

Yeah, I've had times when that would have been nice as well...

--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).

Edgard L. Riba

2005-07-08, 8:25 pm

Hi David,

Goodness! I had no idea DEFAULT TIMESTAMP worked this way <g>...

Most of these tables are under replication as well. Do you know if the
timestamp column changes when an update replicates? I mean, when an
update replicates, the row is updated on the remote server: Will the
timestamp field hold the data of the original update, or will it change when
the replicated update occurs on the remote server?

Thanks,
Edgard

"David Kerber" < ns_dkerber@ns_wraenv
iro.com> escribió en el mensaje
news:MPG. 1d3897a02e52f7ba9897
76@forums.sybase.com...
> In article <42cecbb5$1@forums-1-dub>, "Edgard L. Riba" <elriba at rimith
> dot com> says...
>
> DEFAULT TIMESTAMP updates when UPDATEs are performed on that row, but
> obviously won't handle DELETEs.
>
>
> Yeah, I've had times when that would have been nice as well...
>
>
> --
> Remove the ns_ from if replying by e-mail (but keep posts in the
> newsgroups if possible).

David Kerber

2005-07-08, 8:25 pm

In article <42ced799$1@forums-1-dub>, "Edgard L. Riba" <elriba at rimith
dot com> says...
> Hi David,
>
> Goodness! I had no idea DEFAULT TIMESTAMP worked this way <g>...
>
> Most of these tables are under replication as well. Do you know if the
> timestamp column changes when an update replicates? I mean, when an
> update replicates, the row is updated on the remote server: Will the
> timestamp field hold the data of the original update, or will it change when
> the replicated update occurs on the remote server?

I don't know, but I would hope that it would keep the value from when
the change was done, and not the replication. Somebody else will have
to confirm this, though.

.....

--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).

Breck Carter [TeamSybase]

2005-07-08, 8:25 pm

On 8 Jul 2005 12:44:25 -0700, "Edgard L. Riba" <elriba at rimith dot
com> wrote:

>Hi David,
>
>Goodness! I had no idea DEFAULT TIMESTAMP worked this way <g>...

Oh yeah... the other one that works this way is DEFAULT LAST USER.

>
>Most of these tables are under replication as well. Do you know if the
>timestamp column changes when an update replicates? I mean, when an
>update replicates, the row is updated on the remote server: Will the
>timestamp field hold the data of the original update, or will it change when
>the replicated update occurs on the remote server?

If the column is in the publication, it will be replicated and the
DEFAULT will not be applied. Otherwise, it won't be replicated and the
DEFAULT will be applied.

....I think :)... it's late Friday.

Breck

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Greg Fenton

2005-07-09, 3:23 am

Edgard L. Riba wrote:
>
> Goodness! I had no idea DEFAULT TIMESTAMP worked this way <g>...
>

Just for completeness sake in this thread, recognize that

DEFAULT CURRENT TIMESTAMP

will only assign a value on INSERT. UPDATE will not affect the value.

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

Glenn Paulley

2005-07-09, 8:23 pm

David Kerber < ns_dkerber@ns_wraenv
iro.com> wrote in
news:MPG. 1d3897a02e52f7ba9897
76@forums.sybase.com:

> In article <42cecbb5$1@forums-1-dub>, "Edgard L. Riba" <elriba at
> rimith dot com> says...
>
> DEFAULT TIMESTAMP updates when UPDATEs are performed on that row, but
> obviously won't handle DELETEs.
>
>
> Yeah, I've had times when that would have been nice as well...
>
>

Great point about DELETEs, David. If Edgard's application actually does
deletes, and the history needs to be captured, then a more comprehensive
using shadow tables will be necessary.

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Sponsored Links