I am working with a Sybase SQL Anywhere table that has horrible tracking of changes. I have a windows service that queries multiple tables every 5 minutes looking for changes, but one particular table doesn't have any timestamps. In addition to that, when I added a new record, it was given an ID in the middle for some reason. There are 15,000 records and the new ID was 6775, so simply asking for records that have an ID greater than the last won't work.

I know in MySQL I could alter the table with something like:

ALTER TABLE `customer` ADD `updated_at` DATETIME on update CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

Which would auto update that value any time anything was changed. That doesn't work with Sybase, so my next thought was to create a second small table:

CREATE TABLE customers_getting_updated(
   customer_id int,
   updated_at DATETIME,
   PRIMARY KEY(patient_id) 
)

and then set a trigger to create / update records in that table. There are currently 150 columns in the table. I tried creating a trigger like this:

create TRIGGER "customer_record_updated" AFTER INSERT ON customer
for each row begin 
    INSERT INTO customers_getting_updated (patient_id, updated_at)
    VALUES(:NEW.customer_id, NOW());
end;

I am running this on a dual processor 6 core with 64GB memory on a SSD and the query has taken 10 minutes and still hasn't finished.

Assuming this will finish eventually (I saw someone say one took them 70 minutes????), since I didn't get an error up front, is there a better way since most computers that need to run this will have substantially less hardware power.

FYI - I tried doing a AFTER INSERT OR UPDATE but got an error on the OR, so I assume I will have to write separate triggers.

I am working with a Sybase SQL Anywhere table that has horrible tracking of changes. I have a windows service that queries multiple tables every 5 minutes looking for changes, but one particular table doesn't have any timestamps. In addition to that, when I added a new record, it was given an ID in the middle for some reason. There are 15,000 records and the new ID was 6775, so simply asking for records that have an ID greater than the last won't work.

I know in MySQL I could alter the table with something like:

ALTER TABLE `customer` ADD `updated_at` DATETIME on update CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

Which would auto update that value any time anything was changed. That doesn't work with Sybase, so my next thought was to create a second small table:

CREATE TABLE customers_getting_updated(
   customer_id int,
   updated_at DATETIME,
   PRIMARY KEY(patient_id) 
)

and then set a trigger to create / update records in that table. There are currently 150 columns in the table. I tried creating a trigger like this:

create TRIGGER "customer_record_updated" AFTER INSERT ON customer
for each row begin 
    INSERT INTO customers_getting_updated (patient_id, updated_at)
    VALUES(:NEW.customer_id, NOW());
end;

I am running this on a dual processor 6 core with 64GB memory on a SSD and the query has taken 15 minutes and still hasn't finished.

Assuming this will finish eventually (I saw someone say one took them 70 minutes????), since I didn't get an error up front, is there a better way since most computers that need to run this will have substantially less hardware power.

FYI - I tried doing a AFTER INSERT OR UPDATE but got an error on the OR, so I assume I will have to write separate triggers.