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.

Recursive or not

12 posts in Trigger Last posting was on 2004-08-05 22:26:29.0Z
Francois Posted on 2004-08-03 04:08:32.0Z
Reply-To: "francois" <francois@geedee.com.au>
From: "francois" <francois@geedee.com.au>
Newsgroups: Advantage.Trigger
Subject: Recursive or not
Date: Tue, 3 Aug 2004 12:08:32 +0800
Lines: 27
Organization: geedee
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
NNTP-Posting-Host: 202.72.180.220
Message-ID: <410f10dd@solutions.advantagedatabase.com>
X-Trace: 2 Aug 2004 22:13:17 -0700, 202.72.180.220
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!202.72.180.220
Xref: solutions.advantagedatabase.com Advantage.Trigger:102
Article PK: 1136168

Hi
In the trigger examples for INSTEAD OF INSERT and INSTEAD OF UPDATE two
different methods are used.
INSTEAD OF INSERT
updates the record in __New and then posts it by
INSERT INTO Table SELECT * FROM __New
while
INSTEAD OF UPDATE update one field in the table after ensuring that it is
the same record as in __New
UPDATE Table SET Tme = now()
WHERE RecID = (SELECT RecID from __New)

Q1: why does the last trigger not go into a loop?
Q2: INSTEAD OF INSERT adds the whole record in __New to Table but in the
INSTEAD OF UPDATE trigger only one field is updated. What happens to the
rest of the fields in __New that has been changed from inside Delphi? Are
these changes happening before or after the INSTEAD OF UPDATE is triggered.
To me it looks like the record in __New is already posted which is why
changes are made directly to the Table and not to __New
Q3: INSERT INTO Table SELECT * FROM __New copies the whole record and since
it did not exist. How would the whole record in the INSTEAD OF UPDATE be
written to a record that already exists, without specifying field by field?

Regards
Francois


Jeremy D. Mullin Posted on 2004-08-03 22:52:29.0Z
From: Jeremy D. Mullin <no@email.com>
Newsgroups: Advantage.Trigger
Subject: Re: Recursive or not
Date: Tue, 3 Aug 2004 16:52:29 -0600
Message-ID: <MPG.1b79c513bb52658989d1e@solutions.advantagedatabase.com>
References: <410f10dd@solutions.advantagedatabase.com>
Organization: ESI
X-Newsreader: MicroPlanet Gravity v2.30
NNTP-Posting-Host: 198.102.102.187
X-Trace: 3 Aug 2004 16:58:41 -0700, 198.102.102.187
Lines: 33
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!198.102.102.187
Xref: solutions.advantagedatabase.com Advantage.Trigger:103
Article PK: 1136170

In article <410f10dd@solutions.advantagedatabase.com>,
francois@geedee.com.au says...
> Q1: why does the last trigger not go into a loop?

We're smart.

> Q2: INSTEAD OF INSERT adds the whole record in __New to Table but in the
> INSTEAD OF UPDATE trigger only one field is updated. What happens to the
> rest of the fields in __New that has been changed from inside Delphi?

They are ignored.

> Are
> these changes happening before or after the INSTEAD OF UPDATE is triggered.

They aren't happening at all, because this is an INSTEAD OF trigger, so
the operations inside the trigger happen, instead of the original
update.

> To me it looks like the record in __New is already posted which is why
> changes are made directly to the Table and not to __New

This is not the case.

> Q3: INSERT INTO Table SELECT * FROM __New copies the whole record and since
> it did not exist. How would the whole record in the INSTEAD OF UPDATE be
> written to a record that already exists, without specifying field by field?

It wouldn't be.


J.D. Mullin
Advantage R&D


francois Posted on 2004-08-04 00:01:30.0Z
Reply-To: "francois" <fransh_@westnet.com.au>
From: "francois" <fransh_@westnet.com.au>
Newsgroups: Advantage.Trigger
References: <410f10dd@solutions.advantagedatabase.com> <MPG.1b79c513bb52658989d1e@solutions.advantagedatabase.com>
Subject: Re: Recursive or not
Date: Wed, 4 Aug 2004 08:01:30 +0800
Lines: 20
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
NNTP-Posting-Host: 202.72.164.126
Message-ID: <411028e1@solutions.advantagedatabase.com>
X-Trace: 3 Aug 2004 18:08:01 -0700, 202.72.164.126
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!202.72.164.126
Xref: solutions.advantagedatabase.com Advantage.Trigger:106
Article PK: 1136174

