Posted by T.H.N. on 03/31/06 11:59

Erland Sommarskog wrote:
> I assume that these updates are not performed by your client program,
> but by some other process?
>
> Rather than using a datetime column, you could use a timestamp column.
> A timestamp column is automatically updated with a monotonically
> increasing value each time a row is inserted or updated. Each value is
> unique in the database. Note that the name of the type is misleading.
> The value is a binary(8) and has to relation to time.
>
> You would still need that table for deleted rows. (Unless you add a
> "deleted" bit to the table; the client program would then actually
> perform the deletion once it has consumed the update.

Yes, sort of - there are several client programs all operating on the same
data set.

Thanks for pointing me to the timestamp data type, it's much better for this!
As well as its uniqueness per change I find it preferable to datetime as it's
independent of the system clock. My DDL now looks like this:

create table epl_packages
(
customer varchar(8) not null,
package_type char not null,
package_no int not null,
dimensions varchar(50) not null default(0),
weight_kg int not null,
despatch_id int,
loaded bit not null default(0),
item_count int not null default(0),
ts_last_update timestamp
)

alter table epl_packages
add constraint pk_epl_packages
primary key (customer, package_type, package_no)

create table epl_packages_deletions
(
ts_delete timestamp primary key, -- generated on insert. ok for PK as
-- there will be no updates to this table
customer varchar(8) not null,
package_type char not null,
package_no int not null
)

The triggers have less to do:

create trigger tr_del_epl_packages
on epl_packages
for delete
as
insert epl_packages_deletions
values (
customer,
package_type,
package_no
)
select
customer,
package_type,
package_no
from deleted
go

create trigger tr_upd_epl_packages
on epl_packages
for update
as
-- check for primary key change
if (columns_updated() & 1792) > 0 -- first three columns: 256+512+1024
insert epl_packages_deletions
values (
customer,
package_type,
package_no
)
select
customer,
package_type,
package_no
from deleted

And the client program uses "select @@dbts" prior to reading the data set or
changes to it to determine the point at which its displayed data was current.

[Back to original message]