Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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%20Mangano's gravatar image

Tom Mangano
672242738
accept rate: 8%

edited 08 Apr '14, 06:18

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...)

(08 Apr '14, 06:35) Volker Barth

I need to update last_modified every time certain values change on the customer record.

(08 Apr '14, 07:00) Tom Mangano
Replies hidden
2

certain values change

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):

...
Before Update of column1, column2, column3, ...
on customer
Referencing NEW as new_name
For Each Row
Begin
   set new_game.last_modified = current timestamp;
End;
(08 Apr '14, 07:41) Volker Barth
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×79

question asked: 08 Apr '14, 06:16

question was seen: 1,556 times

last updated: 08 Apr '14, 07:41