JD

So why then do you allow 64 levels of recursion and nested triggers
or rather how do you make a trigger recursive?

Regards
Francois

"Jeremy D. Mullin" <no@email.com> wrote in message
news:MPG.1b79c513bb52658989d1e@solutions.advantagedatabase.com...
> In article <410f10dd@solutions.advantagedatabase.com>,
> francois@geedee.com.au says...
> > Q1: why does the last trigger not go into a loop?
>
> We're smart.

> J.D. Mullin
> Advantage R&D


Jeremy D. Mullin Posted on 2004-08-04 15:03:22.0Z
From: Jeremy D. Mullin <no@email.com>
Newsgroups: Advantage.Trigger
Subject: Re: Recursive or not
Date: Wed, 4 Aug 2004 09:03:22 -0600
Message-ID: <MPG.1b7aa8a1665e3ffe989d20@solutions.advantagedatabase.com>
References: <410f10dd@solutions.advantagedatabase.com> <MPG.1b79c513bb52658989d1e@solutions.advantagedatabase.com> <411028e1@solutions.advantagedatabase.com>
Organization: ESI
X-Newsreader: MicroPlanet Gravity v2.30
NNTP-Posting-Host: 198.102.102.187
X-Trace: 4 Aug 2004 09:10:06 -0700, 198.102.102.187
Lines: 15
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!198.102.102.187
Xref: solutions.advantagedatabase.com Advantage.Trigger:108
Article PK: 1136175

In article <411028e1@solutions.advantagedatabase.com>,
fransh_@westnet.com.au says...

> JD
>
> So why then do you allow 64 levels of recursion and nested triggers
> or rather how do you make a trigger recursive?

I see your question now.

If you update the same record that caused the trigger to fire, that is
allowed and will not cause the same trigger to fire again. If you modify
a different record, the trigger will fire again.

J.D. Mullin
Advantage R&D


francois Posted on 2004-08-04 17:02:14.0Z
Reply-To: "francois" <fransh_@westnet.com.au>
From: "francois" <fransh_@westnet.com.au>
Newsgroups: Advantage.Trigger
References: <410f10dd@solutions.advantagedatabase.com> <MPG.1b79c513bb52658989d1e@solutions.advantagedatabase.com> <411028e1@solutions.advantagedatabase.com> <MPG.1b7aa8a1665e3ffe989d20@solutions.advantagedatabase.com>
Subject: Re: Recursive or not
Date: Thu, 5 Aug 2004 01:02:14 +0800
Lines: 9
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
NNTP-Posting-Host: 202.72.164.126
Message-ID: <411117fb@solutions.advantagedatabase.com>
X-Trace: 4 Aug 2004 11:08:11 -0700, 202.72.164.126
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!202.72.164.126
Xref: solutions.advantagedatabase.com Advantage.Trigger:113
Article PK: 1136179


"Jeremy D. Mullin" <no@email.com> wrote
> If you update the same record that caused the trigger to fire, that is
> allowed and will not cause the same trigger to fire again. If you modify
> a different record, the trigger will fire again.

Thanks
Francois


Jeremy D. Mullin Posted on 2004-08-03 22:54:59.0Z
From: Jeremy D. Mullin <no@email.com>
Newsgroups: Advantage.Trigger
Subject: Re: Recursive or not
Date: Tue, 3 Aug 2004 16:54:59 -0600
Message-ID: <MPG.1b79c5b1d189527a989d1f@solutions.advantagedatabase.com>
References: <410f10dd@solutions.advantagedatabase.com>
Organization: ESI
X-Newsreader: MicroPlanet Gravity v2.30
NNTP-Posting-Host: 198.102.102.187
X-Trace: 3 Aug 2004 17:01:12 -0700, 198.102.102.187
Lines: 13
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!198.102.102.187
Xref: solutions.advantagedatabase.com Advantage.Trigger:104
Article PK: 1136172

In article <410f10dd@solutions.advantagedatabase.com>,
francois@geedee.com.au says...

> INSTEAD OF UPDATE update one field in the table after ensuring that it is
> the same record as in __New
> UPDATE Table SET Tme = now()
> WHERE RecID = (SELECT RecID from __New)

