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, after insert

8 posts in Trigger Last posting was on 2004-06-22 08:50:43.0Z
Brian A. Baley Posted on 2004-06-18 18:23:40.0Z
From: "Brian A. Baley" <bbaley@4gsys.com>
Newsgroups: Advantage.Trigger
Subject: Trigger, after insert
Date: Fri, 18 Jun 2004 11:23:40 -0700
Lines: 22
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
NNTP-Posting-Host: 216.190.199.98
Message-ID: <40d3350e@solutions.advantagedatabase.com>
X-Trace: 18 Jun 2004 12:31:42 -0700, 216.190.199.98
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!216.190.199.98
Xref: solutions.advantagedatabase.com Advantage.Trigger:69
Article PK: 1136136

Hi,
Can someone explain what I am doing wrong?
(works on other servers, not sure how to convert to ADS properly...)

CREATE TRIGGER tContactName
ON Contact
AFTER INSERT
BEGIN
UPDATE __new
SET ContactName = LastName + ', ' + FirstName
END

- I get (2117) unexpected/extra token ???

ALSO -

Can an Advantage trigger be defined as "ON INSERT, UPDATE" like in SQL
Server? or I need to create both seperately?

Thanks


Brian A. Baley Posted on 2004-06-18 18:41:42.0Z
From: "Brian A. Baley" <bbaley@4gsys.com>
Newsgroups: Advantage.Trigger
References: <40d3350e@solutions.advantagedatabase.com>
Subject: Re: Trigger, after insert
Date: Fri, 18 Jun 2004 11:41:42 -0700
Lines: 5
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
NNTP-Posting-Host: 216.190.199.98
Message-ID: <40d3394c@solutions.advantagedatabase.com>
X-Trace: 18 Jun 2004 12:49:48 -0700, 216.190.199.98
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!216.190.199.98
Xref: solutions.advantagedatabase.com Advantage.Trigger:70
Article PK: 1136137

I was missing my ';''s on the line ends......

Thanks


Brian A. Baley Posted on 2004-06-18 19:09:27.0Z
From: "Brian A. Baley" <bbaley@4gsys.com>
Newsgroups: Advantage.Trigger
References: <40d3350e@solutions.advantagedatabase.com>
Subject: Re: Trigger, after insert
Date: Fri, 18 Jun 2004 12:09:27 -0700
Lines: 46
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
NNTP-Posting-Host: 216.190.199.98
Message-ID: <40d33fcc@solutions.advantagedatabase.com>
X-Trace: 18 Jun 2004 13:17:32 -0700, 216.190.199.98
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!216.190.199.98
Xref: solutions.advantagedatabase.com Advantage.Trigger:71
Article PK: 1136139

I changed the trigger to;
UPDATE __new
SET ContactName =
LTRIM(RTRIM(LastName)) + ', ' +
LTRIM(RTRIM(FirstName));

I know it was executing because I was getting an error about truncating the
data before adding the LTRIM/RTRIM's...

However, nothing happens.....hmmmm....
(meaning ContactName=NULL after insert/update when LastName and FirstName
are not NULL)

Am I still doing something wrong?

If I paste this into an exact duplicate of this DB in SQL Server it works as
expected.... I must be doing something dumb...

"Brian A. Baley" <bbaley@4gsys.com> wrote in message
news:40d3350e@solutions.advantagedatabase.com...
> Hi,
> Can someone explain what I am doing wrong?
> (works on other servers, not sure how to convert to ADS properly...)
>
> CREATE TRIGGER tContactName
> ON Contact
> AFTER INSERT
> BEGIN
> UPDATE __new
> SET ContactName = LastName + ', ' + FirstName
> END
>
> - I get (2117) unexpected/extra token ???
>
> ALSO -
>
> Can an Advantage trigger be defined as "ON INSERT, UPDATE" like in SQL
> Server? or I need to create both seperately?
>
> Thanks
>
>


