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.

Trigger on Proxy Table

3 posts in General Discussion Last posting was on 2011-03-15 19:09:27.0Z
Leod Posted on 2011-03-15 10:52:44.0Z
Sender: 70f3.4d7f43b1.1804289383@sybase.com
From: Leod
Newsgroups: sybase.public.ase.general
Subject: Trigger on Proxy Table
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4d7f44fc.710c.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 15 Mar 2011 02:52:44 -0800
X-Trace: forums-1-dub 1300186364 10.22.241.41 (15 Mar 2011 02:52:44 -0800)
X-Original-Trace: 15 Mar 2011 02:52:44 -0800, 10.22.241.41
Lines: 4
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30039
Article PK: 79268

I'm unable to create working trigger on a proxy table. The
referential table exists already. The trigger gets fired but
it has nothing in "inserted" and/or "deleted" tables. Does
anybody know this issue?


Rob V [ Sybase ] Posted on 2011-03-15 11:12:22.0Z
From: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV / TeamSybase / Sybase
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.15) Gecko/20110303 Lightning/1.0b2 Thunderbird/3.1.9
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Trigger on Proxy Table
References: <4d7f44fc.710c.1681692777@sybase.com>
In-Reply-To: <4d7f44fc.710c.1681692777@sybase.com>
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: <4d7f4996$1@forums-1-dub>
Date: 15 Mar 2011 03:12:22 -0800
X-Trace: forums-1-dub 1300187542 10.22.241.152 (15 Mar 2011 03:12:22 -0800)
X-Original-Trace: 15 Mar 2011 03:12:22 -0800, vip152.sybase.com
Lines: 34
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30040
Article PK: 79269


On 15-Mar-2011 11:52, Leod wrote:
> I'm unable to create working trigger on a proxy table. The
> referential table exists already. The trigger gets fired but
> it has nothing in "inserted" and/or "deleted" tables. Does
> anybody know this issue?

Triggers on proxy tables won't work: for a trigger to work, the
transaction log records must be read in order to construct the
'inserted' and 'deleted' tables. Since the actual modifications (and
hence, the log records) are located in the remote server where the real
table is, it follows that triggers cannot work with proxy tables (I'm
not even going into other issues like a proxy table mapped to an O/S
file or directory, or to a stored procedure).
I'm actually surprised you can create a trigger on a proxy table at all,
but the documentation doesn't seem to say anything about it.

HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


"Mark A. Parsons" <iron_horse Posted on 2011-03-15 19:09:27.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Trigger on Proxy Table
References: <4d7f44fc.710c.1681692777@sybase.com>
In-Reply-To: <4d7f44fc.710c.1681692777@sybase.com>
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: <4d7fb967$1@forums-1-dub>
Date: 15 Mar 2011 11:09:27 -0800
X-Trace: forums-1-dub 1300216167 10.22.241.152 (15 Mar 2011 11:09:27 -0800)
X-Original-Trace: 15 Mar 2011 11:09:27 -0800, vip152.sybase.com
Lines: 24
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30043
Article PK: 79272

As Rob's pointed out, the trigger on the proxy table won't do anything because it doesn't have access to the log records.

If you really need a trigger on the proxy table, and you're running ASE 15.0.2+, and depending on what exactly it is
you're looking to do in the trigger, you could try the following:

- create proxy table

- create a view that references the proxy table (eg, select * from proxy_table)

- create a 'instead of' trigger on the view (must have 15.0.2+)

- perform your triggerable operations against the view

The 'instead of' trigger will provide the logical inserted/deleted tables based on the before image of the data provided
by your triggerable operation. The trigger will fire *before* any changes are attempted against the proxy_table.

Again, whether or not this works for you will depend on what you're trying to do in the trigger and if/how you 'pass
thru' any operations to the proxy table.

On 03/15/2011 06:52, Leod wrote:
> I'm unable to create working trigger on a proxy table. The
> referential table exists already. The trigger gets fired but
> it has nothing in "inserted" and/or "deleted" tables. Does
> anybody know this issue?