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.

Help with statement trigger please --

4 posts in General Discussion Last posting was on 2009-06-15 11:38:18.0Z
David Shuman Posted on 2009-06-11 01:56:33.0Z
From: "David Shuman" <d.shuman@att.net>
Subject: Help with statement trigger please --
Newsgroups: ianywhere.public.general
X-Antivirus: avast! (VPS 090610-0, 06/10/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a306451@forums-1-dub>
Date: 10 Jun 2009 18:56:33 -0700
X-Trace: forums-1-dub 1244685393 10.22.241.152 (10 Jun 2009 18:56:33 -0700)
X-Original-Trace: 10 Jun 2009 18:56:33 -0700, vip152.sybase.com
Lines: 45
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7605
Article PK: 5999

The documentation in this area is lacking in my opinion and after three hours please help.

The execution error indicates there were no rows found / updated --

Since these worked as row triggers I suspect the error is in the
ROWID(newkey) = ROWID(oldkey) part of the where clause
Is there another answer for this I am missing?


CREATE TRIGGER data.ind.ind_a30110u_sub AFTER UPDATE
OF ind,indname
ORDER 30110 ON data.ind
REFERENCING old as oldkey
new as newkey
FOR EACH STATEMENT
BEGIN
//
// CASCADE UPDATE row, if required
//
update data.sub
set ind_xref = newkey.ind,indname = newkey.indname
from newkey, oldkey
where ROWID(newkey) = ROWID(oldkey) and
sub.ind_xref = oldkey.ind and sub.indname = oldkey.indname;
END;


CREATE TRIGGER data.ind.ind_a30120u_src AFTER UPDATE
OF ind
ORDER 30120 ON data.ind
REFERENCING old as oldkey
new as newkey
FOR EACH STATEMENT
BEGIN
//
// CASCADE UPDATE row, if required
//
update data.src
set ind = newkey.ind
from newkey, oldkey
where ROWID(newkey) = ROWID(oldkey) and
src.ind = oldkey.ind;
END;


Glenn Paulley [Sybase iAnywhere] Posted on 2009-06-11 02:15:37.0Z
From: "Glenn Paulley [Sybase iAnywhere]" <paulley@ianywhere.com>
Reply-To: paulley@ianywhere.com
Organization: Sybase iAnywhere
User-Agent: Thunderbird 2.0.0.21 (Windows/20090302)
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: Help with statement trigger please --
References: <4a306451@forums-1-dub>
In-Reply-To: <4a306451@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a3068c9$1@forums-1-dub>
Date: 10 Jun 2009 19:15:37 -0700
X-Trace: forums-1-dub 1244686537 10.22.241.152 (10 Jun 2009 19:15:37 -0700)
X-Original-Trace: 10 Jun 2009 19:15:37 -0700, vip152.sybase.com
Lines: 93
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7606
Article PK: 6001

From the help (CREATE TRIGGER -> syntax), you'll note that

For statement-level triggers, the REFERENCING OLD and REFERENCING NEW
clauses refer to declared temporary tables holding the old and new
values of the rows. The default names for these tables are deleted and
inserted.

Since newkey and oldkey refer to different tables, it is impossible for

ROWID(newkey) = ROWID(oldkey)

because there is no possibility of the row identifiers being equal. You
want to compare attribute *values* of the two rows - likely their
primary keys - rather than their physical row identifiers.

Moreover I note the following from the documentation for the ROWID function:

The value returned by the function is not necessarily constant between
queries as various operations performed on the database may result in
changes to the row identifiers of a table. In particular, the REORGANIZE
TABLE statement is likely to result in changes to row identifiers.
Additionally, row identifiers may be reused after a row has been
deleted. Hence users should refrain from using the ROWID function in
ordinary situations; retrieval by primary key value should be used
instead. It is recommended that ROWID be used only in diagnostic situations.

Glenn

David Shuman wrote:
> The documentation in this area is lacking in my opinion and after three hours please help.
>
> The execution error indicates there were no rows found / updated --
>
> Since these worked as row triggers I suspect the error is in the
> ROWID(newkey) = ROWID(oldkey) part of the where clause
> Is there another answer for this I am missing?
>
>
> CREATE TRIGGER data.ind.ind_a30110u_sub AFTER UPDATE
> OF ind,indname
> ORDER 30110 ON data.ind
> REFERENCING old as oldkey
> new as newkey
> FOR EACH STATEMENT
> BEGIN
> //
> // CASCADE UPDATE row, if required
> //
> update data.sub
> set ind_xref = newkey.ind,indname = newkey.indname
> from newkey, oldkey
> where ROWID(newkey) = ROWID(oldkey) and
> sub.ind_xref = oldkey.ind and sub.indname = oldkey.indname;
> END;
>
>
> CREATE TRIGGER data.ind.ind_a30120u_src AFTER UPDATE
> OF ind
> ORDER 30120 ON data.ind
> REFERENCING old as oldkey
> new as newkey
> FOR EACH STATEMENT
> BEGIN
> //
> // CASCADE UPDATE row, if required
> //
> update data.src
> set ind = newkey.ind
> from newkey, oldkey
> where ROWID(newkey) = ROWID(oldkey) and
> src.ind = oldkey.ind;
> END;
>
>

--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://case-express.sybase.com

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the
Sybase iAnywhere pages at
http://www.sybase.com/products/databasemanagement/sqlanywhere/technicalsupport


David Shuman Posted on 2009-06-11 10:55:57.0Z
From: "David Shuman" <d.shuman@att.net>
Subject: Re: Help with statement trigger please --
Newsgroups: ianywhere.public.general
References: <4a3068c9$1@forums-1-dub>
X-Antivirus: avast! (VPS 090610-0, 06/10/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a30e2bd$1@forums-1-dub>
Date: 11 Jun 2009 03:55:57 -0700
X-Trace: forums-1-dub 1244717757 10.22.241.152 (11 Jun 2009 03:55:57 -0700)
X-Original-Trace: 11 Jun 2009 03:55:57 -0700, vip152.sybase.com
Lines: 103
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7609
Article PK: 6002

Thanks -- BUT----

Since the old and new rows potentially have different primary keys in terms of my data, I assume an element has been added such as ID
that provides a common key between the old and new tables. If that assumption is correct (T-SQL uses a column named ID, I believe);
what does SQL ANYWHERE use?

> From the help (CREATE TRIGGER -> syntax), you'll note that
>
>For statement-level triggers, the REFERENCING OLD and REFERENCING NEW
>clauses refer to declared temporary tables holding the old and new
>values of the rows. The default names for these tables are deleted and
>inserted.
>
>Since newkey and oldkey refer to different tables, it is impossible for
>
>ROWID(newkey) = ROWID(oldkey)
>
>because there is no possibility of the row identifiers being equal. You
>want to compare attribute *values* of the two rows - likely their
>primary keys - rather than their physical row identifiers.
>
>Moreover I note the following from the documentation for the ROWID function:
>
>The value returned by the function is not necessarily constant between
>queries as various operations performed on the database may result in
>changes to the row identifiers of a table. In particular, the REORGANIZE
>TABLE statement is likely to result in changes to row identifiers.
>Additionally, row identifiers may be reused after a row has been
>deleted. Hence users should refrain from using the ROWID function in
>ordinary situations; retrieval by primary key value should be used
>instead. It is recommended that ROWID be used only in diagnostic situations.
>
>Glenn
>
>David Shuman wrote:
>> The documentation in this area is lacking in my opinion and after three hours please help.
>>
>> The execution error indicates there were no rows found / updated --
>>
>> Since these worked as row triggers I suspect the error is in the
>> ROWID(newkey) = ROWID(oldkey) part of the where clause
>> Is there another answer for this I am missing?
>>
>>
>> CREATE TRIGGER data.ind.ind_a30110u_sub AFTER UPDATE
>> OF ind,indname
>> ORDER 30110 ON data.ind
>> REFERENCING old as oldkey
>> new as newkey
>> FOR EACH STATEMENT
>> BEGIN
>> //
>> // CASCADE UPDATE row, if required
>> //
>> update data.sub
>> set ind_xref = newkey.ind,indname = newkey.indname
>> from newkey, oldkey
>> where ROWID(newkey) = ROWID(oldkey) and
>> sub.ind_xref = oldkey.ind and sub.indname = oldkey.indname;
>> END;
>>
>>
>> CREATE TRIGGER data.ind.ind_a30120u_src AFTER UPDATE
>> OF ind
>> ORDER 30120 ON data.ind
>> REFERENCING old as oldkey
>> new as newkey
>> FOR EACH STATEMENT
>> BEGIN
>> //
>> // CASCADE UPDATE row, if required
>> //
>> update data.src
>> set ind = newkey.ind
>> from newkey, oldkey
>> where ROWID(newkey) = ROWID(oldkey) and
>> src.ind = oldkey.ind;
>> END;
>>
>>
>
>--
>Glenn Paulley
>Director, Engineering (Query Processing)
>Sybase iAnywhere
>
>Blog: http://iablog.sybase.com/paulley
>
>EBF's and Patches: http://downloads.sybase.com
> choose SQL Anywhere Studio >> change 'time frame' to all
>
>To Submit Bug Reports: http://case-express.sybase.com
>
>SQL Anywhere Studio Supported Platforms and Support Status
>http://my.sybase.com/detail?id=1002288
>
>Whitepapers, TechDocs, and bug fixes are all available through the
>Sybase iAnywhere pages at
>http://www.sybase.com/products/databasemanagement/sqlanywhere/technicalsupport
>
>


Breck Carter [TeamSybase] Posted on 2009-06-15 11:38:18.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__breck.carter@gmail.com>
Newsgroups: ianywhere.public.general
Subject: Re: Help with statement trigger please --
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__breck.carter@gmail.com
Message-ID: <nccc355iimthumsbd63vke1qiocl1k6ss9@4ax.com>
References: <4a3068c9$1@forums-1-dub> <4a30e2bd$1@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: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 15 Jun 2009 04:38:18 -0700
X-Trace: forums-1-dub 1245065898 10.22.241.152 (15 Jun 2009 04:38:18 -0700)
X-Original-Trace: 15 Jun 2009 04:38:18 -0700, vip152.sybase.com
Lines: 124
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7619
Article PK: 6013

AFAIK that feature doesn't exist in SQL Anywhere, but you can ask for
it in the product_futures_discussion newsgroup.

As a workaround, consider using a row-level trigger where there is an
implicit one-to-one relationship between the old and new rows. For
most situations, row-level triggers are much easier to use because the
old and new rows are presented as single-row structures; i.e., no
fetch loops required to process the columns, just direct references
"old_t.column".

Breck


On 11 Jun 2009 03:55:57 -0700, "David Shuman" <d.shuman@att.net>

wrote:

>Thanks -- BUT----
>
>Since the old and new rows potentially have different primary keys in terms of my data, I assume an element has been added such as ID
>that provides a common key between the old and new tables. If that assumption is correct (T-SQL uses a column named ID, I believe);
>what does SQL ANYWHERE use?
>
>
>> From the help (CREATE TRIGGER -> syntax), you'll note that
>>
>>For statement-level triggers, the REFERENCING OLD and REFERENCING NEW
>>clauses refer to declared temporary tables holding the old and new
>>values of the rows. The default names for these tables are deleted and
>>inserted.
>>
>>Since newkey and oldkey refer to different tables, it is impossible for
>>
>>ROWID(newkey) = ROWID(oldkey)
>>
>>because there is no possibility of the row identifiers being equal. You
>>want to compare attribute *values* of the two rows - likely their
>>primary keys - rather than their physical row identifiers.
>>
>>Moreover I note the following from the documentation for the ROWID function:
>>
>>The value returned by the function is not necessarily constant between
>>queries as various operations performed on the database may result in
>>changes to the row identifiers of a table. In particular, the REORGANIZE
>>TABLE statement is likely to result in changes to row identifiers.
>>Additionally, row identifiers may be reused after a row has been
>>deleted. Hence users should refrain from using the ROWID function in
>>ordinary situations; retrieval by primary key value should be used
>>instead. It is recommended that ROWID be used only in diagnostic situations.
>>
>>Glenn
>>
>>David Shuman wrote:
>>> The documentation in this area is lacking in my opinion and after three hours please help.
>>>
>>> The execution error indicates there were no rows found / updated --
>>>
>>> Since these worked as row triggers I suspect the error is in the
>>> ROWID(newkey) = ROWID(oldkey) part of the where clause
>>> Is there another answer for this I am missing?
>>>
>>>
>>> CREATE TRIGGER data.ind.ind_a30110u_sub AFTER UPDATE
>>> OF ind,indname
>>> ORDER 30110 ON data.ind
>>> REFERENCING old as oldkey
>>> new as newkey
>>> FOR EACH STATEMENT
>>> BEGIN
>>> //
>>> // CASCADE UPDATE row, if required
>>> //
>>> update data.sub
>>> set ind_xref = newkey.ind,indname = newkey.indname
>>> from newkey, oldkey
>>> where ROWID(newkey) = ROWID(oldkey) and
>>> sub.ind_xref = oldkey.ind and sub.indname = oldkey.indname;
>>> END;
>>>
>>>
>>> CREATE TRIGGER data.ind.ind_a30120u_src AFTER UPDATE
>>> OF ind
>>> ORDER 30120 ON data.ind
>>> REFERENCING old as oldkey
>>> new as newkey
>>> FOR EACH STATEMENT
>>> BEGIN
>>> //
>>> // CASCADE UPDATE row, if required
>>> //
>>> update data.src
>>> set ind = newkey.ind
>>> from newkey, oldkey
>>> where ROWID(newkey) = ROWID(oldkey) and
>>> src.ind = oldkey.ind;
>>> END;
>>>
>>>
>>
>>--
>>Glenn Paulley
>>Director, Engineering (Query Processing)
>>Sybase iAnywhere
>>
>>Blog: http://iablog.sybase.com/paulley
>>
>>EBF's and Patches: http://downloads.sybase.com
>> choose SQL Anywhere Studio >> change 'time frame' to all
>>
>>To Submit Bug Reports: http://case-express.sybase.com
>>
>>SQL Anywhere Studio Supported Platforms and Support Status
>>http://my.sybase.com/detail?id=1002288
>>
>>Whitepapers, TechDocs, and bug fixes are all available through the
>>Sybase iAnywhere pages at
>>http://www.sybase.com/products/databasemanagement/sqlanywhere/technicalsupport
>>
>>

--
Breck Carter http://sqlanywhere.blogspot.com/

RisingRoad SQL Anywhere and MobiLink Professional Services
breck.carter@risingroad.com