Hello, I have a trigger that was working well in sql anywhere 8 version but now triggers twice after migrating to sql any 12. Basic idea for this trigger is to control the update of a field called last_modified_date. I use a flag called send_to_flag to determine if last_modified_date needs to be set or not. I went through sql anywhere 12 debugger and it appears that if the send_to_flag = 'N', it updates the flag to 'Y' but instead of exiting after performing the action, it goes back and triggers again because there is an update,so second time, the condition of send_to_flag = 'Y' is satisfied, hence it updates the last_modified_date.

How do I prevent the trigger from firing twice? Any help would be appreciated. Thanks.

CREATE TRIGGER "a_inv_last_modified_date_t" after insert,update order 1 on 
DBA.a_inv 
referencing old as OLD_A_INV new as NEW_A_INV 
for each row 
begin 
 if NEW_A_INV.SEND_TO_FLAG = 'Y' then 
   update A_INV 
     set A_INV.LAST_MODIFIED_DATE = GETDATE() 
     where A_INV.INV_ID = NEW_A_INV.INV_ID 
 else 
    update A_INV 
     set A_INV.send_to_flag = 'Y' 
     where A_INV.INV_ID = NEW_A_INV.INV_ID
  end if 
end

asked 28 Nov '12, 15:51

Vid%20R's gravatar image

Vid R
66125
accept rate: 0%

edited 28 Nov '12, 16:27

Mark%20Culp's gravatar image

Mark Culp
24.9k10141297

What is the value of send_to_flag when you insert a new row? Is there a default value?

From your description, if the row is existing and is updated with the flag being set to 'N', then I would think that the handling you describe is as almost expected:

The current update will follow the ELSE clause and thereby modify the flag to 'Y', and that modification will trigger the trigger a second time, and that will follow the IF clause and modify the timestamp - and would even run recursively until the stack frame overflows, in my expectation...

(28 Nov '12, 18:40) Volker Barth

Exactly - that is what is happening. The default value of send_to_flag = 'Y'. Then when a certain action happens in the app, it is set to 'N'. I can see the 'N' value being set in the db when I go put a stop using debugger and run a sql. As soon as that update is done, trigger goes to set it to 'Y' as expected and immediately goes into the the first condition to set the last modified date. I will check the app again to see what other actions happen but in the debugger, since it jumps to the first condition again immediately, I am inclined to think it is the trigger action. Will 'update' you :) Thanks.

