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.

Raiseerror behavior in Stored Proc called from trigger

7 posts in General Discussion Last posting was on 2004-11-22 20:36:49.0Z
Bill Aumen Posted on 2004-11-19 18:19:15.0Z
Sender: 56e4.419e34eb.1804289383@sybase.com
From: Bill Aumen
Newsgroups: ianywhere.public.general
Subject: Raiseerror behavior in Stored Proc called from trigger
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <419e3923.5739.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 19 Nov 2004 10:19:15 -0800
X-Trace: forums-1-dub 1100888355 10.22.241.41 (19 Nov 2004 10:19:15 -0800)
X-Original-Trace: 19 Nov 2004 10:19:15 -0800, 10.22.241.41
Lines: 30
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:3888
Article PK: 7787

I have a after update trigger:
if old.sdrtns_code <> 'COMPLETED' AND "new".sdrtns_code
= 'COMPLETED' THEN
CALL create_hcvist_encounter(old.hcvist_id);
end if

In the SP, I have:
if lui_prgram_id IS NULL OR lui_enpurp_id IS NULL OR
lui_entype_id IS NULL OR ls_synopsis IS NULL then
raiserror 18501 '18501 - PROCEDURE
create_hcvist_encounter, enform_encounter_format defaults
for ' || ls_enform_id || ' have not been set';
RETURN;
end if;


If I call the SP directly from ISQL:
create_hcvist_encounter (20000311), I receive the raiserror
message as I expect to.

If the SP is called by the trigger (ISQL statement: update
hcvist_homecare_visit set sdrtns_code = 'Completed' where
hcvist_id = 20000311 ), I receive the message:
Can not modify column 'hcvist_id' in table 'old'. SQLCODE
-191, STATE 42000.

I don't understand the behavior, and the message.

Thanks for some clarification.
Bill


Breck Carter [TeamSybase] Posted on 2004-11-20 13:01:01.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: Raiseerror behavior in Stored Proc called from trigger
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <hifup058lmmto9f3nvaksblf12ooj4oab2@4ax.com>
References: <419e3923.5739.1681692777@sybase.com>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: ip67-93-228-42.z228-93-67.customer.algx.net
X-Original-NNTP-Posting-Host: ip67-93-228-42.z228-93-67.customer.algx.net
Date: 20 Nov 2004 05:01:01 -0800
X-Trace: forums-1-dub 1100955661 67.93.228.42 (20 Nov 2004 05:01:01 -0800)
X-Original-Trace: 20 Nov 2004 05:01:01 -0800, ip67-93-228-42.z228-93-67.customer.algx.net
Lines: 52
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:3889
Article PK: 7782

That is a whole different error, probably unrelated to the
RAISERROR... it's saying you can't modify the REFERENCING OLD AS table
in any trigger (also, you cannot modify the REFERENCING NEW AS table
in an after trigger).

Show us the full code for the trigger and procedure. Is the parameter
to create_hcvist_encounter marked as OUT or INOUT?

I seem to remember there may be a more subtle reason for this message,
but I'm having a senior moment :)... show us the code, that'll help.

Breck

On 19 Nov 2004 10:19:15 -0800, Bill Aumen wrote:

>I have a after update trigger:
> if old.sdrtns_code <> 'COMPLETED' AND "new".sdrtns_code
>= 'COMPLETED' THEN
> CALL create_hcvist_encounter(old.hcvist_id);
> end if
>
>In the SP, I have:
> if lui_prgram_id IS NULL OR lui_enpurp_id IS NULL OR
>lui_entype_id IS NULL OR ls_synopsis IS NULL then
> raiserror 18501 '18501 - PROCEDURE
>create_hcvist_encounter, enform_encounter_format defaults
>for ' || ls_enform_id || ' have not been set';
> RETURN;
> end if;
>
>
>If I call the SP directly from ISQL:
>create_hcvist_encounter (20000311), I receive the raiserror
>message as I expect to.
>
>If the SP is called by the trigger (ISQL statement: update
>hcvist_homecare_visit set sdrtns_code = 'Completed' where
>hcvist_id = 20000311 ), I receive the message:
>Can not modify column 'hcvist_id' in table 'old'. SQLCODE
>-191, STATE 42000.
>
>I don't understand the behavior, and the message.
>
>Thanks for some clarification.
>Bill

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com


