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.

UPDATE TRIGGER Problem

4 posts in Windows NT Last posting was on 2000-01-25 06:03:04.0Z
TePe Posted on 2000-01-21 17:08:03.0Z
From: "TePe" <tomekp@profis.com.pl>
Subject: UPDATE TRIGGER Problem
Date: Fri, 21 Jan 2000 18:08:03 +0100
Lines: 42
X-Newsreader: Microsoft Outlook Express 4.72.3612.1700
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3612.1700
Message-ID: <jw2PhKDZ$GA.300@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: pc58.wroclaw.ppp.tpnet.pl 212.160.40.58
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2656
Article PK: 1090102

Hi !

I have encountered such a problem :

I have added a column to a table :
ALTER TABLE asset_relation ADD COLUMN s_list VARCHAR(255) NULL

Next, I have written an update triggger :
CREATE TRIGGER tu_pAssetRelation ON asset_relation FOR UPDATE
AS
IF UPDATE( iid_asset )
BEGIN
DECLARE cur_inserted CURSOR FOR
SELECT DISTINCT iid_asset, iidp_asset FROM inserted
DECLARE @iid_Asset INT
DECLARE @iidp_Asset INT
OPEN cur_inserted
WHILE 1=1
BEGIN
FETCH cur_inserted INTO @iid_Asset, @iidp_Asset
IF @@sqlstatus != 0 BREAK
UPDATE asset_relation SET s_list = '1'
WHERE iid_asset = @iid_Asset AND iidp_asset = @iidp_Asset
END
END
END

If column iid_asset is updated, the trigger works fine (column s_list is
updated with value '1'). But, if another
column is updated (not iid_asset and not s_list -> so trigger shouldn't
fire), column s_list is updated with NULL. Why ?
After dropping table and recreating it with s_list column, everything works
fine. But I dont't want to do this.
I tried some sytem procedures like sp_recompile, but it won't help me. Is
there any other solution ?

Sorry, for my English
Thanks


Mark A. Parsons Posted on 2000-01-22 04:53:32.0Z
Message-ID: <388937CC.4739E878@compuserve.com>
Date: Sat, 22 Jan 2000 17:53:32 +1300
From: "Mark A. Parsons" <iron_horse@compuserve.com>
Organization: Pegasys Ltd
X-Mailer: Mozilla 4.61 [en] (Win98; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: UPDATE TRIGGER Problem
References: <jw2PhKDZ$GA.300@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 32
NNTP-Posting-Host: p443.ipa1-n8-16.iconz.net.nz 210.48.25.187
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2655
Article PK: 1090101


> If column iid_asset is updated, the trigger works fine (column s_list is
> updated with value '1'). But, if another
> column is updated (not iid_asset and not s_list -> so trigger shouldn't
> fire), column s_list is updated with NULL. Why ?
> After dropping table and recreating it with s_list column, everything works
> fine. But I dont't want to do this.
> I tried some sytem procedures like sp_recompile, but it won't help me. Is
> there any other solution ?

A couple ideas to start with ...

1) Make sure you create the trigger *after* you've modified the table.
If the trigger exists before you make the table change ... just
re-create the trigger after the table has been modified. This way the
trigger should be re-compiled with the table's new characteristics.

