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.

Trigger problem or BUG ?

6 posts in Windows NT Last posting was on 1999-12-23 04:59:34.0Z
Radek Pospisil Posted on 1999-12-14 15:32:50.0Z
From: "Radek Pospisil" <pospisil@sprinx.cz>
Subject: Trigger problem or BUG ?
Date: Tue, 14 Dec 1999 16:32:50 +0100
Lines: 17
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2314.1300
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Message-ID: <fLJG6hkR$GA.191@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: 195.119.182.101
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2822
Article PK: 1090260

I have an insert trigger on the table. Inside the trigger, there is a
statement with "select ... from inserted" . When I perform insert into that
table, the SQL server proccess hangs up. In the Sybase Central Proccess view
there is a command SELECT with sa login and status sleeping or runable.
Only way to resume is to kill the proccess. When I change the trigger to not
use the inserted pseudo table, all inserts work fine.

does enybody have similar expirience ? what can be wrong ? any ideas? is it
an Sybase bug ?

I am using ASE 11.2.9 for NT.

Thanx a lot

R.Pospisil


Arun Kamat Posted on 1999-12-15 12:28:56.0Z
Message-ID: <38578988.AAE9BF04@tkg.att.ne.jp>
Date: Wed, 15 Dec 1999 21:28:56 +0900
From: Arun Kamat <annarun@tkg.att.ne.jp>
X-Mailer: Mozilla 4.05 [en] (Win95; I)
MIME-Version: 1.0
Subject: Re: Trigger problem or BUG ?
References: <fLJG6hkR$GA.191@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 21
NNTP-Posting-Host: 117.pool19.tokyo.att.ne.jp 165.76.224.132
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2816
Article PK: 1090254

Did you execute alter table lock? If yes then you have come across problem no.
199655. Re-create the trigger that would solve the problem.

Arun

Radek Pospisil wrote:

> I have an insert trigger on the table. Inside the trigger, there is a
> statement with "select ... from inserted" . When I perform insert into that
> table, the SQL server proccess hangs up. In the Sybase Central Proccess view
> there is a command SELECT with sa login and status sleeping or runable.
> Only way to resume is to kill the proccess. When I change the trigger to not
> use the inserted pseudo table, all inserts work fine.
>
> does enybody have similar expirience ? what can be wrong ? any ideas? is it
> an Sybase bug ?
>
> I am using ASE 11.2.9 for NT.
>
> Thanx a lot
>
> R.Pospisil


Radek Pospisil Posted on 1999-12-17 13:35:06.0Z
From: "Radek Pospisil" <pospisil@sprinx.cz>
References: <fLJG6hkR$GA.191@forums.sybase.com> <38578988.AAE9BF04@tkg.att.ne.jp>
Subject: Re: Trigger problem or BUG ?
Date: Fri, 17 Dec 1999 14:35:06 +0100
Lines: 53
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2314.1300
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Message-ID: <bxyQKOJS$GA.164@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: 195.119.182.101
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2810
Article PK: 1090248

Recreating trigger does not help. Without trigger all inserts work fine but
with trigger process always hangs up on command COND ...

The trigger is as simple as this:

create trigger ti_vozidlo on vozidlo for INSERT as
begin
if (select count(*) from zakaznik t1, inserted t2 where
t1.id_zakaznik = t2.id_zakaznik) = 0
begin
rollback trigger
end
return
end

Any suggestions ?

Radek

Arun Kamat <annarun@tkg.att.ne.jp> píse v diskusním
pøíspìvku:38578988.AAE9BF04@tkg.att.ne.jp...

> Did you execute alter table lock? If yes then you have come across problem
no.
> 199655. Re-create the trigger that would solve the problem.
>
> Arun
>
> Radek Pospisil wrote:
>
> > I have an insert trigger on the table. Inside the trigger, there is a
> > statement with "select ... from inserted" . When I perform insert into
that
> > table, the SQL server proccess hangs up. In the Sybase Central Proccess
view
> > there is a command SELECT with sa login and status sleeping or runable.
> > Only way to resume is to kill the proccess. When I change the trigger to
not
> > use the inserted pseudo table, all inserts work fine.
> >
> > does enybody have similar expirience ? what can be wrong ? any ideas? is
it
> > an Sybase bug ?
> >
> > I am using ASE 11.2.9 for NT.
> >
> > Thanx a lot
> >
> > R.Pospisil
>
>
>


