ASA 16.0.0.1761 (64-bit)
2 triggers, both updates
Alter Trigger T1
After Update of column1, column2, column3, ..., last_modified
Order 1 on customer
Referencing OLD as old_name NEW as new_name
For Each Row
Begin
Update customer set last_modified = current timestamp
where customer.customer_id = new_name.customer_id
End
Alter Trigger T2
After Update of column2
Order 1 on customer
Referencing OLD as old_name NEW as new_name
For Each Row
Begin
update DBA.customer set last_modified = current timestamp
where customer.customer_id = new_name.customer_id;
IF new_name.active = 'N' THEN
INSERT INTO logical_deletes(table_name, key3)
VALUES ('customer', new_name.customer_id)
ELSEIF new_name.active = 'Y' THEN
DELETE FROM logical_deletes
WHERE table_name = 'customer' and key3 = new_name.customer_id
END IF;
End
Note that both triggers are order 1 and T1 has last_modified in it.
Having last_modified in T1, which should NOT be part of the AFTER UPDATE OF,
is poor programming on my part. It's a column used by Mobilink, and users
cannot modify the column.
App is written in PB
Both triggers were added to the DB using our app, not Sybase Central.
Modifying the customer record throws an error.
Like a typical user, I didn't write down EXACT error, but it was something to the
effect that the triggers were nested too deep. I think it was because last_modified
was in T1.
Using Sybase Central, I modified the order on T2 to ORDER 2 and
removed last_modified from T1.
Without exiting our app, I then saved the record, and did not get an error.
If I try to display another record, my app dies with a CONNECTION TERMINATED.
Back to Sybase Central.
The service has STOPPED.
I restart the service.
When I validate the DB, I get a corruption message, and the service stops.
asked
08 Apr '14, 06:16
Tom Mangano
672●24●27●38
accept rate:
8%
Is there a particular reason not to use the DEFAULT TIMESTAMP default that would set the current timestamp automatically (and might make these triggers unnecessary)?
IMHO, trigger T1 can be called recursively - in case the old and new value of column "last_modified" are not exactly the same. When using the DEFAULT TIMESTAMP as default, values are guaranteed to be unique AFAIK (so that would always lead to a recursive call), however, I guess when you set the value explicitly, you may not always set a different value depending on the timestamp resolution. Note, AFTER UPDATE triggers do only fire when the row has been changed to different values.
(That's just a comment, as it certainly doesn't answer your questions...)
I need to update last_modified every time certain values change on the customer record.
A column with DEFAULT TIMESTAMP would be modified anytime any value on the custumer record does change - so you can't use that since it should only be updated for changes of particular columns?
(If so, I would still recommend a much simpler BEFORE UPDATE OF ... trigger which would not be called recursively and could just consist of something like the following - note the usage of a simple SET statement instead of an UPDATE statement):