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.

How to suppress/ substitute error message

8 posts in General Discussion Last posting was on 2010-09-01 18:49:07.0Z
LAV Posted on 2010-09-01 14:32:20.0Z
Sender: 1553.4c7e5ee9.1804289383@sybase.com
From: LAV
Newsgroups: sybase.public.ase.general
Subject: How to suppress/ substitute error message
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4c7e63f4.15e5.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 1 Sep 2010 07:32:20 -0700
X-Trace: forums-1-dub 1283351540 10.22.241.41 (1 Sep 2010 07:32:20 -0700)
X-Original-Trace: 1 Sep 2010 07:32:20 -0700, 10.22.241.41
Lines: 43
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29523
Article PK: 78755

Hi,

I would like to raise my error message when DELETE failed in
the trigger.
More details:

In the trigger for DELETE in table A I delete rows from
table B (some kind of cascade deletion), but table B could
have foreign keys referenced to it from other tables. If
there are such rows the DELETE statement generates error
message :
"Dependent foreign key constraint violation in a referential
integrity constraint. dbname = 'show', table name =
'org_op', constraint name = 'fj_org_id_org_id_fk'."

I would like to change this message to more user friendly
and raise error with following text "Cannot delete Role Type
when related object exist!"

I have tried following:

Trigger body:
...

DELETE org_op
FROM deleted
WHERE deleted.party_id = org_op.organization_id and
deleted.role_type_c = org_op.role_type_c


if @@error!=0
rollback trigger with raiserror 20002 'Cannot delete
Role Type when related object exist!'

...

But this did not work. The end user getting "Dependent
foreign key constraint violation in a referential integrity
constraint..." message

How to solve this?

Thank you


Bret Halford Posted on 2010-09-01 15:13:02.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.8) Gecko/20100802 Thunderbird/3.1.2
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: How to suppress/ substitute error message
References: <4c7e63f4.15e5.1681692777@sybase.com>
In-Reply-To: <4c7e63f4.15e5.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c7e6d7e$1@forums-1-dub>
Date: 1 Sep 2010 08:13:02 -0700
X-Trace: forums-1-dub 1283353982 10.22.241.152 (1 Sep 2010 08:13:02 -0700)
X-Original-Trace: 1 Sep 2010 08:13:02 -0700, vip152.sybase.com
Lines: 55
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29524
Article PK: 78754

The only way I see to do that is to write code in
the trigger before the delete to check for
such foreign key violations, if they exist raise
your error and don't call the delete. Only call
the delete if there are no violations.

-bret

On 9/1/2010 8:32 AM, LAV wrote:
> Hi,
>
> I would like to raise my error message when DELETE failed in
> the trigger.
> More details:
>
> In the trigger for DELETE in table A I delete rows from
> table B (some kind of cascade deletion), but table B could
> have foreign keys referenced to it from other tables. If
> there are such rows the DELETE statement generates error
> message :
> "Dependent foreign key constraint violation in a referential
> integrity constraint. dbname = 'show', table name =
> 'org_op', constraint name = 'fj_org_id_org_id_fk'."
>
> I would like to change this message to more user friendly
> and raise error with following text "Cannot delete Role Type
> when related object exist!"
>
> I have tried following:
>
> Trigger body:
> ...
>
> DELETE org_op
> FROM deleted
> WHERE deleted.party_id = org_op.organization_id and
> deleted.role_type_c = org_op.role_type_c
>
>
> if @@error!=0
> rollback trigger with raiserror 20002 'Cannot delete
> Role Type when related object exist!'
>
> ...
>
> But this did not work. The end user getting "Dependent
> foreign key constraint violation in a referential integrity
> constraint..." message
>
> How to solve this?
>
> Thank you


LAV Posted on 2010-09-01 16:35:47.0Z
Sender: 1845.4c7e7df8.1804289383@sybase.com
From: LAV
Newsgroups: sybase.public.ase.general
Subject: Re: How to suppress/ substitute error message
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4c7e80e3.187b.1681692777@sybase.com>
References: <4c7e6d7e$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 1 Sep 2010 09:35:47 -0700
X-Trace: forums-1-dub 1283358947 10.22.241.41 (1 Sep 2010 09:35:47 -0700)
X-Original-Trace: 1 Sep 2010 09:35:47 -0700, 10.22.241.41
Lines: 62
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29525
Article PK: 78760

