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