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.

Help - Update Trigger

5 posts in Windows NT Last posting was on 1998-02-18 16:28:29.0Z
Todd McGuire Posted on 1998-02-17 12:54:40.0Z
Message-ID: <34E98890.28DD@softworkscc.com>
Date: Tue, 17 Feb 1998 07:54:40 -0500
From: Todd McGuire <todd@softworkscc.com>
Organization: SOFTWORKS
X-Mailer: Mozilla 3.01 (Win95; I)
MIME-Version: 1.0
Subject: Help - Update Trigger
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 13
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5185
Article PK: 1081267

I have a need to get the following trigger to work (shortened for
simplicity), or implement in a different way. This trigger works when 1
row is updated, but does not when multiple rows are updated. Does
anyone have any ideas?

CREATE TRIGGER tr_CompanyUpdate ON Company
FOR UPDATE AS
BEGIN
declare @CoID integer
select @CoID = (select pk_company from inserted)
update company set subscr_list = 'TEXT' where pk_company = @CoID
END

Thanks,
Todd McGuire


Mark A. Parsons Posted on 1998-02-18 01:21:18.0Z
Message-ID: <34EA378E.2B68@compuserve.com>
Date: Tue, 17 Feb 1998 20:21:18 -0500
From: "Mark A. Parsons" <Iron_Horse@compuserve.com>
Reply-To: Iron_Horse@compuserve.com
Organization: Iron Horse, Inc.
X-Mailer: Mozilla 3.01 (Win95; I)
MIME-Version: 1.0
Subject: Re: Help - Update Trigger
References: <34E98890.28DD@softworkscc.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 41
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5180
Article PK: 1081260


Todd McGuire wrote:
>
> I have a need to get the following trigger to work (shortened for
> simplicity), or implement in a different way. This trigger works when 1
> row is updated, but does not when multiple rows are updated. Does
> anyone have any ideas?
>
> CREATE TRIGGER tr_CompanyUpdate ON Company
> FOR UPDATE AS
> BEGIN
> declare @CoID integer
> select @CoID = (select pk_company from inserted)
> update company set subscr_list = 'TEXT' where pk_company = @CoID
> END
>
> Thanks,
> Todd McGuire

