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.

possible enhancement: new system function => trigger_for()

4 posts in Product Futures Discussion Last posting was on 2002-03-24 03:50:56.0Z
Pablo Sanchez Posted on 2002-03-22 18:05:41.0Z
From: "Pablo Sanchez" <pablo@dev.null>
Subject: possible enhancement: new system function => trigger_for()
Date: Fri, 22 Mar 2002 11:05:41 -0700
Lines: 44
Organization: High-Performance Database Engineering
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <aa$s50c0BHA.274@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 207.225.105.222
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:639
Article PK: 94175

Howdy,

(As folks can guess, I'm in the midst of writing some triggers ... :)

I am using "raiserror" and sp_addmessage() to manage the error
messages from within my triggers. Very tidy thank you Sybase
engineering.

I'm able to create a single trigger for inserts and updates. What I'd
like to do is tailor an error message without consuming resources
based on whether the DML that fired the trigger was an insert or an
update.

I was thinking that we might have a system function named
trigger_for() which returns: insert, update, delete strings.

What I could do now is the following:

If I had the following message:

"%1! %2! FAILED DUE TO THE PRICE OF GAS ON MARS"

and it'd be invoked in a raiserror as follows:

raiserror 20000, "my_table", upper(trigger_for())

If an error occurred during an 'insert', I'd get the following
message:

my_table INSERT FAILED DUE TO THE PRICE OF GAS ON MARS

I realize that I might be able to figure this out on my own within the
trigger by checking the count of insert/deleted ... figured ASE
already 'knows.'

Thoughts?

Thx!
--
Pablo Sanchez, High-Performance Database Engineering
www.hpdbe.com
Available for short-term and long-term contracts


Jim Egan Posted on 2002-03-22 19:04:11.0Z
From: Jim Egan <dontspam.dbaguru@eganomics.com>
Subject: Re: possible enhancement: new system function => trigger_for()
Date: Fri, 22 Mar 2002 12:04:11 -0700
Message-ID: <MPG.17052e1ae8e2907e98bc5d@forums.sybase.com>
References: <aa$s50c0BHA.274@forums.sybase.com>
Reply-To: eganjp@compuserve.com
X-Newsreader: MicroPlanet Gravity v2.50
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 38
NNTP-Posting-Host: 10.20.41.196
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:635
Article PK: 94162


pablo@dev.null wrote...
> Howdy,
>
> (As folks can guess, I'm in the midst of writing some triggers ... :)
>
> I am using "raiserror" and sp_addmessage() to manage the error
> messages from within my triggers. Very tidy thank you Sybase
> engineering.
>
> I'm able to create a single trigger for inserts and updates. What I'd
> like to do is tailor an error message without consuming resources
> based on whether the DML that fired the trigger was an insert or an
> update.
>
> I was thinking that we might have a system function named
> trigger_for() which returns: insert, update, delete strings.
>
> What I could do now is the following:
>
> If I had the following message:
>
> "%1! %2! FAILED DUE TO THE PRICE OF GAS ON MARS"
>
> and it'd be invoked in a raiserror as follows:
>
> raiserror 20000, "my_table", upper(trigger_for())
>
> If an error occurred during an 'insert', I'd get the following
> message:
>
> my_table INSERT FAILED DUE TO THE PRICE OF GAS ON MARS
>
> I realize that I might be able to figure this out on my own within the
> trigger by checking the count of insert/deleted ... figured ASE
> already 'knows.'
>

There was a similar feature recently added to ASA. It's a handy shortcut. But, if you
wanted to you could check the rowcount on the inserted and deleted tables to see if it was
an insert, update or delete.
--
Jim Egan [TeamSybase]
Senior Consultant
Sybase Professional Services


Bret Halford Posted on 2002-03-22 22:32:09.0Z
Message-ID: <3C9BB0E9.331E579F@sybase.com>
Date: Fri, 22 Mar 2002 15:32:09 -0700
From: Bret Halford <bret@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.76 [en] (Windows NT 5.0; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: possible enhancement: new system function => trigger_for()
References: <aa$s50c0BHA.274@forums.sybase.com> <MPG.17052e1ae8e2907e98bc5d@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 12
NNTP-Posting-Host: 157.133.80.180
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:630
Article PK: 94158


Jim Egan wrote:

>
> There was a similar feature recently added to ASA. It's a handy shortcut. But, if you
> wanted to you could check the rowcount on the inserted and deleted tables to see if it was
> an insert, update or delete.
> --

...which works most of the time, but if the statement didn't affect any rows,
then the rowcounts will be zero and one won't be able to tell if the trigger was
fired by an update, insert, or delete.

-bret


Jim Egan Posted on 2002-03-24 03:50:56.0Z
From: Jim Egan <dontspam.dbaguru@eganomics.com>
Subject: Re: possible enhancement: new system function => trigger_for()
Date: Sat, 23 Mar 2002 20:50:56 -0700
Message-ID: <MPG.1706fb07406084dc98bc65@forums.sybase.com>
References: <aa$s50c0BHA.274@forums.sybase.com> <MPG.17052e1ae8e2907e98bc5d@forums.sybase.com> <3C9BB0E9.331E579F@sybase.com>
Reply-To: eganjp@compuserve.com
X-Newsreader: MicroPlanet Gravity v2.50
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 13
NNTP-Posting-Host: 12-252-108-115.client.attbi.com 12.252.108.115
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:628
Article PK: 94156


bret@sybase.com wrote...
> ...which works most of the time, but if the statement didn't affect any rows,
> then the rowcounts will be zero and one won't be able to tell if the trigger was
> fired by an update, insert, or delete.
>

Yes, I agree. But if no rows were affected at all then you likely wouldn't continue
execution of the trigger either. At least, I never have. I can't think of why I would let
it.
--
Jim Egan [TeamSybase]
Senior Consultant
Sybase Professional Services