There is no problem with the trigger in old version 17.0.10.5963, but an error is reported in new sa-17

[ximen]

    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();

---

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

[VolkerBarth]

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

[chris_keating]

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.

[VolkerBarth]

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.

[ximen]

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

[former_SQLA_member1695108]

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.

[awitter]

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;

[fvestjens]

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.

[VolkerBarth]

Yep, that's exactly the suggestion in my answer 🙂