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.

"Auto complete" trigger question..

4 posts in General Discussion Last posting was on 2010-11-26 19:19:34.0Z
Manuel Espinoza <jmespinoza_no_spam Posted on 2010-11-22 19:31:21.0Z
From: Manuel Espinoza <jmespinoza_no_spam@seicom_dot_com_dot_mx>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; es-ES; rv:1.9.2.12) Gecko/20101027 Thunderbird/3.1.6
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: "Auto complete" trigger question..
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: <4ceac509@forums-1-dub>
Date: 22 Nov 2010 11:31:21 -0800
X-Trace: forums-1-dub 1290454281 10.22.241.152 (22 Nov 2010 11:31:21 -0800)
X-Original-Trace: 22 Nov 2010 11:31:21 -0800, vip152.sybase.com
Lines: 34
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29720
Article PK: 78952

Hi

I have a new task, which is to auto-complete some incomplete data in
certain tables, using a trigger( for insert or update )

Lets say I have two tables, A and B

create table A
( id numeric(10) not null,
char_value char(15) null
Primary Key (id)
)

create table B
(
id_B numeric(10) not null,
char_default char(15)
)


So, every new row in A that didn't have the char_value filled must be
filled within the trigger, using char_default from B.(The same rule
apply if A is updated)

Is this possible?

I knew that I could abort the transaction by issuing a rollback, but to
complete columns??( I've tried the insert trigger, updating the Inserted
table, but neither Inserted nor Deleted tables may be updated )



Thanks in advance.


Michael Peppler [Team Sybase] Posted on 2010-11-22 19:48:08.0Z
From: "Michael Peppler [Team Sybase]" <mpeppler@peppler.org>
Organization: Peppler Consulting SARL
Subject: Re: "Auto complete" trigger question..
User-Agent: Pan/0.14.2 (This is not a psychotic episode. It's a cleansing moment of clarity.)
Message-ID: <pan.2010.11.22.19.48.07.695375@peppler.org>
Newsgroups: sybase.public.ase.general
References: <4ceac509@forums-1-dub>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 22 Nov 2010 11:48:08 -0800
X-Trace: forums-1-dub 1290455288 10.22.241.152 (22 Nov 2010 11:48:08 -0800)
X-Original-Trace: 22 Nov 2010 11:48:08 -0800, vip152.sybase.com
Lines: 28
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29721
Article PK: 78950


On Mon, 22 Nov 2010 11:31:21 -0800, Manuel Espinoza wrote:

> So, every new row in A that didn't have the char_value filled must be
> filled within the trigger, using char_default from B.(The same rule
> apply if A is updated)
>
> Is this possible?
>
> I knew that I could abort the transaction by issuing a rollback, but to
> complete columns??( I've tried the insert trigger, updating the Inserted
> table, but neither Inserted nor Deleted tables may be updated )

In fact you need to update the A or B tables in the trigger, and join
with the inserted table to determine which row in the A or B tables to
update.

So assuming a trigger on table A

update A
set char_value = ....
from inserted i
join A
on A.id = i.id

would update the appropriate row(s) as needed.

Michael


Bret Halford Posted on 2010-11-22 22:12:34.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.12) Gecko/20101027 Thunderbird/3.1.6
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: "Auto complete" trigger question..
References: <4ceac509@forums-1-dub> <pan.2010.11.22.19.48.07.695375@peppler.org>
In-Reply-To: <pan.2010.11.22.19.48.07.695375@peppler.org>
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: <4ceaead2$1@forums-1-dub>
Date: 22 Nov 2010 14:12:34 -0800
X-Trace: forums-1-dub 1290463954 10.22.241.152 (22 Nov 2010 14:12:34 -0800)
X-Original-Trace: 22 Nov 2010 14:12:34 -0800, vip152.sybase.com
Lines: 34
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29722
Article PK: 78951


On 11/22/2010 12:48 PM, Michael Peppler [Team Sybase] wrote:
> On Mon, 22 Nov 2010 11:31:21 -0800, Manuel Espinoza wrote:
>
>> So, every new row in A that didn't have the char_value filled must be
>> filled within the trigger, using char_default from B.(The same rule
>> apply if A is updated)
>>
>> Is this possible?
>>
>> I knew that I could abort the transaction by issuing a rollback, but to
>> complete columns??( I've tried the insert trigger, updating the Inserted
>> table, but neither Inserted nor Deleted tables may be updated )
>
> In fact you need to update the A or B tables in the trigger, and join
> with the inserted table to determine which row in the A or B tables to
> update.
>
> So assuming a trigger on table A
>
> update A
> set char_value = ....
> from inserted i
> join A
> on A.id = i.id
>
> would update the appropriate row(s) as needed.
>
> Michael
>

Minor point, but I think the update would also want
a "and A.id is null" clause so as to not update rows
where the user did supply a value.

-bret


Manuel Espinoza <jmespinoza_no_spam Posted on 2010-11-26 19:19:34.0Z
From: Manuel Espinoza <jmespinoza_no_spam@seicom_dot_com_dot_mx>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; es-ES; rv:1.9.2.12) Gecko/20101027 Thunderbird/3.1.6
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: "Auto complete" trigger question..
References: <4ceac509@forums-1-dub> <pan.2010.11.22.19.48.07.695375@peppler.org> <4ceaead2$1@forums-1-dub>
In-Reply-To: <4ceaead2$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4cf00846$1@forums-1-dub>
Date: 26 Nov 2010 11:19:34 -0800
X-Trace: forums-1-dub 1290799174 10.22.241.152 (26 Nov 2010 11:19:34 -0800)
X-Original-Trace: 26 Nov 2010 11:19:34 -0800, vip152.sybase.com
Lines: 45
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29732
Article PK: 78959

Yup. Works like a charm.

Thanks you guys for your help!


Have a nice weekend!



El 22/11/2010 04:12 p.m., Bret Halford escribió:

> On 11/22/2010 12:48 PM, Michael Peppler [Team Sybase] wrote:
>> On Mon, 22 Nov 2010 11:31:21 -0800, Manuel Espinoza wrote:
>>
>>> So, every new row in A that didn't have the char_value filled must be
>>> filled within the trigger, using char_default from B.(The same rule
>>> apply if A is updated)
>>>
>>> Is this possible?
>>>
>>> I knew that I could abort the transaction by issuing a rollback, but to
>>> complete columns??( I've tried the insert trigger, updating the
>>> Inserted
>>> table, but neither Inserted nor Deleted tables may be updated )
>>
>> In fact you need to update the A or B tables in the trigger, and join
>> with the inserted table to determine which row in the A or B tables to
>> update.
>>
>> So assuming a trigger on table A
>>
>> update A
>> set char_value = ....
>> from inserted i
>> join A
>> on A.id = i.id
>>
>> would update the appropriate row(s) as needed.
>>
>> Michael
>>
> Minor point, but I think the update would also want
> a "and A.id is null" clause so as to not update rows
> where the user did supply a value.
>
> -bret