(29 Nov '12, 10:05) Vid R

Revised: The "referencing old" clause has been removed because it doesn't apply to an insert trigger, and the example has been changed to a complete demo.


I'm not sure why the behavior was different in Version 8, but your trigger is recursively firing itself.

Try changing it to a BEFORE trigger, and use SET assignments instead of UPDATE; the following code worked OK in 12.0.1.3810:

CREATE TABLE a_inv (
   INV_ID               INTEGER NOT NULL PRIMARY KEY,
   LAST_MODIFIED_DATE   TIMESTAMP,
   SEND_TO_FLAG         VARCHAR ( 1 ) );

CREATE TRIGGER "a_inv_last_modified_date_t" 
   BEFORE insert,update order 1 on DBA.a_inv 
   referencing new as NEW_A_INV 
   for each row 
begin 
if NEW_A_INV.SEND_TO_FLAG = 'Y' then 
   SET NEW_A_INV.LAST_MODIFIED_DATE = GETDATE();
   MESSAGE STRING ( 'Trigger fired on INV_ID = ', NEW_A_INV.INV_ID, '; then: LAST_MODIFIED_DATE = ', NEW_A_INV.LAST_MODIFIED_DATE );
else 
   SET NEW_A_INV.send_to_flag = 'Y'; 
   MESSAGE STRING ( 'Trigger fired on INV_ID = ', NEW_A_INV.INV_ID, '; else: send_to_flag = ', NEW_A_INV.send_to_flag );
end if 
end;

INSERT a_inv VALUES ( 1, CURRENT DATE, 'Y' );
INSERT a_inv VALUES ( 2, CURRENT DATE, 'Y' );
INSERT a_inv VALUES ( 3, CURRENT DATE, 'Y' );
INSERT a_inv VALUES ( 4, CURRENT DATE, 'Y' );
INSERT a_inv VALUES ( 5, CURRENT DATE, 'Y' );
INSERT a_inv VALUES ( 6, CURRENT DATE, 'Y' );
INSERT a_inv VALUES ( 7, CURRENT DATE, 'Y' );
INSERT a_inv VALUES ( 8, CURRENT DATE, 'Y' );
COMMIT;

UPDATE a_inv SET send_to_flag = 'N';
COMMIT;

SELECT * FROM a_inv ORDER BY INV_ID;

Trigger fired on INV_ID = 1; then: LAST_MODIFIED_DATE = 2012-11-29 08:59:23.321
Trigger fired on INV_ID = 2; then: LAST_MODIFIED_DATE = 2012-11-29 08:59:23.346
Trigger fired on INV_ID = 3; then: LAST_MODIFIED_DATE = 2012-11-29 08:59:23.351
Trigger fired on INV_ID = 4; then: LAST_MODIFIED_DATE = 2012-11-29 08:59:23.355
Trigger fired on INV_ID = 5; then: LAST_MODIFIED_DATE = 2012-11-29 08:59:23.360
Trigger fired on INV_ID = 6; then: LAST_MODIFIED_DATE = 2012-11-29 08:59:23.366
Trigger fired on INV_ID = 7; then: LAST_MODIFIED_DATE = 2012-11-29 08:59:23.371
Trigger fired on INV_ID = 8; then: LAST_MODIFIED_DATE = 2012-11-29 08:59:23.377
Trigger fired on INV_ID = 1; else: send_to_flag = Y
Trigger fired on INV_ID = 2; else: send_to_flag = Y
Trigger fired on INV_ID = 3; else: send_to_flag = Y
Trigger fired on INV_ID = 4; else: send_to_flag = Y
Trigger fired on INV_ID = 5; else: send_to_flag = Y
Trigger fired on INV_ID = 6; else: send_to_flag = Y
Trigger fired on INV_ID = 7; else: send_to_flag = Y
Trigger fired on INV_ID = 8; else: send_to_flag = Y

INV_ID,LAST_MODIFIED_DATE,SEND_TO_FLAG
1,'2012-11-29 08:59:23.321','Y'
2,'2012-11-29 08:59:23.346','Y'
3,'2012-11-29 08:59:23.351','Y'
4,'2012-11-29 08:59:23.355','Y'
5,'2012-11-29 08:59:23.360','Y'
6,'2012-11-29 08:59:23.366','Y'
7,'2012-11-29 08:59:23.371','Y'
8,'2012-11-29 08:59:23.377','Y'
permanent link

answered 28 Nov '12, 16:00

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

edited 29 Nov '12, 09:02

Thank You for your quick response! I tried the code above and unfortunately no luck. I went through sybase central debugger and I noticed the following: 1. Since I set the send_to_flag = 'N' in the app, the trigger fires and executes the SET NEW_A_INV.send_to_flag = 'Y'. There were 7 rows that were set and it went thru the same 7 times. 2. After the last row, it jumped back to SET NEW_A_INV.LAST_MODIFIED_DATE = GETDATE(); and it set the last modified date to getdate() for all 7 rows and that's what I would like to prevent.

In sql server, the same trigger (different syntax of course) works well. It only sets the send to flag to Y and does not touch the last modified date.

And probably you are right, not sure how it executed in ver 8...this trigger existed when I started working on this module.

(28 Nov '12, 17:19) Vid R
Replies hidden

FWIW, MS SQL Server triggers (and ASE ones) do not call themselves recursively by default, so that simply may make the difference.

SQL Anywhere triggers in Watcom-SQL dialect (as you are using) do call themselves recursively, cf. this doc page.

(28 Nov '12, 18:52) Volker Barth

Check your application and database to see if something else is going on, because I don't see the behavior you are describing. See the revised answer for an end-to-end example that fires the trigger only once per row, per insert or update.

(29 Nov '12, 09:04) Breck Carter
1

Breck, you are 100% right! I tried your example and I do not see the last_modified_date being updated at all when I update the flag to 'N'. As expected, the flag is being set to 'Y' as per the trigger without touching the last_modified_date. So, it is obvious now it is not a database issue but something happening in the app itself. Strange, why sql anywhere would look for any other updates even if happening in the app while sql server does not care....I will do some more research. Thank You all for all your help and input - you guys are awesome!

(29 Nov '12, 10:24) Vid R
Replies hidden
1

Check for the existence of a second trigger...

(29 Nov '12, 11:07) Breck Carter

Don't get puzzled if you see a difference between the behaviour of your original trigger and Breck's sample:

As far as I can tell, they are not semantically identical, and your original AFTER UPDATE trigger does call itself recursively, whereas Breck's trigger does not.

(Just in case you are trying to understand what does happen underneath - if you're satisfied with the modified trigger, than that's very fine, apparently, and we should not dig deepere...)

(29 Nov '12, 11:16) Volker Barth

@Volker - right - I checked the app and also the database to see if any other trigger is firing but I could not find anything. So it is evident that in sql any 12, something is different which makes the after update trigger fire recursively in my case. I will be checking other similar update triggers in the db and warn the developers to see if they observe the same. Right now, I like Breck's solution because it is working well and definitely, I am planning to dig more to see what causes this recursive behavior in my case and possibly we may have to change other triggers in our db based on the outcome. Thanks again!

(29 Nov '12, 11:31) Vid R
showing 4 of 7 show all flat view

Have you thought about using the WHEN clause, or can you only use logic that is consistent between your different database environments? We do stuff like you're doing all the time, and watch those old and new values via the when clause. Actually do a lot of sub-selects within the when clause to get the values we are looking for. It also helps us to check if values in rows in that same table have changed or not.

Just throwing my 2 cents in here on this. Sounds like something that should be easy for you to do.

Jeff Gibson
Intercept Solutions - Sybase SQL Anywhere OEM Partner
Nashville, TN

permanent link

answered 29 Nov '12, 00:32

Jeff%20Gibson's gravatar image

Jeff Gibson
1.8k416172
accept rate: 20%

I can try the 'when' clause, the only issue with that is whenever we add a new column to the table, have to go back and modify the trigger to accommodate the new change. Plus in this case, it is a bit tricky because I am doing an update in the trigger itself , I am expecting this behavior to continue until I find the root cause. As suggested by Breck and Volker, I will try to execute the example in my db plus see if anything else is happening in the app. Thank You.

(29 Nov '12, 10:12) Vid R
Replies hidden

Just to confirm: If you're trying to modify the row that has triggered the trigger itself within the trigger, a BEFORE trigger (as Breck has suggested) is far more easy to code and maintain than an AFTER trigger IMHO.

The obvious downside for db-independent solutions is that MS SQL does not support BEFORE triggers (or any ROW LEVEL triggers) at all...

(29 Nov '12, 10:20) Volker Barth
1

Are you wanting to trigger the "trigger" recursively from one column value changing, or every column value changing? Curious as to why you would need to update your when clause if you added additional columns to your table. (Maybe I should go back and look at your examples more) :)

Just curious on the columns because normally we will only check for one or two column values in the when clause.

(29 Nov '12, 14:02) Jeff Gibson
1

This is the point where I go to companies that are using SQL Server and start doing the feature dance. :)

I remember the first time I asked about setting the before or after value on the trigger. I was like "Huh????"

(29 Nov '12, 14:03) Jeff Gibson

@Jeff- no, I don't want to do that. Just wanted it to fire once based on the if condition.A bit more detail about the last_modified_date column. In the table it is not set to getdate() by default. It is being used to identify rows for syncing purposes to a centralized db. So any update that happens to 'any' column in the table should set this last modified date to getdate(). So the trigger help us in doing that. However, there is one scenario that we don't want the sync to happen (don't ask me why :), that is another long story ) , so we should not update the last modified_date. Hence we are using the send_to_flag to control the update to this column. If I add any columns, I need to make sure that when the new column is updated, it is synced. That is why I was thinking it needs to be included in the 'when' clause. I could use the send_to_flag in the when clause but I would have to go back to the app and set it to the appropriate value in 1000 different places. I would like to control the updates thru' the app but these are the joys of inheriting exiting apps...I think using the before update clause, definitely makes sense.

(01 Dec '12, 13:42) Vid R
Replies hidden

Vid, usually a column with the special DEFAULT TIMESTAMP is very handy to mark those rows that have been modified in any way - and that is handled by the database server "automagically" and does not require any trigger at all - and needs no particular handling if columns are added in the future.

And note: It's a default, meaning that you can always code an UPDATE statement with explicitly setting the column to its previous value, so that the value is not modified then.

From your description, I could imagine that this may already be sufficient for your purposes, if your sync condition is based on the timestamp column being "newer" than the last sync.

(02 Dec '12, 13:06) Volker Barth
showing 2 of 6 show all flat view

According to the following test using your original trigger, it works the same in version 12 as it did in version 8.

In both versions, it SOMETIMES fires the trigger recursively a second time, and sometimes not. The reason is two-fold:

First, the trigger always executes an UPDATE on the same table, which by definition fires the same trigger recursively, but...

Second, in SQL Anywhere an AFTER ROW UPDATE trigger will NOT be fired if the UPDATE doesn't actually change any columns in the row; this behavior is different from that of the BEFORE ROW trigger.

In the following test, the 8 outer INSERT statements cause the "if - then" part of the trigger to execute, which update the LAST_MODIFIED_DATE column but doesn't actually change the value, so the trigger is not recursively fired.

However, the outer "UPDATE a_inv SET send_to_flag = 'N'" statement fires the trigger for each row, causing the "if - else" part of the trigger to execute, which DOES make a change to a column value, which DOES cause the trigger to fire recursively... but only once, because the second time the trigger fires it runs the "if - then" code which doesn't change anything.

You may not have noticed the behavior in version 8 because (a) it doesn't seem to harm the data and (b) you may not have traced the code through the debugger.

Just for grins'and'giggles, change the LAST_MODIFIED_DATE to TIMESTAMP and see what happens... on my computer the trigger recursively executed up to @nesting_level = 4 before the GETDATE() function returned the same date/time value that it did on the previous past, thus halting the recursion :)

Anyway... like Volker said, BEFORE ROW triggers are the most useful kind, and use the special "SET table.column" notation that is available in row-level triggers rather than doing nested set-level UPDATE statements.

If SQL Server is like a polar icecap, then SQL Anywhere is like a tropical beach... you don't have to wear a parka on the beach; similarly, you don't have to struggle with UPDATE statements in SQL Anywhere triggers :)

-- Changes made to the surrounding test code, from the previous answer...
--    Added CREATE VARIABLE @nesting_level.
--    Changed LAST_MODIFIED_DATE data type to date from timestamp... VERY IMPORTANT!

-- Changes made to the your original trigger...
--    Added SET @nesting_level statements.
--    Added MESSAGE statements.
--    Added some semicolons.

CREATE VARIABLE @nesting_level INTEGER;

SET @nesting_level = 0;

CREATE TABLE a_inv (
   INV_ID               INTEGER NOT NULL PRIMARY KEY,
   LAST_MODIFIED_DATE   DATE,
   SEND_TO_FLAG         VARCHAR ( 1 ) );

CREATE TRIGGER "a_inv_last_modified_date_t" after insert,update order 1 on 
DBA.a_inv 
referencing old as OLD_A_INV new as NEW_A_INV 
for each row 
begin 
 SET @nesting_level = @nesting_level + 1;
 if NEW_A_INV.SEND_TO_FLAG = 'Y' then 
   MESSAGE STRING ( 'Trigger fired on INV_ID = ', 
                    NEW_A_INV.INV_ID, 
                    ', @nesting_level = ',
                    @nesting_level,
                    '; then update A_INV set A_INV.LAST_MODIFIED_DATE = GETDATE()...' );
   update A_INV 
     set A_INV.LAST_MODIFIED_DATE = GETDATE() 
     where A_INV.INV_ID = NEW_A_INV.INV_ID; 
 else 
    MESSAGE STRING ( 'Trigger fired on INV_ID = ', 
                     NEW_A_INV.INV_ID, 
                     ', @nesting_level = ',
                     @nesting_level,
                     '; else update A_INV set A_INV.send_to_flag = Y...' );
    update A_INV 
     set A_INV.send_to_flag = 'Y' 
     where A_INV.INV_ID = NEW_A_INV.INV_ID;
  end if;
 SET @nesting_level = @nesting_level - 1;
end;

INSERT a_inv VALUES ( 1, CURRENT DATE, 'Y' );
INSERT a_inv VALUES ( 2, CURRENT DATE, 'Y' );
INSERT a_inv VALUES ( 3, CURRENT DATE, 'Y' );
INSERT a_inv VALUES ( 4, CURRENT DATE, 'Y' );
INSERT a_inv VALUES ( 5, CURRENT DATE, 'Y' );
INSERT a_inv VALUES ( 6, CURRENT DATE, 'Y' );
INSERT a_inv VALUES ( 7, CURRENT DATE, 'Y' );
INSERT a_inv VALUES ( 8, CURRENT DATE, 'Y' );
COMMIT;

UPDATE a_inv SET send_to_flag = 'N';
COMMIT;

SELECT @@VERSION, * FROM a_inv ORDER BY INV_ID;

@@VERSION,INV_ID,LAST_MODIFIED_DATE,SEND_TO_FLAG
'8.0.3.5379',1,'2012-11-29','Y'
'8.0.3.5379',2,'2012-11-29','Y'
'8.0.3.5379',3,'2012-11-29','Y'
'8.0.3.5379',4,'2012-11-29','Y'
'8.0.3.5379',5,'2012-11-29','Y'
'8.0.3.5379',6,'2012-11-29','Y'
'8.0.3.5379',7,'2012-11-29','Y'
'8.0.3.5379',8,'2012-11-29','Y'

Trigger fired on INV_ID = 1, @nesting_level = 1; then update A_INV set A_INV.LAST_MODIFIED_DATE = GETDATE()...
Trigger fired on INV_ID = 2, @nesting_level = 1; then update A_INV set A_INV.LAST_MODIFIED_DATE = GETDATE()...
Trigger fired on INV_ID = 3, @nesting_level = 1; then update A_INV set A_INV.LAST_MODIFIED_DATE = GETDATE()...
Trigger fired on INV_ID = 4, @nesting_level = 1; then update A_INV set A_INV.LAST_MODIFIED_DATE = GETDATE()...
Trigger fired on INV_ID = 5, @nesting_level = 1; then update A_INV set A_INV.LAST_MODIFIED_DATE = GETDATE()...
Trigger fired on INV_ID = 6, @nesting_level = 1; then update A_INV set A_INV.LAST_MODIFIED_DATE = GETDATE()...
Trigger fired on INV_ID = 7, @nesting_level = 1; then update A_INV set A_INV.LAST_MODIFIED_DATE = GETDATE()...
Trigger fired on INV_ID = 8, @nesting_level = 1; then update A_INV set A_INV.LAST_MODIFIED_DATE = GETDATE()...
Trigger fired on INV_ID = 1, @nesting_level = 1; else update A_INV set A_INV.send_to_flag = Y...
Trigger fired on INV_ID = 1, @nesting_level = 2; then update A_INV set A_INV.LAST_MODIFIED_DATE = GETDATE()...
Trigger fired on INV_ID = 2, @nesting_level = 1; else update A_INV set A_INV.send_to_flag = Y...
Trigger fired on INV_ID = 2, @nesting_level = 2; then update A_INV set A_INV.LAST_MODIFIED_DATE = GETDATE()...
Trigger fired on INV_ID = 3, @nesting_level = 1; else update A_INV set A_INV.send_to_flag = Y...
Trigger fired on INV_ID = 3, @nesting_level = 2; then update A_INV set A_INV.LAST_MODIFIED_DATE = GETDATE()...
Trigger fired on INV_ID = 4, @nesting_level = 1; else update A_INV set A_INV.send_to_flag = Y...
Trigger fired on INV_ID = 4, @nesting_level = 2; then update A_INV set A_INV.LAST_MODIFIED_DATE = GETDATE()...
Trigger fired on INV_ID = 5, @nesting_level = 1; else update A_INV set A_INV.send_to_flag = Y...
Trigger fired on INV_ID = 5, @nesting_level = 2; then update A_INV set A_INV.LAST_MODIFIED_DATE = GETDATE()...
Trigger fired on INV_ID = 6, @nesting_level = 1; else update A_INV set A_INV.send_to_flag = Y...
Trigger fired on INV_ID = 6, @nesting_level = 2; then update A_INV set A_INV.LAST_MODIFIED_DATE = GETDATE()...
Trigger fired on INV_ID = 7, @nesting_level = 1; else update A_INV set A_INV.send_to_flag = Y...
Trigger fired on INV_ID = 7, @nesting_level = 2; then update A_INV set A_INV.LAST_MODIFIED_DATE = GETDATE()...
Trigger fired on INV_ID = 8, @nesting_level = 1; else update A_INV set A_INV.send_to_flag = Y...
Trigger fired on INV_ID = 8, @nesting_level = 2; then update A_INV set A_INV.LAST_MODIFIED_DATE = GETDATE()...

@@VERSION,INV_ID,LAST_MODIFIED_DATE,SEND_TO_FLAG
'12.0.1.3810',1,'2012-11-29','Y'
'12.0.1.3810',2,'2012-11-29','Y'
'12.0.1.3810',3,'2012-11-29','Y'
'12.0.1.3810',4,'2012-11-29','Y'
'12.0.1.3810',5,'2012-11-29','Y'
'12.0.1.3810',6,'2012-11-29','Y'
'12.0.1.3810',7,'2012-11-29','Y'
'12.0.1.3810',8,'2012-11-29','Y'

Trigger fired on INV_ID = 1, @nesting_level = 1; then update A_INV set A_INV.LAST_MODIFIED_DATE = GETDATE()...
Trigger fired on INV_ID = 2, @nesting_level = 1; then update A_INV set A_INV.LAST_MODIFIED_DATE = GETDATE()...
Trigger fired on INV_ID = 3, @nesting_level = 1; then update A_INV set A_INV.LAST_MODIFIED_DATE = GETDATE()...
Trigger fired on INV_ID = 4, @nesting_level = 1; then update A_INV set A_INV.LAST_MODIFIED_DATE = GETDATE()...
Trigger fired on INV_ID = 5, @nesting_level = 1; then update A_INV set A_INV.LAST_MODIFIED_DATE = GETDATE()...
Trigger fired on INV_ID = 6, @nesting_level = 1; then update A_INV set A_INV.LAST_MODIFIED_DATE = GETDATE()...
Trigger fired on INV_ID = 7, @nesting_level = 1; then update A_INV set A_INV.LAST_MODIFIED_DATE = GETDATE()...
Trigger fired on INV_ID = 8, @nesting_level = 1; then update A_INV set A_INV.LAST_MODIFIED_DATE = GETDATE()...
Trigger fired on INV_ID = 1, @nesting_level = 1; else update A_INV set A_INV.send_to_flag = Y...
Trigger fired on INV_ID = 1, @nesting_level = 2; then update A_INV set A_INV.LAST_MODIFIED_DATE = GETDATE()...
Trigger fired on INV_ID = 2, @nesting_level = 1; else update A_INV set A_INV.send_to_flag = Y...
Trigger fired on INV_ID = 2, @nesting_level = 2; then update A_INV set A_INV.LAST_MODIFIED_DATE = GETDATE()...
Trigger fired on INV_ID = 3, @nesting_level = 1; else update A_INV set A_INV.send_to_flag = Y...
Trigger fired on INV_ID = 3, @nesting_level = 2; then update A_INV set A_INV.LAST_MODIFIED_DATE = GETDATE()...
Trigger fired on INV_ID = 4, @nesting_level = 1; else update A_INV set A_INV.send_to_flag = Y...
Trigger fired on INV_ID = 4, @nesting_level = 2; then update A_INV set A_INV.LAST_MODIFIED_DATE = GETDATE()...
Trigger fired on INV_ID = 5, @nesting_level = 1; else update A_INV set A_INV.send_to_flag = Y...
Trigger fired on INV_ID = 5, @nesting_level = 2; then update A_INV set A_INV.LAST_MODIFIED_DATE = GETDATE()...
Trigger fired on INV_ID = 6, @nesting_level = 1; else update A_INV set A_INV.send_to_flag = Y...
Trigger fired on INV_ID = 6, @nesting_level = 2; then update A_INV set A_INV.LAST_MODIFIED_DATE = GETDATE()...
Trigger fired on INV_ID = 7, @nesting_level = 1; else update A_INV set A_INV.send_to_flag = Y...
Trigger fired on INV_ID = 7, @nesting_level = 2; then update A_INV set A_INV.LAST_MODIFIED_DATE = GETDATE()...
Trigger fired on INV_ID = 8, @nesting_level = 1; else update A_INV set A_INV.send_to_flag = Y...
Trigger fired on INV_ID = 8, @nesting_level = 2; then update A_INV set A_INV.LAST_MODIFIED_DATE = GETDATE()...
permanent link

answered 29 Nov '12, 16:04

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

edited 29 Nov '12, 16:05

Ah, now I see w.r.t. self-recursion:

Apparently, I had thought (in my comment on the question) that the UPDATE on A_INV.LAST_MODIFIED_DATE = GETDATE() in the IF CLAUSE would always lead to a different value, and that would lead to a endless self-recursion (aka an memory error...). But of course the resolution of both the column's type and of GETDATE() will matter.

So I guess what I had expected would happen when one would update any column of the row and would have declared LAST_MODIFIED_DATE as a column with DEFAULT TIMESTAMP, which is guaranteed to return an unique value (at least with according settings of the default_timestamp_increment option). That would always lead to different column values, and therefore would always trigger another AFTER UPDATE trigger execution...

(29 Nov '12, 16:43) Volker Barth
Replies hidden

I don't think explicit SET = GETDATE() values are guaranteed unique (nor should they be), only values set via the DEFAULT mechanism. FWIW I did run the test with data type TIMESTAMP and the recursion never went past 4 levels. Perhaps on a slowwwwer computer it would go deeper.

(29 Nov '12, 17:06) Breck Carter

BTW, if you change the keyword "after" to "before" in the example above, you see some SPECTACULAR results (infinite recursion!)

(29 Nov '12, 17:08) Breck Carter

Yes, I agree. I had thought about something like SET LAST_MODIFIED_DATE = DEFAULT TIMESTAMP in the UPDATE clause but guess that it is either invalid or not the same as that particular DEFAULT TIMESTAMP column default (and I have no system available for tests in the very moment). "Testing by reading", that is:)

(29 Nov '12, 17:10) Volker Barth

Very well explained Breck. Even I had not tested so deeply. Thanks to you and Volker for clearing things, this clarifies the behavior and also gives good input to developers on how to code correctly.

(01 Dec '12, 13:45) Vid R

It would be NICE if we DID have a guaranteed-incrementing-CURRENT-TIMESTAMP facility available outside the INSERT DEFAULT TIMESTAMP context... but then again, I can't think when I would have used it :)... and I suppose a CREATE FUNCTION incrementing_current_timestamp which remembered the last return value would suffice.

(01 Dec '12, 15:46) Breck Carter
showing 2 of 6 show all flat view
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:

×79

question asked: 28 Nov '12, 15:51

question was seen: 5,465 times

last updated: 02 Dec '12, 13:07