Joachim Duerr (ADS Support) Posted on 2004-06-21 09:55:23.0Z
From: "Joachim Duerr (ADS Support)" <jojo.duerr@gmx.de>
Subject: Re: Trigger, after insert
Newsgroups: Advantage.Trigger
References: <40d3350e@solutions.advantagedatabase.com> <40d33fcc@solutions.advantagedatabase.com>
Organization: Extended Systems GmbH
User-Agent: XanaNews/1.16.3.1
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
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: 195.2.185.25
Message-ID: <40d6a27b@solutions.advantagedatabase.com>
Date: 21 Jun 2004 02:55:23 -0700
X-Trace: 21 Jun 2004 02:55:23 -0700, 195.2.185.25
Lines: 33
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!195.2.185.25
Xref: solutions.advantagedatabase.com Advantage.Trigger:72
Article PK: 1136138


Brian A. Baley wrote in <40d33fcc@solutions.advantagedatabase.com> :

> I changed the trigger to;
> UPDATE __new
> SET ContactName =
> LTRIM(RTRIM(LastName)) + ', ' +
> LTRIM(RTRIM(FirstName));
>
> I know it was executing because I was getting an error about
> truncating the data before adding the LTRIM/RTRIM's...
>
> However, nothing happens.....hmmmm....
> (meaning ContactName=NULL after insert/update when LastName and
> FirstName are not NULL)
>
> Am I still doing something wrong?
>

That's the ANSI standard defined behaviour.
You should use IFNULL:

LTRIM(RTRIM(IFNULL(lastname,'')))+',
'+LTRIM(RTRIM(IFNULL(firstname,'')))

> If I paste this into an exact duplicate of this DB in SQL Server it
> works as expected.... I must be doing something dumb...

there's no need for ?$ to follow any standard;)

--
Joachim Duerr
EMEA Lead ADS Support, Extended Systems GmbH, Germany
advantage[AT]extendsys.de


Jeremy D. Mullin Posted on 2004-06-21 15:11:00.0Z
From: Jeremy D. Mullin <no@email.com>
Newsgroups: Advantage.Trigger
Subject: Re: Trigger, after insert
Date: Mon, 21 Jun 2004 09:11:00 -0600
Message-ID: <MPG.1b40a87281533439989ce3@solutions.advantagedatabase.com>
References: <40d3350e@solutions.advantagedatabase.com>
Organization: ESI
X-Newsreader: MicroPlanet Gravity v2.30
NNTP-Posting-Host: 198.102.102.187
X-Trace: 21 Jun 2004 09:19:49 -0700, 198.102.102.187
Lines: 34
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!198.102.102.187
Xref: solutions.advantagedatabase.com Advantage.Trigger:73
Article PK: 1136140

Advantage does not apply changes to the __new and __old table directly
to the database. You can use these tables as part of your trigger code,
but you need to post the final changes with something like:

insert into mytable select * from __new

J.D. Mullin
Advantage R&D

In article <40d3350e@solutions.advantagedatabase.com>, bbaley@4gsys.com
says...

> Hi,
> Can someone explain what I am doing wrong?
> (works on other servers, not sure how to convert to ADS properly...)
>
> CREATE TRIGGER tContactName
> ON Contact
> AFTER INSERT
> BEGIN
> UPDATE __new
> SET ContactName = LastName + ', ' + FirstName
> END
>
> - I get (2117) unexpected/extra token ???
>
> ALSO -
>
> Can an Advantage trigger be defined as "ON INSERT, UPDATE" like in SQL
> Server? or I need to create both seperately?
>
> Thanks
>
>
>


Joachim Duerr (ADS Support) Posted on 2004-06-21 16:24:02.0Z
From: "Joachim Duerr (ADS Support)" <jojo.duerr@gmx.de>
Subject: Re: Trigger, after insert
Newsgroups: Advantage.Trigger
References: <40d3350e@solutions.advantagedatabase.com> <MPG.1b40a87281533439989ce3@solutions.advantagedatabase.com>
Organization: Extended Systems GmbH
User-Agent: XanaNews/1.16.3.1
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: <40d6fd92@solutions.advantagedatabase.com>
Date: 21 Jun 2004 09:24:02 -0700
X-Trace: 21 Jun 2004 09:24:02 -0700, 195.2.185.25
Lines: 17
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!195.2.185.25
Xref: solutions.advantagedatabase.com Advantage.Trigger:74
Article PK: 1136141

