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 Update Trigger using SQL Script

7 posts in Trigger Last posting was on 2006-06-01 19:30:11.0Z
Jim Muir Posted on 2006-05-26 14:54:14.0Z
Date: Fri, 26 May 2006 10:54:14 -0400
From: Jim Muir <jpmuirNoSpamPlease@adelphia.net>
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: Advantage.Trigger
Subject: After Update Trigger using SQL Script
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 65.23.16.202
Message-ID: <44771607@solutions.advantagedatabase.com>
X-Trace: 26 May 2006 08:51:51 -0700, 65.23.16.202
Lines: 40
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!65.23.16.202
Xref: solutions.advantagedatabase.com Advantage.Trigger:235
Article PK: 1136301

I thought I would get trigger-happy with my new version 8 but I am
shooting blanks..

I am not getting any results from the following After Update Trigger
script when a record is changed in Roster table on which the trigger is
defined.

I prototyped the insert statement (uses a Link) and it worked ok but not
as a trigger using _new in place of Roster

I am getting no errors logged or displayed, but neither is the insert
statement putting any record in the LinkTo_IBHiS_System.AlertMsg table.

Any suggestions?

IF _new.DischType is NOT Null THEN

INSERT INTO LinkTo_IBHiS_System.AlertMsg (
Due,
ShortMsgText,
MsgText,
Scope,
Category,
Priority,
MsgType,
Active)

SELECT
Now(),
'Client Status Update',
'Discharge Notice: '+__new.Dischtype+' For '+__new.Caseno+' '+__New.Name,
'Domain Users',
'CeNotice',
'A1',
2020,
TRUE

from __New ;

endif;


Jim Muir Posted on 2006-05-26 19:28:45.0Z
Date: Fri, 26 May 2006 15:28:45 -0400
From: Jim Muir <jpmuirNoSpamPlease@adelphia.net>
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: Advantage.Trigger
Subject: Re: After Update Trigger using SQL Script
References: <44771607@solutions.advantagedatabase.com>
In-Reply-To: <44771607@solutions.advantagedatabase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 65.23.16.202
Message-ID: <4477565e@solutions.advantagedatabase.com>
X-Trace: 26 May 2006 13:26:22 -0700, 65.23.16.202
Lines: 46
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!65.23.16.202
Xref: solutions.advantagedatabase.com Advantage.Trigger:236
Article PK: 1136302


Jim Muir wrote:
> I thought I would get trigger-happy with my new version 8 but I am
> shooting blanks..

I got this to work on a non-production scratch table bakRoster on the
remote server. I changed the script -- changing to a declared string
instead of testing if __new.DischType is NOT Null.

I still cannot get it to work on my production table Roster (no error)

Nor can I get it to use a defined link to another database table with
either the production table nor the bakRoster (scratch) table.



DECLARE dschg String;

dschg = (Select IFNULL(__new.Dischtype,'') from __New);

IF dschg = '' THEN
RETURN;
ELSE
INSERT INTO AlertMsg (
Due,
ShortMsgText,
MsgText,
Scope,
Category,
Priority,
MsgType,
Active)

SELECT
Now(),
'Client Status Update',
'Discharge Notice:,// '+__new.DISCHTYPE +' For '+__new.Caseno+'
'+__New.Name,
'Domain Users',
'CeNotice',
'A1',
2020,
TRUE

from __New ;

endif;


Jim Muir Posted on 2006-05-30 15:40:08.0Z
Date: Tue, 30 May 2006 11:40:08 -0400
From: Jim Muir <jpmuirNoSpamPlease@adelphia.net>
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: Advantage.Trigger
Subject: Re: After Update Trigger using SQL Script
References: <44771607@solutions.advantagedatabase.com>
In-Reply-To: <44771607@solutions.advantagedatabase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 65.23.16.202
Message-ID: <447c66cd@solutions.advantagedatabase.com>
X-Trace: 30 May 2006 09:37:49 -0700, 65.23.16.202
Lines: 29
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!65.23.16.202
Xref: solutions.advantagedatabase.com Advantage.Trigger:237
Article PK: 1136303


Jim Muir wrote:
> I thought I would get trigger-happy with my new version 8 but I am
> shooting blanks..

I am back here. I recreated the Roster table exactly on the remote
server included indeces, field properties, lets call it bak2Roster.

I tested the script trigger against bak2Roster and it works as expected.
However, against the original table, it does not work. Identical tables
except one is in use across the LAN the bak2Roster is not.

I found the "right" error table. And yes the trigger appears to be
returning the following errors:

408 408 05/30/2006 11:17:36 AM 1804 1001 7008 DBF.C 4321
JIMM D:\IBHiS\Databases\ACMS\Roster\ROSTER.ADT 8.00.0.8 Windows
NT 5.2 OPTIMUS-PRIME

409 409 05/30/2006 11:17:36 AM 1820 7000 7112 DBF.C 2196
JIMM arctemp 8.00.0.8 Windows NT 5.2 OPTIMUS-PRIME

410 410 05/30/2006 11:17:36 AM 1784 7000 7112 DBF.C 2196
JIMM arctemp 8.00.0.8 Windows NT 5.2 OPTIMUS-PRIME

I am investigating the Error 7008 regards to the link used in the trigger.

any help appreciated...

Jim


Jim Muir Posted on 2006-05-30 15:54:27.0Z
Date: Tue, 30 May 2006 11:54:27 -0400
From: Jim Muir <jpmuirNoSpamPlease@adelphia.net>
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: Advantage.Trigger
Subject: Re: After Update Trigger using SQL Script
References: <44771607@solutions.advantagedatabase.com>
In-Reply-To: <44771607@solutions.advantagedatabase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 65.23.16.202
Message-ID: <447c6a28@solutions.advantagedatabase.com>
X-Trace: 30 May 2006 09:52:08 -0700, 65.23.16.202
Lines: 38
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!65.23.16.202
Xref: solutions.advantagedatabase.com Advantage.Trigger:238
Article PK: 1136304


Jim Muir wrote:

> I thought I would get trigger-happy with my new version 8 but I am
shooting blanks..


I am back here. I recreated the Roster table exactly on the remote
server included indeces, field properties, lets call it bak2Roster.

I tested the script trigger against bak2Roster and it works as expected.
However, against the original table, it does not work. Identical tables
except one is in use across the LAN the bak2Roster is not.

I found the "right" error table. And yes the trigger appears to be
returning the following errors:

408 408 05/30/2006 11:17:36 AM 1804 1001 7008
DBF.C 4321 JIMM D:\IBHiS\Databases\ACMS\Roster\ROSTER.ADT
8.00.0.8 Windows NT 5.2 OPTIMUS-PRIME

409 409 05/30/2006 11:17:36 AM 1820 7000 7112
DBF.C 2196 JIMM arctemp 8.00.0.8 Windows NT 5.2
OPTIMUS-PRIME

410 410 05/30/2006 11:17:36 AM 1784 7000 7112
DBF.C 2196 JIMM arctemp 8.00.0.8 Windows NT 5.2
OPTIMUS-PRIME

I am investigating the Error 7008 regards to the link used in the trigger.

My next solution is to export the table, sql the indeces and field
properties rename the production table to oldRoster and rename the new
table to roster to see if there is some corruption in the index or
something in the DD that is malfunctioning.

any help appreciated...

Jim


Jeremy Mullin Posted on 2006-05-30 19:05:15.0Z
Date: Tue, 30 May 2006 19:05:15 +0000 (UTC)
Message-ID: <886edc151372b8c85200d411efdc@devzone.advantagedatabase.com>
From: Jeremy Mullin <no@email.com>
Subject: Re: After Update Trigger using SQL Script
Newsgroups: Advantage.Trigger
References: <44771607@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: 30 May 2006 13:01:42 -0700, 10.24.38.116
Lines: 63
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!10.24.38.116
Xref: solutions.advantagedatabase.com Advantage.Trigger:239
Article PK: 1136306

Hi Jim,

Is that your full script? You need to declare a cursor to read the __new
values. Something like this:

declare newvalues cursor as select * from __new;
open newvalues;
try
fetch newvalues;
if ( newvalues.empid = 2 ) then
update demo10 set lastname = 'test' where empid = newvalues.empid;
end;
finally
close newvalues;
end;


J.D. Mullin
Advantage R&D

> I thought I would get trigger-happy with my new version 8 but I am
> shooting blanks..
>
> I am not getting any results from the following After Update Trigger
> script when a record is changed in Roster table on which the trigger
> is defined.
>
> I prototyped the insert statement (uses a Link) and it worked ok but
> not as a trigger using _new in place of Roster
>
> I am getting no errors logged or displayed, but neither is the insert
> statement putting any record in the LinkTo_IBHiS_System.AlertMsg
> table.
>
> Any suggestions?
>
> IF _new.DischType is NOT Null THEN
>
> INSERT INTO LinkTo_IBHiS_System.AlertMsg (
> Due,
> ShortMsgText,
> MsgText,
> Scope,
> Category,
> Priority,
> MsgType,
> Active)
> SELECT
> Now(),
> 'Client Status Update',
> 'Discharge Notice: '+__new.Dischtype+' For '+__new.Caseno+'
> '+__New.Name,
> 'Domain Users',
> 'CeNotice',
> 'A1',
> 2020,
> TRUE
> from __New ;
>
> endif;
>