2) In the situation where you want to update/modify a column other than
iid_asset, make sure ... before hand ... that s_list is not already
NULL. (Are you, by any chance, using 'begin/commit/rollback tran'
during any of this? I'm just wondering if you could be issuing a
rollback that's undoing your updates of s_list?) Now perform your
update and see if s_list did, in fact, change to NULL for the given row.

--
Mark Parsons
Pegasys Ltd


TePe Posted on 2000-01-24 16:21:32.0Z
From: "TePe" <tomekp@profis.com.pl>
References: <jw2PhKDZ$GA.300@forums.sybase.com> <388937CC.4739E878@compuserve.com>
Subject: Re: UPDATE TRIGGER Problem
Date: Mon, 24 Jan 2000 17:21:32 +0100
Lines: 77
X-Newsreader: Microsoft Outlook Express 4.72.3612.1700
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3612.1700
Message-ID: <n$l4YeoZ$GA.85@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: pc100.wroclaw.ppp.tpnet.pl 212.160.40.100
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2654
Article PK: 1090100

It isn't the first time, I encountered the problem, I described earlier.
It also happend in other tables (even without triggers).
For instance :
I've added a column to table 'for_invoice' (there are some data in this
table, the structure is : iid_for_invoice (primary_key), ibo_invoice) :
"ALTER TABLE for_invoice ADD COLUMN s_list VARCHAR(255) NULL"
Next, I've written a test trigger like this :
"CREATE TRIGGER test_sl ON for_invoice
FOR UPDATE AS
IF UPDATE (s_list)
BEGIN
print 'sub_list'
END
IF UPDATE (ibo_invoice)
BEGIN
DECLARE @ins_list varchar(255)
DECLARE @del_list varchar(255)
select @ins_list = 'ins=' + IsNull( subscription_list, 'NULL' ) from
inserted
select @del_list = 'del=' + IsNull( subscription_list, 'NULL' ) from
deleted
print 'ibo_invoice'
print @ins_list
print @del_list
END"
Next, I've executed command on row with primary key value = 1
(using SQL Advantage):
"UPDATE for_invoice set s_list = '1', ibo_invoice = 1 where iid_for_invoice
= 1"
I've got results :
'sub_list
ibo_invoice
ins=1
del=NULL' and this what I've expected...
But, next, I've executed command :
"UPDATE for_invoice set ibo_invoice = 1 where
iid_for_invoice = 1"
... and I've got results :
'ibo_invoice
ins=NULL
del=1'
Table inserted contains NULL value of column s_list !!!
After dropping and recreating table for_invoice everything works fine.
I suppose, it is a problem with system databases (master?), maybe
procedures,
but I'm not an expert. Should I execute some system procedures
to refresh my database ? I use ASE 11.5.

>> If column iid_asset is updated, the trigger works fine (column s_list is
>> updated with value '1'). But, if another
>> column is updated (not iid_asset and not s_list -> so trigger shouldn't
>> fire), column s_list is updated with NULL. Why ?
>> After dropping table and recreating it with s_list column, everything
works
>> fine. But I dont't want to do this.
>> I tried some sytem procedures like sp_recompile, but it won't help me. Is
>> there any other solution ?
>
>A couple ideas to start with ...
>
>1) Make sure you create the trigger *after* you've modified the table.
>If the trigger exists before you make the table change ... just
>re-create the trigger after the table has been modified. This way the
>trigger should be re-compiled with the table's new characteristics.
>
>2) In the situation where you want to update/modify a column other than
>iid_asset, make sure ... before hand ... that s_list is not already
>NULL. (Are you, by any chance, using 'begin/commit/rollback tran'
>during any of this? I'm just wondering if you could be issuing a
>rollback that's undoing your updates of s_list?) Now perform your
>update and see if s_list did, in fact, change to NULL for the given row.
>
>--
>Mark Parsons
>Pegasys Ltd


Mark A. Parsons Posted on 2000-01-25 06:03:04.0Z
Message-ID: <388D3C98.E715EF86@compuserve.com>
Date: Tue, 25 Jan 2000 19:03:04 +1300
From: "Mark A. Parsons" <iron_horse@compuserve.com>
Organization: Pegasys Ltd
X-Mailer: Mozilla 4.61 [en] (Win98; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: UPDATE TRIGGER Problem
References: <jw2PhKDZ$GA.300@forums.sybase.com> <388937CC.4739E878@compuserve.com> <n$l4YeoZ$GA.85@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 24
NNTP-Posting-Host: p379.ipa1-n8-16.iconz.net.nz 210.48.25.123
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2648
Article PK: 1090099

It sounds like your concern is with your output, i.e., the contents of
@ins_list and @del_list, right?

First, assuming that this is your true/actual trigger code ... could you
explain why you're setting @ins_list and @del_list to the value of
'subscription_list' and not 's_list' (since this appears to be the
column you're focusing on)??

Second, is this your entire (the WHOLE THING) trigger code? Did you
leave anything out? (Just wondering if you could have additional code
which is firing a trigger on another table which, in turn, comes back
and makes additional changes to your target table?)

Third, is the value of subscription_list (or s_list) actually being
changed in the base table?

Fourth, you mention that you drop and recreate the base table and
everything works as expected. What exactly do you mean 'it works',
i.e., what do you get for your output?

--
Mark Parsons
Pegasys Ltd