Returning PK of Multiple inserts

25 Aug 2006 11:10 AM

Robert Bravery

HI All,

I have a triger, tat inserts tree-hierchy of related data based on the
initial row inserted. What I would like to do is return all the PK of those
inserted rows.
So
A users inserts row PK1
THe trigger then isnerts related rows from a tree structured table
INSERT INTO policysection_division SELECT @pskey, divkey,@bordcode,
@policyperiod FROM Division WHERE lineage LIKE ('%'+ltrim(str(@divkey))+'%')

Triger inserts row PK2
Triger inserts row PK3
Triger inserts row PK4
Triger inserts row PK5
Triger inserts row PK6

I would then once this process has finnished, like to return the pk
1,2,3,4,5,6
So that I can use this for another process

THanks
Robert

25 Aug 2006 12:04 PM

David Portas

Robert Bravery wrote:
Show quote

> HI All,
>
> I have a triger, tat inserts tree-hierchy of related data based on the
> initial row inserted. What I would like to do is return all the PK of those
> inserted rows.
> So
> A users inserts row PK1
> THe trigger then isnerts related rows from a tree structured table
> INSERT INTO policysection_division SELECT @pskey, divkey,@bordcode,
> @policyperiod FROM Division WHERE lineage LIKE ('%'+ltrim(str(@divkey))+'%')
>
> Triger inserts row PK2
> Triger inserts row PK3
> Triger inserts row PK4
> Triger inserts row PK5
> Triger inserts row PK6
>
> I would then once this process has finnished, like to return the pk
> 1,2,3,4,5,6
> So that I can use this for another process
>
>
> THanks
> Robert

My guess is you can do this:

SELECT p.pk_col
FROM policysection_division AS p
JOIN Division AS d
ON p.divkey = d.divkey
WHERE p.pskey = @pskey
AND p.bordcode = @bordcode
AND p.policyperiod = @policyperiod
AND d.lineage LIKE ('%'+ltrim(str(@divkey))+'%') ;

If there are multiple inserts then you can collect the results in a
table variable and output them at the end.

SQL Server 2005 also has the OUTPUT clause on the INSERT statement,
which makes the same task much easier. Please remember to mention what
version you are using when you post a question.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

25 Aug 2006 1:38 PM

WebBuilder451

would it be possible to use the output feature in 2005 to get this result? So
that you'd output the inserted records on insert when the triger is fired? I
know it can on a normal insert ant i might using merge?

--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes

Show quote

"David Portas" wrote:

> Robert Bravery wrote:
> > HI All,
> >
> > I have a triger, tat inserts tree-hierchy of related data based on the
> > initial row inserted. What I would like to do is return all the PK of those
> > inserted rows.
> > So
> > A users inserts row PK1
> > THe trigger then isnerts related rows from a tree structured table
> > INSERT INTO policysection_division SELECT @pskey, divkey,@bordcode,
> > @policyperiod FROM Division WHERE lineage LIKE ('%'+ltrim(str(@divkey))+'%')
> >
> > Triger inserts row PK2
> > Triger inserts row PK3
> > Triger inserts row PK4
> > Triger inserts row PK5
> > Triger inserts row PK6
> >
> > I would then once this process has finnished, like to return the pk
> > 1,2,3,4,5,6
> > So that I can use this for another process
> >
> >
> > THanks
> > Robert
>
> My guess is you can do this:
>
> SELECT p.pk_col
> FROM policysection_division AS p
> JOIN Division AS d
>   ON p.divkey = d.divkey
> WHERE p.pskey = @pskey
>   AND p.bordcode = @bordcode
>   AND p.policyperiod = @policyperiod
>   AND d.lineage LIKE ('%'+ltrim(str(@divkey))+'%') ;
>
> If there are multiple inserts then you can collect the results in a
> table variable and output them at the end.
>
> SQL Server 2005 also has the OUTPUT clause on the INSERT statement,
> which makes the same task much easier. Please remember to mention what
> version you are using when you post a question.
>
> --
> David Portas, SQL Server MVP
>
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
>
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>

25 Aug 2006 7:24 PM

David Portas

WebBuilder451 wrote:

> would it be possible to use the output feature in 2005 to get this result? So
> that you'd output the inserted records on insert when the triger is fired? I
> know it can on a normal insert ant i might using merge?

After posting my original reply I realised that in 2005 it would make
more sense to use a recursive CTE to populate the hierarchy. That way
there would be no need to use an explicit loop and most likely no need
for a trigger at all.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

25 Aug 2006 9:45 PM

--CELKO--

>> The trigger then inserts related rows from a tree structured table <<

What is a "tree structured table"? I have a whole book on various
methods for modeling trees and hierarchies. Please post DDL, so that
people do not have to guess what the keys, constraints, Declarative
Referential Integrity, data types, etc. in your schema are. Sample data
is also a good idea, along with clear specifications. It is very hard
to debug code when you do not let us see it.