Where did you find this example? I can modify it so it doesn't imply the
__new table changes were already made.


J.D. Mullin
Advantage R&D


francois Posted on 2004-08-03 23:59:15.0Z
Reply-To: "francois" <fransh_@westnet.com.au>
From: "francois" <fransh_@westnet.com.au>
Newsgroups: Advantage.Trigger
References: <410f10dd@solutions.advantagedatabase.com> <MPG.1b79c5b1d189527a989d1f@solutions.advantagedatabase.com>
Subject: Re: Recursive or not
Date: Wed, 4 Aug 2004 07:59:15 +0800
Lines: 49
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
NNTP-Posting-Host: 202.72.164.126
Message-ID: <41102859@solutions.advantagedatabase.com>
X-Trace: 3 Aug 2004 18:05:45 -0700, 202.72.164.126
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!202.72.164.126
Xref: solutions.advantagedatabase.com Advantage.Trigger:105
Article PK: 1136171

JD
I have a problem with INSTEAD OF UPDATE
Changes to this table are made by a trigger in another table.
I only want to add a timestamp to the record when it is changed.
This will nullify all the changes made by the other trigger.

I'm having trouble understanding why the values in __New are not autoposted
when the trigger exists.
ADS has the __Old record
ADS has the __New record
ADS allows me to make changes to the __New record (one field: adding the
timestamp)
Then ADS ignores the whole lot unless I copy each field to the original
table which in itself is a new update of that record directly in that table.

So just to make the point:
if I have 20 tables that I just want to add a timestamp to, I will have to
write a trigger that adds the Timestamp and then for each table copy each
field from __New to the table.

Regards
Francois

"Jeremy D. Mullin" <no@email.com> wrote in message
news:MPG.1b79c5b1d189527a989d1f@solutions.advantagedatabase.com...
> In article <410f10dd@solutions.advantagedatabase.com>,
> francois@geedee.com.au says...
> > INSTEAD OF UPDATE update one field in the table after ensuring that it
is
> > the same record as in __New
> > UPDATE Table SET Tme = now()
> > WHERE RecID = (SELECT RecID from __New)
>
> Where did you find this example? I can modify it so it doesn't imply the
> __new table changes were already made.

===========================================
Just my adaptation from the help topic
Trigger Types (BEFORE, INSTEAD OF, and AFTER) in Advantage Concepts

UPDATE customers SET lastupdated = now(),
name = ( SELECT name FROM __new )
WHERE id = ( SELECT id FROM __new )
============================================
>
>
> J.D. Mullin
> Advantage R&D


Jeremy D. Mullin Posted on 2004-08-04 15:06:43.0Z
From: Jeremy D. Mullin <no@email.com>
Newsgroups: Advantage.Trigger
Subject: Re: Recursive or not
Date: Wed, 4 Aug 2004 09:06:43 -0600
Message-ID: <MPG.1b7aa96c62dcdb8b989d21@solutions.advantagedatabase.com>
References: <410f10dd@solutions.advantagedatabase.com> <MPG.1b79c5b1d189527a989d1f@solutions.advantagedatabase.com> <41102859@solutions.advantagedatabase.com>
Organization: ESI
X-Newsreader: MicroPlanet Gravity v2.30
NNTP-Posting-Host: 198.102.102.187
X-Trace: 4 Aug 2004 09:13:26 -0700, 198.102.102.187
Lines: 64
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!198.102.102.187
Xref: solutions.advantagedatabase.com Advantage.Trigger:109
Article PK: 1136176

I'm aware of this issue. It gets even more complicated because if you
later change your record structure you have to remember to modify the
trigger too.

Version 8.0 will allow AFTER triggers to modify the same record that
caused the trigger to fire. This will allow you to use and AFTER trigger
to set the timestamp of a single field after the main contents of the
record have been posted.

J.D. Mullin
Advantage R&D

In article <41102859@solutions.advantagedatabase.com>,
fransh_@westnet.com.au says...

