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.

__new table alias problem

3 posts in Trigger Last posting was on 2006-04-06 08:37:24.0Z
Manuel Posted on 2006-04-05 16:18:33.0Z
From: "Manuel" <mavahe@telefonica.net>
Newsgroups: Advantage.Trigger
Subject: __new table alias problem
Date: Wed, 5 Apr 2006 18:18:33 +0200
Lines: 87
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="----=_NextPart_000_000F_01C658DD.597FEB10"
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: 80.33.36.196
Message-ID: <4433eaa5@solutions.advantagedatabase.com>
X-Trace: 5 Apr 2006 10:04:53 -0700, 80.33.36.196
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!80.33.36.196
Xref: solutions.advantagedatabase.com Advantage.Trigger:207
Article PK: 1136272

Hi:
 
I'm trying to write simple BEFORE INSERT  trigger to avoid duplicate keys (Foxpro database format)
When inserting new record in table , I get 7200 error (Table or alias not found: __new )
 
Any help?
 
Regards,
Manuel
 
=========================================================================
ADS Version: 8.00.0.2
 
TEST TABLE STRUCTURE :
CREATE TABLE test ( UID Integer, D1 Date,  TEXT Char( 50 ));
 
TRIGGER SOURCE SQL CODE:
declare c integer;
c = (select count(*) from TEST where TEST.UID = __new.UID);
if(c > 0) then
insert into __error values(0, 'Duplicated key');
endif
=========================================================================


Lance Schmidt Posted on 2006-04-05 16:37:27.0Z
From: "Lance Schmidt" <LanceSc@ExtendSys.com>
Newsgroups: Advantage.Trigger
References: <4433eaa5@solutions.advantagedatabase.com>
Subject: Re: __new table alias problem
Date: Wed, 5 Apr 2006 10:37:27 -0600
Lines: 156
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="----=_NextPart_000_0044_01C6589C.EEC099E0"
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
NNTP-Posting-Host: 198.102.102.91
Message-ID: <4433f17c@solutions.advantagedatabase.com>
X-Trace: 5 Apr 2006 10:34:04 -0700, 198.102.102.91
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!198.102.102.91
Xref: solutions.advantagedatabase.com Advantage.Trigger:208
Article PK: 1136274

Hi Manuel,
 
You currently need to open the __new table as a cursor.  Here is an example that I have been using for the last few days.
 
CREATE TRIGGER Test5bi1InsteadOfInsert
   ON test5bi1
   INSTEAD OF
   INSERT
   BEGIN
      DECLARE CInput CURSOR AS SELECT * FROM __new;
 
      OPEN CInput;
      WHILE FETCH CInput DO
         INSERT INTO test5bi1( Field1 ) VALUES ( CInput.Field1 );
         UPDATE test5i1 SET Field2 = CInput.Field2;
      END; 
   END
   PRIORITY 1;
 
Best Regards,
 
Lance Schmidt
Advantage R&D
"Manuel" <mavahe@telefonica.net> wrote in message news:4433eaa5@solutions.advantagedatabase.com...
Hi:
 
I'm trying to write simple BEFORE INSERT  trigger to avoid duplicate keys (Foxpro database format)
When inserting new record in table , I get 7200 error (Table or alias not found: __new )
 
Any help?
 
Regards,
Manuel
 
=========================================================================
ADS Version: 8.00.0.2
 
TEST TABLE STRUCTURE :
CREATE TABLE test ( UID Integer, D1 Date,  TEXT Char( 50 ));
 
TRIGGER SOURCE SQL CODE:
declare c integer;
c = (select count(*) from TEST where TEST.UID = __new.UID);
if(c > 0) then
insert into __error values(0, 'Duplicated key');
endif
=========================================================================


Joachim Duerr (ADS Support) Posted on 2006-04-06 08:37:24.0Z
From: "Joachim Duerr (ADS Support)" <jojo.duerr@gmx.de>
Subject: Re: __new table alias problem
Newsgroups: Advantage.Trigger
References: <4433eaa5@solutions.advantagedatabase.com>
Organization: iAnywhere
User-Agent: XanaNews/1.18.1.2
X-Face: ,QMv7[luB)BpWAQ~:"kw6n%0ieY63.:g2K3n~8ky0;||5Xle*Xq+=~<Fy:0CVC2nx@8~vZ
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
NNTP-Posting-Host: 195.2.185.25
Message-ID: <4434c534@solutions.advantagedatabase.com>
Date: 6 Apr 2006 01:37:24 -0700
X-Trace: 6 Apr 2006 01:37:24 -0700, 195.2.185.25
Lines: 27
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!195.2.185.25
Xref: solutions.advantagedatabase.com Advantage.Trigger:209
Article PK: 1136276


Manuel wrote in <4433eaa5@solutions.advantagedatabase.com>:

> declare c integer;
> c = (select count(*) from TEST where TEST.UID = __new.UID);
> if(c > 0) then
> insert into __error values(0, 'Duplicated key');
> endif

you need to specify the __new table in the SQL table list:

c = (select count(*) from TEST, __new where TEST.UID = __new.UID);
^^^^^^^^^^

--
Joachim Duerr
Senior Product Support Analyst (Advantage Database Server)
iAnywhere Solutions / Extended Systems
advantage[AT]extendsys.de
*********** European Advantage Conference (EAC) 2006 ****************
Do you want to develop faster, deliver better solutions and drive your
business forward?
Plan to attend now THE Advantage event of the Year!
+++ UK - London, 8.-9. May
+++ GERMANY - Frankfurt, 11.-12. May
+++ ITALY - Milano, 15. May
Click for Details: http://www.AdvantageDatabase.com/EAC2006
*********** European Advantage Conference (EAC) 2006 ****************