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.

After Insert question

3 posts in Trigger Last posting was on 2006-06-16 23:05:55.0Z
Jim Muir Posted on 2006-06-16 10:49:52.0Z
Date: Fri, 16 Jun 2006 06:49:52 -0400
From: Jim Muir <jpmuirNoSpamPlease@adelphia.net>
User-Agent: Mozilla Thunderbird 1.6.4.0b (Windows/20050716)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: Advantage.Trigger
Subject: After Insert question
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 70.39.141.12
Message-ID: <44928c52@solutions.advantagedatabase.com>
X-Trace: 16 Jun 2006 04:47:46 -0700, 70.39.141.12
Lines: 57
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!70.39.141.12
Xref: solutions.advantagedatabase.com Advantage.Trigger:249
Article PK: 1136317

I feel like this trigger should work but it does not.

I want the trigger to fill in 2 fields with existing data from the
insert that should appear in the __new table after insert.
Assume Emp_FName, Emp_LName are never null.

(ADS version 8.0.0.8)

Why wouldn't this work?

Jim


CREATE TRIGGER UpdateDisplayNamesinStaffRoster
ON StaffRoster
AFTER
INSERT
BEGIN

declare @displayname string, @displayname2 string, @id integer;
declare StaffRosterNew CURSOR;

// this select statement works fine in Native SQL...

OPEN StaffRosterNew AS
Select IBH_ID,
RTRIM(Emp_Lname)+', '+RTRIM(Emp_FName)+'
'+IIF(IFNULL(Rtrim(Emp_MName),'')>'',Left(Emp_MName,1)+'.','')+' '
DisplayName2,
RTRIM(Emp_Fname)+'
'+IIF(IFNULL(Rtrim(Emp_MName),'')>'',Left(Emp_MName,1)+'.','')+RTRIM(Emp_LName)
DisplayName
from __New;

TRY

While Fetch StaffRosterNew DO

@id = StaffRosterNew.IBH_ID;
@displayname = StaffRosterNew.DisplayName;
@displayname2 = StaffRosterNew.DisplayName2;

Update StaffRoster Set StaffRoster.DisplayName = @DisplayName,
StaffRoster.DisplayName2 = @DisplayName2 where StaffRoster.IBH_ID = @id;

End;

FINALLY

CLOSE StaffRosterNew;

END TRY;


END
NO MEMOS
PRIORITY 1;


Jeremy Mullin Posted on 2006-06-16 21:38:23.0Z
Date: Fri, 16 Jun 2006 21:38:23 +0000 (UTC)
Message-ID: <886edc151411b8c85f72098097b0@devzone.advantagedatabase.com>
From: Jeremy Mullin <no@email.com>
Subject: Re: After Insert question
Newsgroups: Advantage.Trigger
References: <44928c52@solutions.advantagedatabase.com>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Content-Type: text/plain; charset=iso-8859-1; format=flowed
X-Newsreader: JetBrains Omea Reader 928.2
NNTP-Posting-Host: 10.24.38.116
X-Trace: 16 Jun 2006 15:34:47 -0700, 10.24.38.116
Lines: 67
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!10.24.38.116
Xref: solutions.advantagedatabase.com Advantage.Trigger:250
Article PK: 1136315

Hi Jim,

It looks OK to me. I created a similar trigger here and it worked fine. Can
you send me the table in question so I can try it here?

Also try closing all connections and then attempting an insert again. Trigger
info is cached on the server, so sometimes if you make changes they won't
be re-read by the server until all clients who have executed the trigger
in the past disconnect.

J.D. Mullin
Advantage R&D

> I feel like this trigger should work but it does not.
>
> I want the trigger to fill in 2 fields with existing data from the
> insert that should appear in the __new table after insert.
> Assume Emp_FName, Emp_LName are never null.
> (ADS version 8.0.0.8)
>
> Why wouldn't this work?
>
> Jim
>
> CREATE TRIGGER UpdateDisplayNamesinStaffRoster
> ON StaffRoster
> AFTER
> INSERT
> BEGIN
> declare @displayname string, @displayname2 string, @id integer;
> declare StaffRosterNew CURSOR;
> // this select statement works fine in Native SQL...
>
> OPEN StaffRosterNew AS
> Select IBH_ID,
> RTRIM(Emp_Lname)+', '+RTRIM(Emp_FName)+'
> '+IIF(IFNULL(Rtrim(Emp_MName),'')>'',Left(Emp_MName,1)+'.','')+' '
> DisplayName2,
> RTRIM(Emp_Fname)+'
> '+IIF(IFNULL(Rtrim(Emp_MName),'')>'',Left(Emp_MName,1)+'.','')+RTRIM(E
> mp_LName)
> DisplayName
> from __New;
> TRY
>
> While Fetch StaffRosterNew DO
>
> @id = StaffRosterNew.IBH_ID;
> @displayname = StaffRosterNew.DisplayName;
> @displayname2 = StaffRosterNew.DisplayName2;
> Update StaffRoster Set StaffRoster.DisplayName = @DisplayName,
> StaffRoster.DisplayName2 = @DisplayName2 where StaffRoster.IBH_ID =
> @id;
>
> End;
>
> FINALLY
>
> CLOSE StaffRosterNew;
>
> END TRY;
>
> END
> NO MEMOS
> PRIORITY 1;


Jim Muir Posted on 2006-06-16 23:05:55.0Z
Date: Fri, 16 Jun 2006 19:05:55 -0400
From: Jim Muir <jpmuirNoSpamPlease@adelphia.net>
User-Agent: Mozilla Thunderbird 1.6.4.0b (Windows/20050716)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: Advantage.Trigger
Subject: Re: After Insert question
References: <44928c52@solutions.advantagedatabase.com> <886edc151411b8c85f72098097b0@devzone.advantagedatabase.com>
In-Reply-To: <886edc151411b8c85f72098097b0@devzone.advantagedatabase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 70.39.141.12
Message-ID: <449338d6@solutions.advantagedatabase.com>
X-Trace: 16 Jun 2006 17:03:50 -0700, 70.39.141.12
Lines: 20
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!70.39.141.12
Xref: solutions.advantagedatabase.com Advantage.Trigger:251
Article PK: 1136316

Thanks for checking Jeremy.
I will try that Monday when I get back to work.
I suspected something like that might be messing with me.

I'll let you know...

Jeremy Mullin wrote:
> Hi Jim,
>
> It looks OK to me. I created a similar trigger here and it worked fine.
> Can you send me the table in question so I can try it here?
>
> Also try closing all connections and then attempting an insert again.
> Trigger info is cached on the server, so sometimes if you make changes
> they won't be re-read by the server until all clients who have executed
> the trigger in the past disconnect.
>
> J.D. Mullin
> Advantage R&D
>