I have tried to do, but I could not check all tables which
this table (Table B) is referred from. Of course I could
hard-code all known tables at the moment, but I do not like
the idea to modify the trigger each time when a new foreign
key added.

> The only way I see to do that is to write code in
> the trigger before the delete to check for
> such foreign key violations, if they exist raise
> your error and don't call the delete. Only call
> the delete if there are no violations.
>
> -bret
>
>
>
> On 9/1/2010 8:32 AM, LAV wrote:
> > Hi,
> >
> > I would like to raise my error message when DELETE
> > failed in the trigger.
> > More details:
> >
> > In the trigger for DELETE in table A I delete rows from
> > table B (some kind of cascade deletion), but table B
> > could have foreign keys referenced to it from other
> > tables. If there are such rows the DELETE statement
> > generates error message :
> > "Dependent foreign key constraint violation in a
> > referential integrity constraint. dbname = 'show',
> > table name = 'org_op', constraint name =
> 'fj_org_id_org_id_fk'." >
> > I would like to change this message to more user
> > friendly and raise error with following text "Cannot
> > delete Role Type when related object exist!"
> >
> > I have tried following:
> >
> > Trigger body:
> > ...
> >
> > DELETE org_op
> > FROM deleted
> > WHERE deleted.party_id = org_op.organization_id and
> > deleted.role_type_c = org_op.role_type_c
> >
> >
> > if @@error!=0
> > rollback trigger with raiserror 20002 'Cannot
> > delete Role Type when related object exist!'
> >
> > ...
> >
> > But this did not work. The end user getting "Dependent
> > foreign key constraint violation in a referential
> > integrity constraint..." message
> >
> > How to solve this?
> >
> > Thank you
>


Jason L. Froebe [TeamSybase] Posted on 2010-09-01 17:30:03.0Z
From: "Jason L. Froebe [TeamSybase]" <jason.froebe@gmail.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.1.11) Gecko/20100713 Thunderbird/3.0.6
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: How to suppress/ substitute error message
References: <4c7e6d7e$1@forums-1-dub> <4c7e80e3.187b.1681692777@sybase.com>
In-Reply-To: <4c7e80e3.187b.1681692777@sybase.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c7e8d9b@forums-1-dub>
Date: 1 Sep 2010 10:30:03 -0700
X-Trace: forums-1-dub 1283362203 10.22.241.152 (1 Sep 2010 10:30:03 -0700)
X-Original-Trace: 1 Sep 2010 10:30:03 -0700, vip152.sybase.com
Lines: 13
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29529
Article PK: 78757


On 09/01/2010 11:35 AM, LAV wrote:
> I have tried to do, but I could not check all tables which
> this table (Table B) is referred from. Of course I could
> hard-code all known tables at the moment, but I do not like
> the idea to modify the trigger each time when a new foreign
> key added.

Typically such a requirement would need to be implemented in the client
application layer not the db backend. Is there a particular reason why
this can't be done in the application?

jason


"Mark A. Parsons" <iron_horse Posted on 2010-09-01 17:41:55.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: How to suppress/ substitute error message
References: <4c7e6d7e$1@forums-1-dub> <4c7e80e3.187b.1681692777@sybase.com> <4c7e8d9b@forums-1-dub>
In-Reply-To: <4c7e8d9b@forums-1-dub>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c7e9063@forums-1-dub>
Date: 1 Sep 2010 10:41:55 -0700
X-Trace: forums-1-dub 1283362915 10.22.241.152 (1 Sep 2010 10:41:55 -0700)
X-Original-Trace: 1 Sep 2010 10:41:55 -0700, vip152.sybase.com
Lines: 19
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29530
Article PK: 78761

And if these checks are going to be placed in the application, then why not skip the RI checks and just mask/re-map the
ASE-generated error message into a more 'friendly' error message for end user consumption ... though I'd recommend the
application log the complete ASE-generated error message so that a T-SQL/dataserver troubleshooter has access to the
original message.

Jason L. Froebe [TeamSybase] wrote:
> On 09/01/2010 11:35 AM, LAV wrote:
>> I have tried to do, but I could not check all tables which
>> this table (Table B) is referred from. Of course I could
>> hard-code all known tables at the moment, but I do not like
>> the idea to modify the trigger each time when a new foreign
>> key added.
>
> Typically such a requirement would need to be implemented in the client
> application layer not the db backend. Is there a particular reason why
> this can't be done in the application?
>
> jason
>


