for update trigger? One of our devs recently put a cursor in his for update
trigger to loop over rows in the inserted table. However, from what I
understand, inserted should never have more than one row in it. I just
wanted to verify this before I removed it as I am working on optimizing it.
Brent Black
Onvia.com
Technical Lead/Database AdministratorThis is a multi-part message in MIME format.
--=_NextPart_000_01FB_01C3F484.E84A26E0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
The inserted table can indeed have > 1 row in it and you code should take
this into account. Likely, you don't need a cursor either.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Brent Black" <bblack@.onvia.com> wrote in message
news:uhVRS4K9DHA.3404@.TK2MSFTNGP09.phx.gbl...
Is it ever possible for the inserted table to have more than one row in a
for update trigger? One of our devs recently put a cursor in his for update
trigger to loop over rows in the inserted table. However, from what I
understand, inserted should never have more than one row in it. I just
wanted to verify this before I removed it as I am working on optimizing it.
Brent Black
Onvia.com
Technical Lead/Database Administrator
--=_NextPart_000_01FB_01C3F484.E84A26E0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
The inserted table can indeed have => 1 row in it and you code should take this into account. Likely, you don't =need a cursor either.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Brent Black"
--=_NextPart_000_01FB_01C3F484.E84A26E0--|||Don't use a cursor in a trigger, typically people do something like this:
update table set column = value where prinmarykey = (select primary key from
inserted)
If you will have multiple updates or inserts you would want to change it to
this
update table set column = value where prinmarykey IN (select primary key
from inserted)
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Brent Black" <bblack@.onvia.com> wrote in message
news:uhVRS4K9DHA.3404@.TK2MSFTNGP09.phx.gbl...
> Is it ever possible for the inserted table to have more than one row in a
> for update trigger? One of our devs recently put a cursor in his for
update
> trigger to loop over rows in the inserted table. However, from what I
> understand, inserted should never have more than one row in it. I just
> wanted to verify this before I removed it as I am working on optimizing
it.
> Brent Black
> Onvia.com
> Technical Lead/Database Administrator
>|||I've been able to do that in every case except where the ID value from the
cursor is being passed into a udf that returns a table.. For example:
insert into sometable (column1, column2)
select distinct @.CursorValue, pgr.ID
from someUDF(@.CursorValue) as pgr
I tried changing this to:
insert into sometable(column1, column2)
select distinct i.ID, pgr.ID
from someUDF(i.ID) as pgr,
inserted i
but that didn't work because it expects a single deterministic value to be
passed into the UDL.. It appears that was why the original dev chose to use
a cursor in the trigger to handle this in the first place. Any ideas on how
to do this without the cursor?
Thanks!
Brent Black
Onvia.com
Technical Lead/Database Administrator
"Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
news:OYCx3KL9DHA.2604@.TK2MSFTNGP10.phx.gbl...
> Don't use a cursor in a trigger, typically people do something like this:
> update table set column = value where prinmarykey = (select primary key
from
> inserted)
> If you will have multiple updates or inserts you would want to change it
to
> this
> update table set column = value where prinmarykey IN (select primary key
> from inserted)
> HTH
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Brent Black" <bblack@.onvia.com> wrote in message
> news:uhVRS4K9DHA.3404@.TK2MSFTNGP09.phx.gbl...
> > Is it ever possible for the inserted table to have more than one row in
a
> > for update trigger? One of our devs recently put a cursor in his for
> update
> > trigger to loop over rows in the inserted table. However, from what I
> > understand, inserted should never have more than one row in it. I just
> > wanted to verify this before I removed it as I am working on optimizing
> it.
> >
> > Brent Black
> > Onvia.com
> > Technical Lead/Database Administrator
> >
> >
>|||What's the UDF look like?
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Brent Black" <bblack@.onvia.com> wrote in message
news:ucGCsKN9DHA.1936@.TK2MSFTNGP12.phx.gbl...
> I've been able to do that in every case except where the ID value from the
> cursor is being passed into a udf that returns a table.. For example:
> insert into sometable (column1, column2)
> select distinct @.CursorValue, pgr.ID
> from someUDF(@.CursorValue) as pgr
> I tried changing this to:
> insert into sometable(column1, column2)
> select distinct i.ID, pgr.ID
> from someUDF(i.ID) as pgr,
> inserted i
> but that didn't work because it expects a single deterministic value to
be
> passed into the UDL.. It appears that was why the original dev chose to
use
> a cursor in the trigger to handle this in the first place. Any ideas on
how
> to do this without the cursor?
> Thanks!
> Brent Black
> Onvia.com
> Technical Lead/Database Administrator
> "Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
> news:OYCx3KL9DHA.2604@.TK2MSFTNGP10.phx.gbl...
> > Don't use a cursor in a trigger, typically people do something like
this:
> >
> > update table set column = value where prinmarykey = (select primary key
> from
> > inserted)
> >
> > If you will have multiple updates or inserts you would want to change it
> to
> > this
> >
> > update table set column = value where prinmarykey IN (select primary key
> > from inserted)
> >
> > HTH
> > --
> > Ray Higdon MCSE, MCDBA, CCNA
> > --
> > "Brent Black" <bblack@.onvia.com> wrote in message
> > news:uhVRS4K9DHA.3404@.TK2MSFTNGP09.phx.gbl...
> > > Is it ever possible for the inserted table to have more than one row
in
> a
> > > for update trigger? One of our devs recently put a cursor in his for
> > update
> > > trigger to loop over rows in the inserted table. However, from what I
> > > understand, inserted should never have more than one row in it. I
just
> > > wanted to verify this before I removed it as I am working on
optimizing
> > it.
> > >
> > > Brent Black
> > > Onvia.com
> > > Technical Lead/Database Administrator
> > >
> > >
> >
> >
>|||Brent,
I wouldn't be surprised if in this case the UDF is something like
create function someUDF(
@.v somedatatype
) returns table ...
WHERE someColumn = @.v
...
If that's the case, then the trigger could probably be written by
joining the inserted
table with whatever the current UDF applies its WHERE clause to, or with
not much more work than that.
In other words, as Ray said, what does the UDF (and the trigger) look like?
SK
Brent Black wrote:
>I've been able to do that in every case except where the ID value from the
>cursor is being passed into a udf that returns a table.. For example:
>insert into sometable (column1, column2)
> select distinct @.CursorValue, pgr.ID
> from someUDF(@.CursorValue) as pgr
>I tried changing this to:
>insert into sometable(column1, column2)
> select distinct i.ID, pgr.ID
> from someUDF(i.ID) as pgr,
> inserted i
> but that didn't work because it expects a single deterministic value to be
>passed into the UDL.. It appears that was why the original dev chose to use
>a cursor in the trigger to handle this in the first place. Any ideas on how
>to do this without the cursor?
>Thanks!
>Brent Black
>Onvia.com
>Technical Lead/Database Administrator
>"Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
>news:OYCx3KL9DHA.2604@.TK2MSFTNGP10.phx.gbl...
>
>>Don't use a cursor in a trigger, typically people do something like this:
>>update table set column = value where prinmarykey = (select primary key
>>
>from
>
>>inserted)
>>If you will have multiple updates or inserts you would want to change it
>>
>to
>
>>this
>>update table set column = value where prinmarykey IN (select primary key
>>from inserted)
>>HTH
>>--
>>Ray Higdon MCSE, MCDBA, CCNA
>>--
>>"Brent Black" <bblack@.onvia.com> wrote in message
>>news:uhVRS4K9DHA.3404@.TK2MSFTNGP09.phx.gbl...
>>
>>Is it ever possible for the inserted table to have more than one row in
>>
>a
>
>>for update trigger? One of our devs recently put a cursor in his for
>>
>>update
>>
>>trigger to loop over rows in the inserted table. However, from what I
>>understand, inserted should never have more than one row in it. I just
>>wanted to verify this before I removed it as I am working on optimizing
>>
>>it.
>>
>>Brent Black
>>Onvia.com
>>Technical Lead/Database Administrator
>>
>>
>>
>
>|||Hi Brent,
Thank you for using the newsgroup.
Here is an example for your reference, you could run in your Query Analyzer:
use pubs
go
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[authorsx]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[authorsx]
GO
CREATE TABLE [dbo].[authorsx] (
[au_id] [id] NOT NULL ,
[au_lname] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[au_fname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[phone] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[address] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[city] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[state] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[zip] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[contract] [bit] NOT NULL ,
[test_column] varchar(2)
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[author_fun]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[author_fun]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create function author_fun(@.state varchar(30))
returns table
as
return(select * from authors where @.state=authors.state
)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
truncate table authorsx
insert into authorsx select *,1 from author_fun('CA')
select * from authorsx
go
drop table authorsx
So, I agree with Ray that if your the value returned by the UDF is match
the column you want to insert to or not.
Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Hi Brent,
I am reviewing you post and since I have not heard from you for some time,
I wonder whether you have solved you problem or you still have any
questions about that. For any questions, please feel free to post new
message here and I am glad to help.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.
No comments:
Post a Comment