Jim Muir Posted on 2006-05-30 20:05:10.0Z
Date: Tue, 30 May 2006 16:05:10 -0400
From: Jim Muir <jpmuirNoSpamPlease@adelphia.net>
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: Advantage.Trigger
Subject: Re: After Update Trigger using SQL Script
References: <44771607@solutions.advantagedatabase.com> <886edc151372b8c85200d411efdc@devzone.advantagedatabase.com>
In-Reply-To: <886edc151372b8c85200d411efdc@devzone.advantagedatabase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 65.23.16.202
Message-ID: <447ca4e9@solutions.advantagedatabase.com>
X-Trace: 30 May 2006 14:02:49 -0700, 65.23.16.202
Lines: 29
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!65.23.16.202
Xref: solutions.advantagedatabase.com Advantage.Trigger:240
Article PK: 1136305


Jeremy Mullin wrote:
> Hi Jim,
>
> Is that your full script? You need to declare a cursor to read the
> __new values. Something like this:
>
> declare newvalues cursor as select * from __new;
> open newvalues;
> try
> fetch newvalues;
> if ( newvalues.empid = 2 ) then
> update demo10 set lastname = 'test' where empid = newvalues.empid;
> end;
> finally
> close newvalues;
> end;
>
>
> J.D. Mullin
> Advantage R&D
>

Well, after export copying the table to a new table, then renaming both
tables and some updates on the properties of the table, the darn script
[as I wrote it] works without declaring a cursor. (perhaps because its
a trigger that already knows __New? I did test it and it now works.

But I will take your recommendation into account before I do anything
else. And I will test it with your recommendation tomorrow.


Jim Muir Posted on 2006-06-01 19:30:11.0Z
Date: Thu, 01 Jun 2006 15:30:11 -0400
From: Jim Muir <jpmuirNoSpamPlease@adelphia.net>
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: Advantage.Trigger
Subject: Re: After Update Trigger using SQL Script
References: <44771607@solutions.advantagedatabase.com> <886edc151372b8c85200d411efdc@devzone.advantagedatabase.com>
In-Reply-To: <886edc151372b8c85200d411efdc@devzone.advantagedatabase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 65.23.16.202
Message-ID: <447f3f6e@solutions.advantagedatabase.com>
X-Trace: 1 Jun 2006 13:26:38 -0700, 65.23.16.202
Lines: 77
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!65.23.16.202
Xref: solutions.advantagedatabase.com Advantage.Trigger:244
Article PK: 1136309


Jeremy Mullin wrote:
> Hi Jim,
>
> Is that your full script? You need to declare a cursor to read the
> __new values.

Thanks Jeremy, I'm starting to get it.

I re-did everything and used your recommendations.
I promise I will eventually learn the scripting business that isn't
just SQL ... I think more examples (many more) would be helpful for
users to post in this forum.

Here's my [working] update for others who may need to traverse the
learning curve of script container triggers... see my first attempt.
then see what really works...

Jim

CREATE TRIGGER AlertMsg_Notice_Of_Discharge
ON Roster
AFTER
UPDATE

BEGIN

declare @dischtype string, @Name String, @Caseno String;
declare dischargeNew CURSOR;
declare dischtype CURSOR;

@dischtype = '';

OPEN DischargeNew AS SELECT * from __New;
OPEN DischType AS Select IFNULL(__New.dischtype,'') dsch FROM __New;


TRY
FETCH DischType;
FETCH DischargeNew;
@dischtype = DischType.dsch;

if @dischtype > '' then
@caseno = DischargeNew.caseno;
@Name = DischargeNew.Name ;

INSERT INTO LinkTo_IBHiS_System.AlertMsg (
Due,
ShortMsgText,
MsgText,
Scope,
Category,
Priority,
MsgType,
Active)

Values (
Now(),
'Client Status Update',
'Discharge Notice: '+ @dischtype +' For '+ @Caseno+' '+@Name,
'Domain Users',
'CeNotice',
'A1',
2020,
TRUE);

else return;
endif;

FINALLY
// Ensure the cursor is closed
CLOSE DischargeNew;
CLOSE DischType;

END TRY;

END
PRIORITY 1;