LAV Posted on 2010-09-01 18:49:07.0Z
Sender: 1845.4c7e7df8.1804289383@sybase.com
From: LAV
Newsgroups: sybase.public.ase.general
Subject: Re: How to suppress/ substitute error message
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4c7ea023.1b13.1681692777@sybase.com>
References: <4c7e9063@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 1 Sep 2010 11:49:07 -0700
X-Trace: forums-1-dub 1283366947 10.22.241.41 (1 Sep 2010 11:49:07 -0700)
X-Original-Trace: 1 Sep 2010 11:49:07 -0700, 10.22.241.41
Lines: 27
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29531
Article PK: 78763

Yeah... It seems to me you are right, I will move the error
processing on application level.

Thank you.

> And if these checks are going to be placed in the
> application, then why not skip the RI checks and just
> mask/re-map the ASE-generated error message into a more
> 'friendly' error message for end user consumption ...
> though I'd recommend the application log the complete
> ASE-generated error message so that a T-SQL/dataserver
> troubleshooter has access to the original message.
>
> Jason L. Froebe [TeamSybase] wrote:
> > On 09/01/2010 11:35 AM, LAV wrote:
> >> I have tried to do, but I could not check all tables
> which >> this table (Table B) is referred from. Of course
> I could >> hard-code all known tables at the moment, but I
> do not like >> the idea to modify the trigger each time
> when a new foreign >> key added.
> >
> > Typically such a requirement would need to be
> > implemented in the client application layer not the db
> > backend. Is there a particular reason why this can't
> > be done in the application?
> > jason
> >


"Mark A. Parsons" <iron_horse Posted on 2010-09-01 17:09:48.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: How to suppress/ substitute error message
References: <4c7e63f4.15e5.1681692777@sybase.com>
In-Reply-To: <4c7e63f4.15e5.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c7e88dc$1@forums-1-dub>
Date: 1 Sep 2010 10:09:48 -0700
X-Trace: forums-1-dub 1283360988 10.22.241.152 (1 Sep 2010 10:09:48 -0700)
X-Original-Trace: 1 Sep 2010 10:09:48 -0700, vip152.sybase.com
Lines: 99
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29527
Article PK: 78758

While I'm (roughly) 100% sure Sybase would not support this, the following steps could be taken to customize the error
message generated by ASE:

==============================
use master
go

select * from t1
go

Msg 208, Level 16, State 1:
Server 'CC1', Line 1:
t1 not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of
output).

sp_configure 'allow updates',1
go

begin tran
update sysmessages set description =
"I am so very sorry, but I am unable to locate an object by the name of '%.*s'."
where error = 208
go

-- if no error and only one row affected:
commit tran
-- else rollback tran and try update again
go

select * from t1
go

Msg 208, Level 16, State 1:
Server 'CC1', Line 1:
I am so very sorry, but I am unable to locate an object by the name of 't1'.
==============================

Obviously (?) ...

- you'll have a hard time opening a case with Sybase tech support if you cannot provide the error messages that they are
expecting

- it's up to you to understand how place holders are utilized in the description column (eg, what happens if/when the
dataserver tries to feed values into the description string just to find you've entered an invalid format and/or you've
left out the necessary place holder(s))

- you'll need to reload your custom messages after running various Sybase-supplied installation scripts

- monitoring scripts that rely on specific error message strings will need to be modified

- other folks who are used to seeing Sybase-supplied error messages may be less then enthused to find themselves dealing
with new message strings, especially if you don't do a good job of providing useful messages (not that Sybase can be
used as a role model when it comes to useful/meaningful error messages)