> JD
> I have a problem with INSTEAD OF UPDATE
> Changes to this table are made by a trigger in another table.
> I only want to add a timestamp to the record when it is changed.
> This will nullify all the changes made by the other trigger.
>
> I'm having trouble understanding why the values in __New are not autoposted
> when the trigger exists.
> ADS has the __Old record
> ADS has the __New record
> ADS allows me to make changes to the __New record (one field: adding the
> timestamp)
> Then ADS ignores the whole lot unless I copy each field to the original
> table which in itself is a new update of that record directly in that table.
>
> So just to make the point:
> if I have 20 tables that I just want to add a timestamp to, I will have to
> write a trigger that adds the Timestamp and then for each table copy each
> field from __New to the table.
>
> Regards
> Francois
>
> "Jeremy D. Mullin" <no@email.com> wrote in message
> news:MPG.1b79c5b1d189527a989d1f@solutions.advantagedatabase.com...
> > In article <410f10dd@solutions.advantagedatabase.com>,
> > francois@geedee.com.au says...
> > > INSTEAD OF UPDATE update one field in the table after ensuring that it
> is
> > > the same record as in __New
> > > UPDATE Table SET Tme = now()
> > > WHERE RecID = (SELECT RecID from __New)
> >
> > Where did you find this example? I can modify it so it doesn't imply the
> > __new table changes were already made.
> ===========================================
> Just my adaptation from the help topic
> Trigger Types (BEFORE, INSTEAD OF, and AFTER) in Advantage Concepts
>
> UPDATE customers SET lastupdated = now(),
> name = ( SELECT name FROM __new )
> WHERE id = ( SELECT id FROM __new )
> ============================================
> >
> >
> > J.D. Mullin
> > Advantage R&D
>
>
>


francois Posted on 2004-08-04 17:01:29.0Z
Reply-To: "francois" <fransh_@westnet.com.au>
From: "francois" <fransh_@westnet.com.au>
Newsgroups: Advantage.Trigger
References: <410f10dd@solutions.advantagedatabase.com> <MPG.1b79c5b1d189527a989d1f@solutions.advantagedatabase.com> <41102859@solutions.advantagedatabase.com> <MPG.1b7aa96c62dcdb8b989d21@solutions.advantagedatabase.com>
Subject: Re: Recursive or not
Date: Thu, 5 Aug 2004 01:01:29 +0800
Lines: 10
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
NNTP-Posting-Host: 202.72.164.126
Message-ID: <411117ce@solutions.advantagedatabase.com>
X-Trace: 4 Aug 2004 11:07:26 -0700, 202.72.164.126
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!202.72.164.126
Xref: solutions.advantagedatabase.com Advantage.Trigger:112
Article PK: 1136180


"Jeremy D. Mullin" <no@email.com> wrote
> Version 8.0 will allow AFTER triggers to modify the same record that
> caused the trigger to fire. This will allow you to use and AFTER trigger
> to set the timestamp of a single field after the main contents of the
> record have been posted.

Thanks
Francois


francois Posted on 2004-08-04 17:39:50.0Z
Reply-To: "francois" <fransh_@westnet.com.au>
From: "francois" <fransh_@westnet.com.au>
Newsgroups: Advantage.Trigger
References: <410f10dd@solutions.advantagedatabase.com> <MPG.1b79c5b1d189527a989d1f@solutions.advantagedatabase.com> <41102859@solutions.advantagedatabase.com> <MPG.1b7aa96c62dcdb8b989d21@solutions.advantagedatabase.com> <411117ce@solutions.advantagedatabase.com>
Subject: Re: Recursive or not
Date: Thu, 5 Aug 2004 01:39:50 +0800
Lines: 27
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
NNTP-Posting-Host: 202.72.164.126
Message-ID: <411120cc@solutions.advantagedatabase.com>
X-Trace: 4 Aug 2004 11:45:48 -0700, 202.72.164.126
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!202.72.164.126
Xref: solutions.advantagedatabase.com Advantage.Trigger:115
Article PK: 1136184

JD
Sounds OK in principle but is the INSTEAD OF INSERT/UPDATE TRIGGER still
going to stop the AFTER INSERT/UPDATE trigger to fire?
I suggest an Autopost on exit as a better solution.
Any changes made to the __New record should be retained (Except for DELETE)
I also suggest that the INSTEAD OF trigger be superceded/ enhanced by a
BEFORE POST TRIGGER that will fire for both INSERT and UPDATE situations.
As it is now nearly all my triggers are duplicated for INSERT and UPDATE.