Bill Aumen Posted on 2004-11-20 17:16:47.0Z
Sender: 56e4.419e34eb.1804289383@sybase.com
From: Bill Aumen
Newsgroups: ianywhere.public.general
Subject: Re: Raiseerror behavior in Stored Proc called from trigger
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <419f7bff.66fd.1681692777@sybase.com>
References: <hifup058lmmto9f3nvaksblf12ooj4oab2@4ax.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 20 Nov 2004 09:16:47 -0800
X-Trace: forums-1-dub 1100971007 10.22.241.41 (20 Nov 2004 09:16:47 -0800)
X-Original-Trace: 20 Nov 2004 09:16:47 -0800, 10.22.241.41
Lines: 168
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:3890
Article PK: 7785

Thanks for your help Breck. Here is the SP:

CREATE PROCEDURE
app_owner.create_hcvist_encounter(aui_hcvist_id unsigned
integer)
begin
DECLARE ls_enform_id varchar(128);
DECLARE lui_encntr_id unsigned integer;
DECLARE lui_crregs_registry_id unsigned integer;
DECLARE lui_hcgivr_registry_id unsigned integer;
DECLARE ld_date date;
DECLARE lui_prgram_id unsigned integer;
DECLARE lui_enpurp_id unsigned integer;
DECLARE lui_entype_id unsigned integer;
DECLARE ls_synopsis varchar(128);
DECLARE ls_syusr_id varchar(128);

SET ls_enform_id = 'HCSCHEDULEDVISIT';

SELECT prgram_id,
enpurp_id,
entype_id,
enform_default_synopsis
INTO lui_prgram_id,
lui_enpurp_id,
lui_entype_id,
ls_synopsis
FROM app_owner.enform_encounter_format
WHERE enform_id = ls_enform_id;
if sqlcode <> 0 then
raiserror 18501 '18501 - PROCEDURE
create_hcvist_encounter, SELECT enform_encounter_format
failed, sqlcode=' || String(sqlcode);
RETURN;
end if;
if lui_prgram_id IS NULL OR lui_enpurp_id IS NULL OR
lui_entype_id IS NULL OR ls_synopsis IS NULL then
raiserror 18501 '18501 - PROCEDURE
create_hcvist_encounter, enform_encounter_format defaults
for ' || ls_enform_id || ' have not been set';
RETURN;
end if;

SELECT get_next_identity('encntr_encounter') INTO
lui_encntr_id
FROM DUMMY;
if sqlcode <> 0 then
raiserror 18500 '18500 - PROCEDURE
create_hcvist_encounter, SELECT get_next_identity failed,
sqlcode=' || String(sqlcode);
RETURN;
end if;

SELECT crregs_registry_id,
hcgivr_registry_id,
hcvist_date
INTO lui_crregs_registry_id,
lui_hcgivr_registry_id,
ld_date
FROM app_owner.hcvist_homecare_visit
WHERE hcvist_id = aui_hcvist_id;
if sqlcode <> 0 then
raiserror 18501 '18501 - PROCEDURE
create_hcvist_encounter, SELECT hcvist_homecare_visit
failed, sqlcode=' || String(sqlcode);
end if;


INSERT INTO app_owner.encntr_encounter
(encntr_id,
crregs_registry_id,
encntr_date,
prgram_id,
enpurp_id,
entype_id,
encntr_synopsis)
VALUES
(lui_encntr_id,
lui_crregs_registry_id,
ld_date,
lui_prgram_id,
lui_enpurp_id,
lui_entype_id,
ls_synopsis);
if sqlcode <> 0 then
raiserror 18501 '18501 - PROCEDURE
create_hcvist_encounter, INSERT INTO encntr_encounter
failed, sqlcode=' || String(sqlcode);
end if;

INSERT INTO app_owner.ennarr_encounter_narrative
(encntr_id,
enform_id)
VALUES
(lui_encntr_id,
ls_enform_id);
if sqlcode <> 0 then
raiserror 18501 '18501 - PROCEDURE
create_hcvist_encounter, INSERT INTO
ennarr_encounter_narrative failed, sqlcode=' ||
String(sqlcode);
end if;

SELECT syusr_id INTO ls_syusr_id
FROM app_owner.hcgivr_homecaregiver
WHERE crregs_registry_id = lui_hcgivr_registry_id;
if sqlcode <> 0 then
raiserror 18501 '18501 - PROCEDURE
create_hcvist_encounter, SELECT hcgivr_homecaregiver failed,
sqlcode=' || String(sqlcode);
end if;