LAV wrote:
> Hi,
>
> I would like to raise my error message when DELETE failed in
> the trigger.
> More details:
>
> In the trigger for DELETE in table A I delete rows from
> table B (some kind of cascade deletion), but table B could
> have foreign keys referenced to it from other tables. If
> there are such rows the DELETE statement generates error
> message :
> "Dependent foreign key constraint violation in a referential
> integrity constraint. dbname = 'show', table name =
> 'org_op', constraint name = 'fj_org_id_org_id_fk'."
>
> I would like to change this message to more user friendly
> and raise error with following text "Cannot delete Role Type
> when related object exist!"
>
> I have tried following:
>
> Trigger body:
> ...
>
> DELETE org_op
> FROM deleted
> WHERE deleted.party_id = org_op.organization_id and
> deleted.role_type_c = org_op.role_type_c
>
>
> if @@error!=0
> rollback trigger with raiserror 20002 'Cannot delete
> Role Type when related object exist!'
>
> ...
>
> But this did not work. The end user getting "Dependent
> foreign key constraint violation in a referential integrity
> constraint..." message
>
> How to solve this?
>
> Thank you


LAV Posted on 2010-09-01 17:16:30.0Z
Sender: 1845.4c7e7df8.1804289383@sybase.com
From: LAV
Newsgroups: sybase.public.ase.general
Subject: Re: How to suppress/ substitute error message
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4c7e8a6e.194b.1681692777@sybase.com>
References: <4c7e88dc$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 1 Sep 2010 10:16:30 -0700
X-Trace: forums-1-dub 1283361390 10.22.241.41 (1 Sep 2010 10:16:30 -0700)
X-Original-Trace: 1 Sep 2010 10:16:30 -0700, 10.22.241.41
Lines: 114
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29528
Article PK: 78759

It would not work because it will change the text for all
such kind of errors. But I need only for one specific
situation.

> While I'm (roughly) 100% sure Sybase would not support
> this, the following steps could be taken to customize the
> error message generated by ASE:
>
> ==============================
> use master
> go
>
> select * from t1
> go
>
> Msg 208, Level 16, State 1:
> Server 'CC1', Line 1:
> t1 not found. Specify owner.objectname or use sp_help to
> check whether the object exists (sp_help may produce lots
> of output).
>
> sp_configure 'allow updates',1
> go
>
> begin tran
> update sysmessages set description =
> "I am so very sorry, but I am unable to locate an object
> by the name of '%.*s'." where error = 208
> go
>
> -- if no error and only one row affected:
> commit tran
> -- else rollback tran and try update again
> go
>
> select * from t1
> go
>
> Msg 208, Level 16, State 1:
> Server 'CC1', Line 1:
> I am so very sorry, but I am unable to locate an object by
> the name of 't1'. ==============================
>
> Obviously (?) ...
>
> - you'll have a hard time opening a case with Sybase tech
> support if you cannot provide the error messages that they
> are expecting
>
> - it's up to you to understand how place holders are
> utilized in the description column (eg, what happens
> if/when the dataserver tries to feed values into the
> description string just to find you've entered an invalid
> format and/or you've left out the necessary place
> holder(s))
>
> - you'll need to reload your custom messages after running
> various Sybase-supplied installation scripts
>
> - monitoring scripts that rely on specific error message
> strings will need to be modified
>
> - other folks who are used to seeing Sybase-supplied error
> messages may be less then enthused to find themselves
> dealing with new message strings, especially if you don't
> do a good job of providing useful messages (not that
> Sybase can be used as a role model when it comes to
> useful/meaningful error messages)
>
>
> LAV wrote:
> > Hi,
> >
> > I would like to raise my error message when DELETE
> > failed in the trigger.
> > More details:
> >
> > In the trigger for DELETE in table A I delete rows from
> > table B (some kind of cascade deletion), but table B
> > could have foreign keys referenced to it from other
> > tables. If there are such rows the DELETE statement
> > generates error message :
> > "Dependent foreign key constraint violation in a
> > referential integrity constraint. dbname = 'show',
> > table name = 'org_op', constraint name =
> > 'fj_org_id_org_id_fk'."
> > I would like to change this message to more user
> > friendly and raise error with following text "Cannot
> > delete Role Type when related object exist!"
> >
> > I have tried following:
> >
> > Trigger body:
> > ...
> >
> > DELETE org_op
> > FROM deleted
> > WHERE deleted.party_id = org_op.organization_id and
> > deleted.role_type_c = org_op.role_type_c
> >
> >
> > if @@error!=0
> > rollback trigger with raiserror 20002 'Cannot
> > delete Role Type when related object exist!'
> >
> > ...
> >
> > But this did not work. The end user getting "Dependent
> > foreign key constraint violation in a referential
> > integrity constraint..." message
> >
> > How to solve this?
> >
> > Thank you