Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Problem timestamping Date field with update trigger

2 posts in General Discussion (old) Last posting was on 2007-11-09 19:06:30.0Z
Hedley Sohn Posted on 2007-11-09 15:44:11.0Z
Reply-To: "Hedley Sohn" <hsohn@microsoft.com>
From: "Hedley Sohn" <tcarlson@geographix.com>
Newsgroups: sybase.public.sqlanywhere
Subject: Problem timestamping Date field with update trigger
Lines: 49
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3138
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: 34.252.4.2
X-Original-NNTP-Posting-Host: 34.252.4.2
Message-ID: <4734804b$2@forums-1-dub>
Date: 9 Nov 2007 07:44:11 -0800
X-Trace: forums-1-dub 1194623051 34.252.4.2 (9 Nov 2007 07:44:11 -0800)
X-Original-Trace: 9 Nov 2007 07:44:11 -0800, 34.252.4.2
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:49
Article PK: 866505

(I apologize if this is answered somewhere. I haven't been able to find
anything related on the support site)

We have ASA 9.0. We have to keep a DATE field updated with the current time.
We CANNOT define the field with DEFAULT TIMESTAMP because the user
applications sometimes issue UPDATE statements when nothing about the row
has changed. This then causes a new TIMESTAMP but the data in the record
hasn't really changed. We have a busines rule that the DATE field is only
updated when the record is inserted or when some field within the record has
actually been changed.

So, we were forced to implement this with INSERT and UPDATE triggers. The
problem we have is that the insert after trigger we use is written as an
update to it's table:

ALTER TRIGGER "TIME_TRIG" AFTER INSERT
ORDER 1 ON "DBA"."DOCS"
REFERENCING NEW as new_insert
FOR EACH ROW
BEGIN
update DBA.DOCS set CHANGED_DATE = getdate(*)
WHERE DOCS.PKEY = new_insert.PKEY
END

The Update trigger:
ALTER TRIGGER "TIME_TRIG" AFTER UPDATE
ORDER 1 ON "DBA"."DOCS"
REFERENCING OLD AS old_record
FOR EACH ROW
BEGIN
update DBA.DOCS set CHANGED_DATE = getdate(*)
WHERE DOCS.PKEY = old_record.PKEY
END

then has it's own update statement.

The problem is that the update trigger can sometimes get into an extreme
recursive loop which then causes an exception. This is also very
inefficient.

Can anyone suggest a better way to handle this using triggers. Remember, we
simply cannot define the COLUMN as a DEFAULT TIMESTAMP (although we'd love
to)

Thanks,

-HS


Breck Carter [Team iAnywhere] Posted on 2007-11-09 19:06:30.0Z
From: "Breck Carter [Team iAnywhere]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: sybase.public.sqlanywhere
Subject: Re: Problem timestamping Date field with update trigger
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <jeb9j31sd9ekp1qruhosd3u08givgek3o7@4ax.com>
References: <4734804b$2@forums-1-dub>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: bcarter.sentex.ca
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
Date: 9 Nov 2007 11:06:30 -0800
X-Trace: forums-1-dub 1194635190 64.7.134.118 (9 Nov 2007 11:06:30 -0800)
X-Original-Trace: 9 Nov 2007 11:06:30 -0800, bcarter.sentex.ca
Lines: 90
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:50
Article PK: 866504

I am guessing you chose the less-useful and harder-to-work-with
"AFTER" form of the row-level trigger because it isn't fired unless
some column value has actually changed.

What you want to do is skip the UPDATE statement in the recursive
(second) firing of the trigger. You can do that by testing to see if
CHANGED_DATE has actually changed, which would mean it's a recursive
invocation.

This solution also works if the user explicitly changes the date; then
the trigger does nothing the *first* time it fires as well, which is
probably what you want.

CAVEAT EMPTOR: I have not tested this code.

Breck

ALTER TRIGGER "TIME_TRIG" AFTER UPDATE
ORDER 1 ON "DBA"."DOCS"
REFERENCING OLD AS old_record NEW AS new_record
FOR EACH ROW
BEGIN
IF old_record.CHANGED_DATE = new_record.CHANGED_DATE THEN
-- This is the first time this trigger has fired for this row.
update DBA.DOCS set CHANGED_DATE = getdate(*)
WHERE DOCS.PKEY = old_record.PKEY;
ELSE
-- Do not do anything the second time the trigger fires.
END IF;
END


On 9 Nov 2007 07:44:11 -0800, "Hedley Sohn" <tcarlson@geographix.com>

wrote:

>(I apologize if this is answered somewhere. I haven't been able to find
>anything related on the support site)
>
>We have ASA 9.0. We have to keep a DATE field updated with the current time.
>We CANNOT define the field with DEFAULT TIMESTAMP because the user
>applications sometimes issue UPDATE statements when nothing about the row
>has changed. This then causes a new TIMESTAMP but the data in the record
>hasn't really changed. We have a busines rule that the DATE field is only
>updated when the record is inserted or when some field within the record has
>actually been changed.
>
>So, we were forced to implement this with INSERT and UPDATE triggers. The
>problem we have is that the insert after trigger we use is written as an
>update to it's table:
>
>ALTER TRIGGER "TIME_TRIG" AFTER INSERT
>ORDER 1 ON "DBA"."DOCS"
>REFERENCING NEW as new_insert
>FOR EACH ROW
>BEGIN
>update DBA.DOCS set CHANGED_DATE = getdate(*)
>WHERE DOCS.PKEY = new_insert.PKEY
>END
>
>The Update trigger:
>ALTER TRIGGER "TIME_TRIG" AFTER UPDATE
>ORDER 1 ON "DBA"."DOCS"
>REFERENCING OLD AS old_record
>FOR EACH ROW
>BEGIN
>update DBA.DOCS set CHANGED_DATE = getdate(*)
>WHERE DOCS.PKEY = old_record.PKEY
>END
>
>then has it's own update statement.
>
>The problem is that the update trigger can sometimes get into an extreme
>recursive loop which then causes an exception. This is also very
>inefficient.
>
>Can anyone suggest a better way to handle this using triggers. Remember, we
>simply cannot define the COLUMN as a DEFAULT TIMESTAMP (although we'd love
>to)
>
>Thanks,
>
>-HS
>

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
breck.carter@risingroad.com