IF ls_syusr_id IS NOT NULL THEN
INSERT INTO app_owner.encwkr_encounter_worker
(encntr_id,
syusr_id)
VALUES
(lui_encntr_id,
ls_syusr_id);
if sqlcode <> 0 then
raiserror 18501 '18501 - PROCEDURE
create_hcvist_encounter, INSERT INTO encwkr_encounter_worker
failed, sqlcode=' || String(sqlcode);
end if;
END IF;

end
go


Here is the Trigger:
CREATE TRIGGER app_owner.tau_hcvist_homecare_visit after
update of sdrtns_code order 2 on
app_owner.hcvist_homecare_visit
referencing old as old new as "new"
for each row
begin
if current remote user is null then
if old.sdrtns_code <> 'COMPLETED' AND "new".sdrtns_code
= 'COMPLETED' THEN
CALL create_hcvist_encounter(old.hcvist_id);
end if
end if
end
go


For my testing, I have insured that the first SELECT in the
SP returns NULL values to insure that check is working. That
is where I am at so far.

> That is a whole different error, probably unrelated to the
> RAISERROR... it's saying you can't modify the REFERENCING
> OLD AS table in any trigger (also, you cannot modify the
> REFERENCING NEW AS table in an after trigger).
>
> Show us the full code for the trigger and procedure. Is
> the parameter to create_hcvist_encounter marked as OUT or
> INOUT?
>
> I seem to remember there may be a more subtle reason for
> this message, but I'm having a senior moment :)... show us
> the code, that'll help.
>
> Breck
>


Greg Fenton Posted on 2004-11-20 19:23:25.0Z
From: Greg Fenton <greg.fenton_NOSPAM_@ianywhere.com>
Organization: iAnywhere Solutions Inc.
User-Agent: Mozilla Thunderbird 1.6.3.2f (Windows/20041103)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: Raiseerror behavior in Stored Proc called from trigger
References: <hifup058lmmto9f3nvaksblf12ooj4oab2@4ax.com> <419f7bff.66fd.1681692777@sybase.com>
In-Reply-To: <419f7bff.66fd.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vpn-concord-057.sybase.com
X-Original-NNTP-Posting-Host: vpn-concord-057.sybase.com
Message-ID: <419f99ad$1@forums-1-dub>
Date: 20 Nov 2004 11:23:25 -0800
X-Trace: forums-1-dub 1100978605 158.159.8.57 (20 Nov 2004 11:23:25 -0800)
X-Original-Trace: 20 Nov 2004 11:23:25 -0800, vpn-concord-057.sybase.com
Lines: 31
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:3891
Article PK: 7784


Bill Aumen wrote:
>
> CREATE PROCEDURE
> app_owner.create_hcvist_encounter(aui_hcvist_id unsigned
> integer)
>
> [...]
>
> Here is the Trigger:
> CREATE TRIGGER app_owner.tau_hcvist_homecare_visit after
> [...]
> if old.sdrtns_code <> 'COMPLETED' AND "new".sdrtns_code
> = 'COMPLETED' THEN
> CALL create_hcvist_encounter(old.hcvist_id);

Please always state the version and build number of ASA that you are using.

I am not 100% sure, but I believe the cause of your problem is that you
do not declare the parameter of your SP as being an "IN" parameter. By
default, the param will be an INOUT parameter and you are passing a
read-only value to it.

Give that a try and let us know how it goes,
greg.fenotn
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/


Breck Carter [TeamSybase] Posted on 2004-11-20 22:17:40.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: Raiseerror behavior in Stored Proc called from trigger
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <mggvp0lit4dvtj31njq09h6tvue9qgd7sq@4ax.com>
References: <hifup058lmmto9f3nvaksblf12ooj4oab2@4ax.com> <419f7bff.66fd.1681692777@sybase.com> <419f99ad$1@forums-1-dub>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: ip67-93-228-42.z228-93-67.customer.algx.net
X-Original-Trace: 20 Nov 2004 14:17:36 -0800, ip67-93-228-42.z228-93-67.customer.algx.net
Lines: 43
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 20 Nov 2004 14:17:37 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 20 Nov 2004 14:17:40 -0800
X-Trace: forums-1-dub 1100989060 10.22.108.75 (20 Nov 2004 14:17:40 -0800)
X-Original-Trace: 20 Nov 2004 14:17:40 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:3892
Article PK: 7786

I think you're right, Greg, and that reminds me what the "subtlety"
is: the default is different depending on syntax...