Jeremy D. Mullin wrote in
<MPG.1b40a87281533439989ce3@solutions.advantagedatabase.com> :

> Advantage does not apply changes to the __new and __old table
> directly to the database. You can use these tables as part of your
> trigger code, but you need to post the final changes with something
> like:
>
> insert into mytable select * from __new

and - btw - he should apply a INSTEAD OF trigger since the BEFORE and
the AFTER triggers don't allow changing the current record!

--
Joachim Duerr
EMEA Lead ADS Support, Extended Systems GmbH, Germany
advantage[AT]extendsys.de


Brian A. Baley Posted on 2004-06-21 16:56:52.0Z
From: "Brian A. Baley" <bbaley@4gsys.com>
Newsgroups: Advantage.Trigger
References: <40d3350e@solutions.advantagedatabase.com> <MPG.1b40a87281533439989ce3@solutions.advantagedatabase.com> <40d6fd92@solutions.advantagedatabase.com>
Subject: Re: Trigger, after insert
Date: Mon, 21 Jun 2004 09:56:52 -0700
Lines: 24
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
NNTP-Posting-Host: 216.190.199.98
Message-ID: <40d71531@solutions.advantagedatabase.com>
X-Trace: 21 Jun 2004 11:04:49 -0700, 216.190.199.98
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!216.190.199.98
Xref: solutions.advantagedatabase.com Advantage.Trigger:75
Article PK: 1136142

thank you...
that makes sense.

"Joachim Duerr (ADS Support)" <jojo.duerr@gmx.de> wrote in message
news:40d6fd92@solutions.advantagedatabase.com...
> Jeremy D. Mullin wrote in
> <MPG.1b40a87281533439989ce3@solutions.advantagedatabase.com> :
>
> > Advantage does not apply changes to the __new and __old table
> > directly to the database. You can use these tables as part of your
> > trigger code, but you need to post the final changes with something
> > like:
> >
> > insert into mytable select * from __new
>
> and - btw - he should apply a INSTEAD OF trigger since the BEFORE and
> the AFTER triggers don't allow changing the current record!
>
> --
> Joachim Duerr
> EMEA Lead ADS Support, Extended Systems GmbH, Germany
> advantage[AT]extendsys.de


Joachim Duerr (ADS Support) Posted on 2004-06-22 08:50:43.0Z
From: "Joachim Duerr (ADS Support)" <jojo.duerr@gmx.de>
Subject: Re: Trigger, after insert
Newsgroups: Advantage.Trigger
References: <40d3350e@solutions.advantagedatabase.com> <MPG.1b40a87281533439989ce3@solutions.advantagedatabase.com> <40d6fd92@solutions.advantagedatabase.com> <40d71531@solutions.advantagedatabase.com>
Organization: Extended Systems GmbH
User-Agent: XanaNews/1.16.3.1
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: <40d7e4d3@solutions.advantagedatabase.com>
Date: 22 Jun 2004 01:50:43 -0700
X-Trace: 22 Jun 2004 01:50:43 -0700, 195.2.185.25
Lines: 22
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!195.2.185.25
Xref: solutions.advantagedatabase.com Advantage.Trigger:76
Article PK: 1136143


Brian A. Baley wrote in <40d71531@solutions.advantagedatabase.com> :

>
> thank you...
> that makes sense.

Let's compile all together:

CREATE TRIGGER tContactName
ON Contact
INSTEAD OF INSERT
BEGIN
UPDATE __new
SET ContactName=LTRIM(RTRIM(ifnull(LastName ,'')))
+', '+LTRIM(RTRIM(ifnull(FirstName,'')));
INSERT INTO Contact SELECT * FROM __new;
END

--
Joachim Duerr
EMEA Lead ADS Support, Extended Systems GmbH, Germany
advantage[AT]extendsys.de