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.

    CREATE TABLE "DBA"."time_type" (
    "type_id" VARCHAR(30) NOT NULL,
    "description" VARCHAR(60) NULL,
    "create_time" TIMESTAMP NULL,
    "edit_time" TIMESTAMP NULL,
    PRIMARY KEY ( "type_id" ASC )
) IN "system";

CREATE TRIGGER "tr_time_type_in" after insert order 2 on dba.time_type referencing new as new_time_type     for each row begin update time_type set create_time = getdate() where time_type.type_id = time_type.type_id end;

CREATE TRIGGER "tr_time_type_up" after update order 1 on dba.time_type referencing new as new_time_type         for each row begin update time_type set edit_time = getdate() where time_type.type_id = new_time_type.type_id end;

--insert test data

INSERT INTO "DBA"."time_type" ("type_ID","description","create_time","edit_time")

select '101','test',now(),now();

alt text


The results of my tests on different versions are as follows:

error version:

     sql anywhere 17 17.0.11.7458    error

     sql anywhere 17 17.0.11.7058    error

no problem version:

     **sql anywhere 17 17.0.10.5963    OK**
     sql anywhere 12                 OK

asked 28 Nov '23, 10:22

mfkpie8's gravatar image

mfkpie8
273667175
accept rate: 12%

edited 29 Nov '23, 09:37


I can't comment on the difference in both built versions - however, I would very strongly recommend not to use an AFTER UPDATE TRIGGER to update the same row again unconditionally because that will possibly lead to recursion until the maximum nested trigger level is reached.

(It would be different if you would update the row only once, say only if a particular column is empty, and you then add a value, so the trigger would be fired once more, but then stops because the column isn't empty anymore.)

In particular, you don't need triggers at all to update a "last_changed_time" (like your "edit_time" column) - just use the column with DEFAULT TIMESTAMP, and it gets automatically updated unless you explicitely set its value.

Similarly, a column with DEFAULT CURRENT TIMESTAMP is set automatically when a row is inserted, so you could also get rid of the AFTER INSERT trigger to set "create_time".

permanent link

answered 28 Nov '23, 11:16

Volker%20Barth's gravatar image

Volker Barth
40.3k363552824
accept rate: 34%

4

This is the result of Microsecond TIMESTAMP (HH:MM:SSS.nnnnnn) support that was introduced in SQLA 17 SP01 (17.0.11) on Windows platforms. Note that this code would have always failed on UNIX platforms which had microsecond timestamps. You can workaround with

set option public.truncate_timestamp_values = 'On'; set option public.default_timestamp_increment = 1000;

but it is recommended to rewrite the affected triggers per Volker's suggestion.

(28 Nov '23, 21:55) Chris Keating
Replies hidden
1

OK, so that would probably explain the effect:

The background is the particular behaviour of AFTER ROW UPDATE triggers: Those are NOT fired for a row where no column values actually changed in value.

I assume that the former TIMESTAMP precision of milliseconds on Windows has meant that two consecutive calls of getdate() could return exactly the same TIMESTAMP value, so in the AFTER ROW UPDATE trigger the UPDATE on column "edit_time" would be set to the value it already had, and then the trigger would stop calling itself again because of the rule mentioned above. (I would suspect that would happen after an arbitrary number of nested calls of that trigger.)

In contrast, with the much higher precision in 17.0.11 (or on other OSes), each getdate() call will return a different TIMESTAMP value, so each trigger call does change the value of "edit_time" to a different value, thereby endlessly triggering another trigger call until the database server returns the SQLCODE -274 (SQLE_NESTING_TOO_DEEP) error.

(29 Nov '23, 04:10) Volker Barth

Thank you very much for your guidance. It is indeed a logical problem! I learned a lot of knowledge here. Thank you for sharing

(29 Nov '23, 09:38) mfkpie8

I can't comment on the difference in both built versions - however, I would very strongly recommend not to use an AFTER UPDATE TRIGGER to update the same row again unconditionally because that will possibly lead to recursion until the maximum nested trigger level is reached.

(It would be different if you would update the row only once, say only if a particular column is empty, and you then add a value, so the trigger would be fired once more, but then stops because the column isn't empty anymore.)

In particular, you don't need triggers at all to update a "last_changed_time" (like your "edit_time" column) - just use the column with DEFAULT TMESTAMP, and it gets automatically updated unless you explicitely set its value.

Similarly, a column with DEFAULT CURRENT TIMESTAMP is set automatically when a row is inserted, so you could also get rid of the AFTER INSERT trigger to set "create_time".

Thank you for the insight! I appreciate your caution about the potential recursion issue with the AFTER UPDATE trigger. I'll definitely reconsider the approach and explore using DEFAULT TIMESTAMP for the 'edit_time' column.

Regarding the 'create_time' column, I'll explore the option of DEFAULT CURRENT TIMESTAMP for the initial value during insertion, as you suggested. It seems like a more straightforward approach, and your advice on avoiding triggers for such scenarios is noted.

(14 Dec '23, 01:27) robinluie

Besides above suggestion (using default current timestamp) for this example, one genereic advice: Don't use an update statement to change values of the same record. Try to change the field in the before insert/update: Instead of using that first trigger:

CREATE TRIGGER "tr_time_type_in" after insert order 2 on dba.time_type referencing new as new_time_type     for each row 
    begin 
      update time_type set create_time = getdate() where time_type.type_id = time_type.type_id 
    end;
do this:
CREATE TRIGGER "tr_time_type_in" before insert, update order 2 on dba.time_type referencing old as old_time_type new as new_time_type     for each row 
    begin 
      if inserting
         set new_time_type.create_time = getdate();
      else
        set new_time_type.edit_time = getdate();
      end if;
    end;

permanent link

answered 04 Dec '23, 01:40

ArcoW's gravatar image

ArcoW
2613315
accept rate: 0%

edited 04 Dec '23, 03:01

Volker%20Barth's gravatar image

Volker Barth
40.3k363552824

I would suggest:

...
"create_time" timestamp default current timestamp,
"edit_time"  timestamp default timestamp,
...
Then you don't need a trigger to fill the fields.

permanent link

answered 30 Nov '23, 07:57

Frank%20Vestjens's gravatar image

Frank Vestjens
1.3k364766
accept rate: 21%

1

Yep, that's exactly the suggestion in my answer :)

(30 Nov '23, 09:39) Volker Barth
Your answer
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:

×248
×79

question asked: 28 Nov '23, 10:22

question was seen: 636 times

last updated: 31 Jan, 05:52