"Watcom-SQL procedure parameters are INOUT by default or can specified
as IN, OUT, or INOUT. Transact-SQL procedure parameters are INPUT
parameters by default or can be specified as OUTPUT."

Breck

On 20 Nov 2004 11:23:25 -0800, Greg Fenton

<greg.fenton_NOSPAM_@ianywhere.com> wrote:

>Bill Aumen wrote:
>>
>> CREATE PROCEDURE
>> app_owner.create_hcvist_encounter(aui_hcvist_id unsigned
>> integer)
>>
>> [...]
>>
>> Here is the Trigger:
>> CREATE TRIGGER app_owner.tau_hcvist_homecare_visit after
>> [...]
>> if old.sdrtns_code <> 'COMPLETED' AND "new".sdrtns_code
>> = 'COMPLETED' THEN
>> CALL create_hcvist_encounter(old.hcvist_id);
>
>Please always state the version and build number of ASA that you are using.
>
>I am not 100% sure, but I believe the cause of your problem is that you
>do not declare the parameter of your SP as being an "IN" parameter. By
>default, the param will be an INOUT parameter and you are passing a
>read-only value to it.
>
>Give that a try and let us know how it goes,
>greg.fenotn

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com


Greg Fenton Posted on 2004-11-21 21:09:58.0Z
From: Greg Fenton <greg.fenton_NOSPAM_@ianywhere.com>
Organization: iAnywhere Solutions Inc.
User-Agent: Mozilla Thunderbird 1.6.3.2f (Windows/20041103)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: Raiseerror behavior in Stored Proc called from trigger
References: <hifup058lmmto9f3nvaksblf12ooj4oab2@4ax.com> <419f7bff.66fd.1681692777@sybase.com> <419f99ad$1@forums-1-dub> <mggvp0lit4dvtj31njq09h6tvue9qgd7sq@4ax.com>
In-Reply-To: <mggvp0lit4dvtj31njq09h6tvue9qgd7sq@4ax.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: cpec2cdc91b1d31-cm000f212f9e50.cpe.net.cable.rogers.com
X-Original-NNTP-Posting-Host: cpec2cdc91b1d31-cm000f212f9e50.cpe.net.cable.rogers.com
Message-ID: <41a10426$1@forums-1-dub>
Date: 21 Nov 2004 13:09:58 -0800
X-Trace: forums-1-dub 1101071398 69.198.107.137 (21 Nov 2004 13:09:58 -0800)
X-Original-Trace: 21 Nov 2004 13:09:58 -0800, cpec2cdc91b1d31-cm000f212f9e50.cpe.net.cable.rogers.com
Lines: 17
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:3895
Article PK: 7788


Breck Carter [TeamSybase] wrote:
> I think you're right, Greg, and that reminds me what the "subtlety"
> is: the default is different depending on syntax...
>

This is why I *never* rely on defaults (at least not in source code or
in production environment configurations)....a bit more typing today
leads to a lot less all-night debug sessions... ;-)

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/


Bill Aumen Posted on 2004-11-22 20:36:49.0Z
Sender: 56e4.419e34eb.1804289383@sybase.com
From: Bill Aumen
Newsgroups: ianywhere.public.general
Subject: Re: Raiseerror behavior in Stored Proc called from trigger
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <41a24de1.2559.1681692777@sybase.com>
References: <41a10426$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 22 Nov 2004 12:36:49 -0800
X-Trace: forums-1-dub 1101155809 10.22.241.41 (22 Nov 2004 12:36:49 -0800)
X-Original-Trace: 22 Nov 2004 12:36:49 -0800, 10.22.241.41
Lines: 27
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:3899
Article PK: 7792

Gentlemen, Thank you. Adding "IN" did indeed solve my
problem.

Guess I'll have to start paying more attention to those
behavior change notices :-).

Bill

> Breck Carter [TeamSybase] wrote:
> > I think you're right, Greg, and that reminds me what the
> > "subtlety" is: the default is different depending on
> > syntax...
>
> This is why I *never* rely on defaults (at least not in
> source code or in production environment
> configurations)....a bit more typing today leads to a lot
> less all-night debug sessions... ;-)
>
> greg.fenton
> --
> Greg Fenton
> Consultant, Solution Services, iAnywhere Solutions
> --------
> Visit the iAnywhere Solutions Developer Community
> Whitepapers, TechDocs, Downloads
> http://www.ianywhere.com/developer/