Get Updated_At for any change in row on Sybase?

Question

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.

Answer 1

From your question I assume that you cannot create the updated_at column in that customer table by yourself (maybe because that table "belongs" to the software vendor).

Logging the creation or update timestamp in your additional table customers_getting_updated is simple. You need two triggers on the customer table, one for (after!) the creation and one for (after!) the update.

The first trigger fires after a customer record has been inserted. It simply inserts a record into the customer_getting_updated table with the (new) customer_id from the customer table and sets the current timestamp.

CREATE TRIGGER customer_inserted AFTER INSERT
ORDER 1 ON customer
REFERENCING NEW AS new_cust
FOR EACH ROW
BEGIN
  INSERT INTO customer_getting_updated ( customer_id, updated_at )
  VALUES (new_cust.customer_id, CURRENT TIMESTAMP);
END;

The second trigger fires after an existing customer has been updated. The trigger checks whether or not there is already a "timestamp logging entry" for that customer id. If existing it updates the timestamp, if missing it creates a new logging record (identical to the after insert trigger).

CREATE TRIGGER customer_updated_at AFTER UPDATE
ORDER 1 ON customer
REFERENCING NEW AS new_cust
FOR EACH ROW
BEGIN
  IF EXISTS(SELECT 1 FROM customer_getting_updated WHERE customer_id = new_cust.customer_id) THEN
    UPDATE customer_getting_updated SET updated_at = CURRENT TIMESTAMP
    WHERE customer_id = new_cust.customer_id;
  ELSE
    INSERT INTO customer_getting_updated ( customer_id, updated_at )
    VALUES (new_cust.customer_id, CURRENT TIMESTAMP);
  END IF;
END;

A short note on before or after triggers:

• If you want to manipulate the new (or updated) record itself you typically use a before trigger and change values in that record before they are written to the table.
• If you want to do something else - often with values from the new or updated record - you use an after trigger.