Mark A. Parsons Posted on 1999-12-18 04:52:18.0Z
Message-ID: <385B1302.6577E58C@compuserve.com>
Date: Sat, 18 Dec 1999 17:52:18 +1300
From: "Mark A. Parsons" <iron_horse@compuserve.com>
Organization: Iron Horse, Inc.
X-Mailer: Mozilla 4.61 [en] (Win98; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Trigger problem or BUG ?
References: <fLJG6hkR$GA.191@forums.sybase.com> <38578988.AAE9BF04@tkg.att.ne.jp> <bxyQKOJS$GA.164@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 31
NNTP-Posting-Host: p311.ipa1-n8-16.iconz.net.nz 210.48.25.55
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2804
Article PK: 1090242


Radek Pospisil wrote:
>
> Recreating trigger does not help. Without trigger all inserts work fine but
> with trigger process always hangs up on command COND ...
>
> The trigger is as simple as this:
>

... snip ...

I would suggest you post the *entire* trigger as you may not be zero'ing
in on the correct statement. I would also suggest you post the entire
error message (if any) for said trigger.

One other thing ... you mention that the process *hangs* ... what do you
mean by *hang*? Does it ever complete? Is the process eating up cpu or
disk cycles? Can you see the process doing a lot of I/O's? Does just
this individual connection/process *hang* or does the entire dataserver
*hang*? Is this process blocked by another process?

In your first post you mentioned doing a SELECT against 'inserted' and
then an insert into 'that table' ... are you talking about doing an
insert into your user table or the table named 'inserted'? If doing an
insert into the table named 'inserted' ... this is not allowed as
'inserted' is a pseudo table that is only usable as the target of a
SELECT in a trigger. (duh, Mark!?!?)


--
Mark Parsons
Iron Horse Consulting, Ltd.


Radek Pospisil Posted on 1999-12-20 11:44:44.0Z
From: "Radek Pospisil" <pospisil@sprinx.cz>
References: <fLJG6hkR$GA.191@forums.sybase.com> <38578988.AAE9BF04@tkg.att.ne.jp> <bxyQKOJS$GA.164@forums.sybase.com> <385B1302.6577E58C@compuserve.com>
Subject: Re: Trigger problem or BUG ?
Date: Mon, 20 Dec 1999 12:44:44 +0100
Lines: 232
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2314.1300
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Message-ID: <n8SKi#tS$GA.287@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: 195.119.182.101
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2798
Article PK: 1090236

OK, I tell the whole story:

I opened SQL console (SQL Advantage), logged in under username "jine" and
manualy executed this insert statement:

INSERT INTO vozidlo (cis_asistence, cis_tech_prukaz, cislo_podvozek,
datum_prodej,
id_dealer, id_karta, id_model, id_osoba, id_typ_vuz, id_vozidlo,
id_zakaznik, kod_klic,
kod_radia, mesic_vyroby, platd_do, platd_od, pocet_komp, spz, stav, tp,
typ_radia,
vyr_cis_radia, zmena_kdo, zmena_kdy)
VALUES (NULL, NULL, NULL, NULL, 3,1,1509, NULL, 1, 3498, 359, NULL, NULL,
'1999/12/20 0:0:0:0', '1999/12/31 0:0:0:0', '1999/12/20 10:39:31:656', 0,
NULL,
NULL, NULL, NULL, NULL, 'jine', '1999/12/20 10:39:31:656')

... and there was no response from the SQL server. The console's statusbar
indicated: "Executing ..." but after 10 minutes, there was no response.
So I opened another console and executed sp_who. And the result is:

fid spid status loginame origname hostname
blk dbname cmd
----------- ----------- ------ -------- -------- -------- --
- ------ ---
0 1 sleeping sa sa TISHA 0
autec COND
0 2 sleeping NULL NULL 0
master NETWORK HANDLER
0 3 sleeping NULL NULL 0
master NETWORK HANDLER
0 4 sleeping NULL NULL 0
master DEADLOCK TUNE
0 5 sleeping NULL NULL 0
master MIRROR HANDLER
0 6 sleeping NULL NULL 0
master SHUTDOWN HANDLER
0 7 sleeping NULL NULL 0
master CHECKPOINT SLEEP
0 8 sleeping NULL NULL 0
master HOUSEKEEPER
0 10 running sa sa TISHA 0
master SELECT

The interesting process is spid 1 (the one who executed INSERT statement). I
dont know what command COND means and why it is sleeping. Notice that the
loginname changed from "jine" to "sa". That's why I assumed it is a trigger
problem. When I look on the server, the CPU is about 50% busy and there is
no unusual disk activity. There is no error message indicating any troubles.

The definition for the "vozidlo" is this:

CREATE TABLE dbo.vozidlo (
id_vozidlo t_id,
id_osoba int NULL,
id_zakaznik int,
id_karta t_id,
id_dealer int,
id_typ_vuz t_id,
id_model t_id,
cislo_podvozek char(7) NULL,
datum_prodej t_datum NULL,
spz char(10) NULL,
cis_tech_prukaz char(10) NULL,
cis_asistence char(10) NULL,
kod_klic char(10) NULL,
kod_radia char(4) NULL,
vyr_cis_radia char(8) NULL,
pocet_komp int NULL,
zmena_kdo t_zmena_kdo NULL,
typ_radia char(6) NULL,
stav char(1) NULL,
zmena_kdy t_platnost NULL,
platd_do t_platnost NULL,
platd_od t_platnost NULL,
mesic_vyroby t_datum NULL,
tp char(1) NULL
)

And finally the trigger is as folows:

create trigger ti_vozidlo on vozidlo for INSERT as
begin
declare
@numrows int,
@numnull int,
@errno int,
@errmsg varchar(255)

select @numrows = @@rowcount
if @numrows = 0
return


/* parent "zakaznik" must exist when inserting a child in "vozidlo" */
if update(id_zakaznik)
begin
if (select count(*)
from zakaznik t1, inserted t2
where t1.id_zakaznik = t2.id_zakaznik) != @numrows
begin
select @errno = 30002,
@errmsg = 'Záznam v tabulce "zakaznik" neexistuje. Nelze
zalozit vìtu v tabulce "vozidlo".'
goto error
end
end

/* parent "karta" must exist when inserting a child in "vozidlo" */
if update(id_karta)
begin
if (select count(*)
from karta t1, inserted t2
where t1.id_karta = t2.id_karta) != @numrows
begin
select @errno = 30002,
@errmsg = 'Záznam v tabulce "karta" neexistuje. Nelze
zalozit vìtu v tabulce "vozidlo".'
goto error
end
end

/* parent "dealer" must exist when inserting a child in "vozidlo" */
if update(id_dealer)
begin
if (select count(*)
from dealer t1, inserted t2
where t1.id_dealer = t2.id_dealer) != @numrows
begin
select @errno = 30002,
@errmsg = 'Záznam v tabulce "dealer" neexistuje. Nelze
zalozit vìtu v tabulce "vozidlo".'
goto error
end
end

/* parent "typ_vuz" must exist when inserting a child in "vozidlo" */
if update(id_typ_vuz)
begin
if (select count(*)
from typ_vuz t1, inserted t2
where t1.id_typ_vuz = t2.id_typ_vuz) != @numrows
begin
select @errno = 30002,
@errmsg = 'Záznam v tabulce "typ_vuz" neexistuje. Nelze
zalozit vìtu v tabulce "vozidlo".'
goto error
end
end

/* parent "model" must exist when inserting a child in "vozidlo" */
if update(id_model)
begin
if (select count(*)
from model t1, inserted t2
where t1.id_model = t2.id_model) != @numrows
begin
select @errno = 30002,
@errmsg = 'Záznam v tabulce "model" neexistuje. Nelze
zalozit vìtu v tabulce "vozidlo".'
goto error
end
end

/* rodiê "osoba" musì existovat kdyz zakládáte "vozidlo" */
if update(id_osoba)
begin
select @numnull = (select count(*)
from inserted
where id_osoba is null)
if @numnull != @numrows
if (select count(*)
from osoba t1, inserted t2
where t1.id_osoba = t2.id_osoba) != @numrows - @numnull
begin
select @errno = 30002,
@errmsg = 'Záznam v tabulce "osoba" neexistuje. Nelze
zalozit vìtu v tabulce "vozidlo".'
goto error
end
end

return

/* errors handling */
error:
raiserror @errno @errmsg
rollback trigger
end


Any suggestions ?

Thank you very much,

Radek

Mark A. Parsons <iron_horse@compuserve.com> píse v diskusním
pøíspìvku:385B1302.6577E58C@compuserve.com...

> Radek Pospisil wrote:
> >
> > Recreating trigger does not help. Without trigger all inserts work fine
but
> > with trigger process always hangs up on command COND ...
> >
> > The trigger is as simple as this:
> >
> ... snip ...
>
> I would suggest you post the *entire* trigger as you may not be zero'ing
> in on the correct statement. I would also suggest you post the entire
> error message (if any) for said trigger.
>
> One other thing ... you mention that the process *hangs* ... what do you
> mean by *hang*? Does it ever complete? Is the process eating up cpu or
> disk cycles? Can you see the process doing a lot of I/O's? Does just
> this individual connection/process *hang* or does the entire dataserver
> *hang*? Is this process blocked by another process?
>
> In your first post you mentioned doing a SELECT against 'inserted' and
> then an insert into 'that table' ... are you talking about doing an
> insert into your user table or the table named 'inserted'? If doing an
> insert into the table named 'inserted' ... this is not allowed as
> 'inserted' is a pseudo table that is only usable as the target of a
> SELECT in a trigger. (duh, Mark!?!?)
>
>
> --
> Mark Parsons
> Iron Horse Consulting, Ltd.


Mark A. Parsons Posted on 1999-12-23 04:59:34.0Z
Message-ID: <3861AC36.BB6683D1@compuserve.com>
Date: Thu, 23 Dec 1999 17:59:34 +1300
From: "Mark A. Parsons" <iron_horse@compuserve.com>
Organization: Iron Horse, Inc.
X-Mailer: Mozilla 4.61 [en] (Win98; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Trigger problem or BUG ?
References: <fLJG6hkR$GA.191@forums.sybase.com> <38578988.AAE9BF04@tkg.att.ne.jp> <bxyQKOJS$GA.164@forums.sybase.com> <385B1302.6577E58C@compuserve.com> <n8SKi#tS$GA.287@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 49
NNTP-Posting-Host: p397.ipa1-n8-16.iconz.net.nz 210.48.25.141
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2779
Article PK: 1090217

OK, a couple suggestions to get started on ...

First, try doing your insert and, once your connections 'hangs', log in
under another connection and check the page numbers returned by sp_lock
(for the 'hung' connection) ... if the page numbers keep changing then
you know that your first connection is probably doing some sort of table
scan. If the process is doing a table scan then figure out which table
(take the object id from sp_lock and look up the table name in
sysobjects ... or use the system function object_name()). Once you get
the table name go through and find the matching piece of code from your
trigger and perform the necessary P&T steps (using an index?, etc.) to
get rid of the table scan. Then wash, repeat, wash, repeat, wash,
repeat ... errrr ... go to the beginning of this paragraph and see if
you have any other problems.

Assuming your 'hung' connection isn't off doing a table scan somewhere
...

Drop the trigger and see if you can get the INSERT to work ok by
itself. Once you get the insert working without the trigger ...

Try running your individual referential integrity checks (SELECTs) from
the command line (isql, SQL Advantage, etc.) based on the data of the
sample row you just inserted (without the trigger in place) ... just to
make sure your queries are running ok (do any of them do table scans?).
Where you're doing count()'s and comparing to sum's ... just get the
count()'s and manually check to see if they equal your sum's (sum's
should equal 1 for a single insert??).

Since this trigger is used for INSERT only (no UPDATEs), I'd suggest
dropping all of the tests for 'if update(..column..)'. (No, I don't
think this is the problem but we might as well get rid of any
'clutter'.)

See if any of this helps and get back to us (the forum) if you have any
other questions/issues.



--
Mark Parsons
Iron Horse Consulting, Ltd.