Another thing about the enhanced SQL scripting planned for v 8.0:
Being able to save the result of a subselect to a variable would be really
nice.

Regards
Francois

> "Jeremy D. Mullin" <no@email.com> wrote
> > Version 8.0 will allow AFTER triggers to modify the same record that
> > caused the trigger to fire. This will allow you to use and AFTER trigger
> > to set the timestamp of a single field after the main contents of the
> > record have been posted.
> Thanks
> Francois
>
>


Jeremy D. Mullin Posted on 2004-08-05 22:26:29.0Z
From: Jeremy D. Mullin <no@email.com>
Newsgroups: Advantage.Trigger
Subject: Re: Recursive or not
Date: Thu, 5 Aug 2004 16:26:29 -0600
Message-ID: <MPG.1b7c6200d8890cc9989d24@solutions.advantagedatabase.com>
References: <410f10dd@solutions.advantagedatabase.com> <MPG.1b79c5b1d189527a989d1f@solutions.advantagedatabase.com> <41102859@solutions.advantagedatabase.com> <MPG.1b7aa96c62dcdb8b989d21@solutions.advantagedatabase.com> <411117ce@solutions.advantagedatabase.com> <411120cc@solutions.advantagedatabase.com>
Organization: ESI
X-Newsreader: MicroPlanet Gravity v2.30
NNTP-Posting-Host: 198.102.102.187
X-Trace: 5 Aug 2004 16:33:12 -0700, 198.102.102.187
Lines: 36
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!198.102.102.187
Xref: solutions.advantagedatabase.com Advantage.Trigger:116
Article PK: 1136183

Thanks for the suggestions Francois, I'll log them for consideration in
a future release.

J.D. Mullin
Advantage R&D

In article <411120cc@solutions.advantagedatabase.com>,
fransh_@westnet.com.au says...

> JD
> Sounds OK in principle but is the INSTEAD OF INSERT/UPDATE TRIGGER still
> going to stop the AFTER INSERT/UPDATE trigger to fire?
> I suggest an Autopost on exit as a better solution.
> Any changes made to the __New record should be retained (Except for DELETE)
> I also suggest that the INSTEAD OF trigger be superceded/ enhanced by a
> BEFORE POST TRIGGER that will fire for both INSERT and UPDATE situations.
> As it is now nearly all my triggers are duplicated for INSERT and UPDATE.
>
> Another thing about the enhanced SQL scripting planned for v 8.0:
> Being able to save the result of a subselect to a variable would be really
> nice.
>
> Regards
> Francois
>
> > "Jeremy D. Mullin" <no@email.com> wrote
> > > Version 8.0 will allow AFTER triggers to modify the same record that
> > > caused the trigger to fire. This will allow you to use and AFTER trigger
> > > to set the timestamp of a single field after the main contents of the
> > > record have been posted.
> > Thanks
> > Francois
> >
> >
>
>
>


francois Posted on 2004-08-04 17:00:42.0Z
Reply-To: "francois" <fransh_@westnet.com.au>
From: "francois" <fransh_@westnet.com.au>
Newsgroups: Advantage.Trigger
References: <410f10dd@solutions.advantagedatabase.com> <MPG.1b79c5b1d189527a989d1f@solutions.advantagedatabase.com>
Subject: Re: Recursive or not
Date: Thu, 5 Aug 2004 01:00:42 +0800
Lines: 20
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
NNTP-Posting-Host: 202.72.164.126
Message-ID: <4111179f@solutions.advantagedatabase.com>
X-Trace: 4 Aug 2004 11:06:39 -0700, 202.72.164.126
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!202.72.164.126
Xref: solutions.advantagedatabase.com Advantage.Trigger:111
Article PK: 1136178


"Jeremy D. Mullin" <no@email.com> wrote
----8<
> > UPDATE Table SET Tme = now()
> > WHERE RecID = (SELECT RecID from __New)
>
> Where did you find this example? I can modify it so it doesn't imply the
> __new table changes were already made.

----8<
Just my adaptation from the help topic
Trigger Types (BEFORE, INSTEAD OF, and AFTER) in Advantage Concepts

UPDATE customers SET lastupdated = now(),
name = ( SELECT name FROM __new )
WHERE id = ( SELECT id FROM __new )

Regards
Francois