The problem is that @CoID is a variable and can thus only hold *one*
value. When you go to populate @CoID with your 'select' ... the select
pulls back all pk_company values from the inserted table, placing the
'last' one (from the select'c result set) into @CoID. From here you can
only update that row in company where pk_company = @CoID.

If you're planning to set subscr_list to the same value for all matching
rows from the inserted table ... and you're pretty sure that the
following 'mass' update will not generate a table lock (or that it'll
hold it for such a short period that it won't be 'noticeable') then try
the following:

update company
set subscr_list = 'TEXT'
from inserted i,
company c
where c.pk_company = i.pk_company

If you're worried about obtaining a table lock and/or unnecessary
blocking of other users then you'll need to use a cursor method like
John McVicker's posted ... or use a pseudo-cursor if you have
performance problems with the 'regular' cursor.

--

Mark Parsons
Iron Horse, Inc.
[Team Sybase]


Todd McGuire Posted on 1998-02-18 16:28:29.0Z
Message-ID: <34EB0C2D.290D@softworkscc.com>
Date: Wed, 18 Feb 1998 11:28:29 -0500
From: Todd McGuire <todd@softworkscc.com>
Organization: SOFTWORKS
X-Mailer: Mozilla 3.01 (Win95; I)
MIME-Version: 1.0
Subject: Re: Help - Update Trigger
References: <34E98890.28DD@softworkscc.com> <34EA378E.2B68@compuserve.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 51
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5177
Article PK: 1081256


Mark A. Parsons wrote:
>
> Todd McGuire wrote:
> >
> > I have a need to get the following trigger to work (shortened for
> > simplicity), or implement in a different way. This trigger works when 1
> > row is updated, but does not when multiple rows are updated. Does
> > anyone have any ideas?
> >
> > CREATE TRIGGER tr_CompanyUpdate ON Company
> > FOR UPDATE AS
> > BEGIN
> > declare @CoID integer
> > select @CoID = (select pk_company from inserted)
> > update company set subscr_list = 'TEXT' where pk_company = @CoID
> > END
> >
> > Thanks,
> > Todd McGuire
>
> The problem is that @CoID is a variable and can thus only hold *one*
> value. When you go to populate @CoID with your 'select' ... the select
> pulls back all pk_company values from the inserted table, placing the
> 'last' one (from the select'c result set) into @CoID. From here you can
> only update that row in company where pk_company = @CoID.
>
> If you're planning to set subscr_list to the same value for all matching
> rows from the inserted table ... and you're pretty sure that the
> following 'mass' update will not generate a table lock (or that it'll
> hold it for such a short period that it won't be 'noticeable') then try
> the following:
>
> update company
> set subscr_list = 'TEXT'
> from inserted i,
> company c
> where c.pk_company = i.pk_company
>
> If you're worried about obtaining a table lock and/or unnecessary
> blocking of other users then you'll need to use a cursor method like
> John McVicker's posted ... or use a pseudo-cursor if you have
> performance problems with the 'regular' cursor.
>
> --
>
> Mark Parsons
> Iron Horse, Inc.
> [Team Sybase]

Mark,

Thanks for the reply. The value for subcr_list may be different for all
the updated rows. Thanks for the tip on pseudo-cursors too. I think
John McVicker response looks like what I need.

Todd McGuire


John McVicker Posted on 1998-02-17 12:38:04.0Z
From: "John McVicker" <mcvicker@sybase.com>
Organization: 192.175.209.26
References: <34E98890.28DD@softworkscc.com>
X-Newsreader: AspNNTP (Advent 2000, Inc.)
Subject: Re: Help - Update Trigger
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Message-ID: <yTyYkD6O9GA.421@forums.powersoft.com>
Newsgroups: sybase.public.sqlserver.nt
Date: Tue, 17 Feb 1998 07:38:04 -0500
Lines: 47
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5184
Article PK: 1081264

Todd,
Yes, first thing I see is a risk where more than one row is hit.
Here's what you do (what version are you on? This example will
work for System 10 and above). This works for 0, 1 or more rows.
It's a little longer, but a good template for writing multi-row
cursor-based triggers.

CREATE TRIGGER tr_CompanyUpdate ON Company
FOR UPDATE AS
BEGIN
/* if nothing actually got updated, exit the trigger */
IF @@rowcount < 1
RETURN

/* Local Variables */
DECLARE @CoID integer

/* Cursor(s) for inserted and deleted */
DECLARE i_cur CURSOR FOR
SELECT pk_company
FROM inserted
FOR READ ONLY
OPEN i_cur
FETCH i_cur INTO @ColID

WHILE @@sqlstatus = 0
BEGIN
UPDATE company
SET subscr_list = 'TEXT'
WHERE pk_company = @CoID

FETCH i_cur INTO @ColID
END
/* Should exist loop with @@sqlstatus = 2 */

IF @@sqlstatus = 1 /* Error - now what? */
BEGIN
CLOSE i_cur
DEALLOCATE CURSOR i_cur
RAISERROR <n> "MSG" /* see doc set on RAISERROR */
ROLLBACK TRIGGER
END
CLOSE i_cur
DEALLOCATE CURSOR i_cur

END /* END Trigger */

On Tue, 17 Feb 1998 07:54:40 -0500,
in sybase.public.sqlserver.nt

Todd McGuire <todd@softworkscc.com> wrote:
>I have a need to get the following trigger to work (shortened for
>simplicity), or implement in a different way. This trigger works when 1
>row is updated, but does not when multiple rows are updated. Does
>anyone have any ideas?
>
>CREATE TRIGGER tr_CompanyUpdate ON Company
> FOR UPDATE AS
> BEGIN
> declare @CoID integer
> select @CoID = (select pk_company from inserted)
> update company set subscr_list = 'TEXT' where pk_company = @CoID
> END
>
>Thanks,
>Todd McGuire

John McVicker
Principal Consultant/District Lead Architect
Sybase Professional Services
Philadelphia, PA
301-896-1765


Todd McGuire Posted on 1998-02-18 16:22:31.0Z
Message-ID: <34EB0AC7.1DB1@softworkscc.com>
Date: Wed, 18 Feb 1998 11:22:31 -0500
From: Todd McGuire <todd@softworkscc.com>
Organization: SOFTWORKS
X-Mailer: Mozilla 3.01 (Win95; I)
MIME-Version: 1.0
Subject: Re: Help - Update Trigger
References: <34E98890.28DD@softworkscc.com> <yTyYkD6O9GA.421@forums.powersoft.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 53
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5178
Article PK: 1081258

John,
Thanks for the reply... This looks like what I'm looking for.

Todd McGuire

John McVicker wrote:
>
> Todd,
> Yes, first thing I see is a risk where more than one row is hit.
> Here's what you do (what version are you on? This example will
> work for System 10 and above). This works for 0, 1 or more rows.
> It's a little longer, but a good template for writing multi-row
> cursor-based triggers.
>
> CREATE TRIGGER tr_CompanyUpdate ON Company
> FOR UPDATE AS
> BEGIN
> /* if nothing actually got updated, exit the trigger */
> IF @@rowcount < 1
> RETURN
>
> /* Local Variables */
> DECLARE @CoID integer
>
> /* Cursor(s) for inserted and deleted */
> DECLARE i_cur CURSOR FOR
> SELECT pk_company
> FROM inserted
> FOR READ ONLY
> OPEN i_cur
> FETCH i_cur INTO @ColID
>
> WHILE @@sqlstatus = 0
> BEGIN
> UPDATE company
> SET subscr_list = 'TEXT'
> WHERE pk_company = @CoID
>
> FETCH i_cur INTO @ColID
> END
> /* Should exist loop with @@sqlstatus = 2 */
>
> IF @@sqlstatus = 1 /* Error - now what? */
> BEGIN
> CLOSE i_cur
> DEALLOCATE CURSOR i_cur
> RAISERROR <n> "MSG" /* see doc set on RAISERROR */
> ROLLBACK TRIGGER
> END
> CLOSE i_cur
> DEALLOCATE CURSOR i_cur
>
> END /* END Trigger */
>
> On Tue, 17 Feb 1998 07:54:40 -0500,
> in sybase.public.sqlserver.nt
> Todd McGuire <todd@softworkscc.com> wrote:
> >I have a need to get the following trigger to work (shortened for
> >simplicity), or implement in a different way. This trigger works when 1
> >row is updated, but does not when multiple rows are updated. Does
> >anyone have any ideas?
> >
> >CREATE TRIGGER tr_CompanyUpdate ON Company
> > FOR UPDATE AS
> > BEGIN
> > declare @CoID integer
> > select @CoID = (select pk_company from inserted)
> > update company set subscr_list = 'TEXT' where pk_company = @CoID
> > END
> >
> >Thanks,
> >Todd McGuire
>
> John McVicker
> Principal Consultant/District Lead Architect
> Sybase Professional Services
> Philadelphia, PA
> 301-896-1765