[dba-SQLServer] tsql help - propogate that pk

Billy Pang tuxedo_man at hotmail.com
Thu Jul 22 19:41:39 CDT 2004 

Thanks Arthur for the tip.  The instead of trigger will help me for single 
inserts into the view because I can use scope_identity value from first 
insert to populate the child record.  Then the instead of trigger may need 
to incorporate a database cursor to loop through all records in inserted 
psuedo table to find the scope_identity value and insert the record into the 
child tables.

Sincerely,
Billy

>From: "Arthur Fuller" <artful at rogers.com>
>Reply-To: dba-sqlserver at databaseadvisors.com
>To: <dba-sqlserver at databaseadvisors.com>
>Subject: RE: [dba-SQLServer] tsql help - propogate that pk
>Date: Thu, 22 Jul 2004 11:00:54 -0400
>
>Have you considered using an "Instead Of" trigger, Billy? That might
>give you everything you need. I just wrote a SQL Tip for Builder.com on
>how and when to use "Instead Of" triggers, but since it hasn't been
>published yet I can't attach it to a public list like this. However, I
>encourage you to look up "Instead Of Triggers" in BOL, and go from
>there. From what I have gathered, an Instead Of trigger will let you
>accomplish exactly what you need.
>
>Arthur
>
>-----Original Message-----
>From: dba-sqlserver-bounces at databaseadvisors.com
>[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Billy
>Pang
>Sent: Friday, July 16, 2004 4:05 PM
>To: dba-sqlserver at databaseadvisors.com
>Subject: Re: [dba-SQLServer] tsql help - propogate that pk
>
>
>Hi Shamil:
>
>The sentence from BOL is absolutely correct.  But it does not give me
>what I
>want; I need to have "identity" values moved from one table to another
>after
>a view insert.  There is no doubt that a view can update multiple base
>tables; there is no question about that; and it is possible to reference
>
>them unambigiously; however, the fact that it is an "instead of insert",
>it
>is not possible to reference any identity values generated from the view
>
>(without using scope_identity()).  This would not be a problem if it was
>
>possible to create an "After" trigger on a view.
>
>And yes, it works eloquently in Access.  For demonstration purposes,
>within
>Access, create two tables with following schema:
>
>tablea
>tablea_id autonumber
>tablea_value text(50)
>
>tableb
>tableb_id autonumber
>tableb_tablea_id number long
>tableb_value text(50)
>
>then create a query called qryInsert with the following source:
>
>SELECT tablea.tablea_id, tableb.tableb_id, tablea.tablea_value,
>tableb.tableb_value, tableb.tableb_tablea_id, tablea.tablea_id FROM
>tablea INNER JOIN tableb ON tablea.tablea_id = tableb.tableb_tablea_id;
>
>then create and execute the following query:
>
>insert into qryInsert(tablea_value, tableb_value) values('a','b')
>
>followed by:
>
>insert into qryInsert(tablea_value, tableb_value)
>select tablea_value, tableb_value
>from qryInsert
>
>and again..
>
>insert into qryInsert(tablea_value, tableb_value)
>select tablea_value, tableb_value
>from qryInsert
>
>and you will find four records in tablea as expected, four records in
>tableb
>as expected and all four records in tablea have related records in
>tableb.
>
>Access was able to do this without any database triggers.  And Access
>was
>able to accommodate "view inserts" that have SELECT as source.  If SQL
>Server is able to immitate this behavior (without using
>scope_identity(), of
>course :) ), then I am would like to know.
>
>Kindest regards,
>Billy
>
> >From: "Shamil Salakhetdinov" <shamil at users.mns.ru>
> >Reply-To: dba-sqlserver at databaseadvisors.com
> >To: dba-sqlserver at databaseadvisors.com
> >Subject: Re: [dba-SQLServer] tsql help - propogate that pk
> >Date: Fri, 16 Jul 2004 22:22:25 +0400
> >
> >Billy,
> >
> >What about that sentence from BOL?:
> >
> >"SQL Server 2000
> >Updatable views can modify more than one table involved in the view.
> >The DELETE, INSERT, and UPDATE statements can reference a view as long
> >as SQL Server can translate the user's update request unambiguously to
> >updates in the base tables referenced in the view's definition. Expect
> >differences in behavior when working with updatable views with more
> >than one table involved in the DELETE, INSERT, or UPDATE statements."
> >
> > > I was actually hoping for a solution that is
> > > quite simple
> >For me the solution looks simple. I called it tricky only because it's
> >not obvious when starting to work with instead of triggers. You can
> >also use a simple stored procedure to fullfill your task...
> >
> > > the view insert (where it
> > > affects multiple tables) works eloquently in MS Access but not in
> > > Sql Server.
> >Are you sure it will work in MS Access for your task?  (This question
> >is not an invitation to MS Access vs. MS SQL2000 discussion)
> >
> >Shamil
> >
> >----- Original Message -----
> >From: "Billy Pang" <tuxedo_man at hotmail.com>
> >To: <dba-sqlserver at databaseadvisors.com>
> >Sent: Friday, July 16, 2004 3:18 AM
> >Subject: Re: [dba-SQLServer] tsql help - propogate that pk
> >
> >
> > > Thanks Shamil for your time.  I was actually hoping for a solution
> >that is
> > > quite simple (perhaps I was overlooking something very obvious).  I
> >thought
> > > that there was a more simple solution because the view insert (where
> >it
> > > affects multiple tables) works eloquently in MS Access but not in
> > > Sql Server.
> > >
> > > Regards,
> > > Billy
> > >
> > >
> > >
> > >
> > > >From: "Shamil Salakhetdinov" <shamil at users.mns.ru>
> > > >Reply-To: dba-sqlserver at databaseadvisors.com
> > > >To: "dba-SQLServer" <dba-SQLServer at databaseadvisors.com>
> > > >Subject: Re: [dba-SQLServer] tsql help - propogate that pk
> > > >Date: Fri, 16 Jul 2004 02:32:23 +0400
> > > >
> > > >Hi Billy,
> > > >
> > > >You didn't ask about inserting several rows using instead of insert
>
> > > >trigger, did you? :)
> > > >
> > > >Well, I think it can be done without cursors but we have to assume
> >that
> > > >there is no UNIVERSAL solution for such a case - I mean that for
> > > >each certain case the cursor-less solution with instead of triggers
>
> > > >could have quite different implementation - as always in
> > > >programming an "optimal" solution is a balance of interests, a
> > > >compromise:
> > > >
> > > >So if we assume that when several rows are inserted against my
> > > >sample view then this means that:
> > > >
> > > >1. they all belong to one order
> > > >2. product name is unique
> > > >
> > > >Then one of the the solutons is:
> > > >
> > > >- insert all rows into tblProduct from inserted if there is no yet
> >such
> > > >product,
> > > >- insert one row into tblOrder from inserted,
> > > >- insert all rows into tblOrdItem from inserted joined to
> > > >tblProduct
> >on
> > > >ProdName
> > > >
> > > >See SQL script in P.S. of this message.
> > > >
> > > >If you need something more complicated - e.g. inserting several
> >orders
> > > >against view like in the current sample avoiding cursors' usage by
> >all
> > > >means - if you need that - then you're welcome to hire me to do
> > > >that
> >and
> > > >other tricky work! :)
> > > >
> > > >HTH,
> > > >Shamil
> > > >
> > > >P.S. Solution:
> > > >
> > > >
> > > >CREATE TRIGGER [qselInsert] ON [dbo].[qselOrder]
> > > >INSTEAD OF INSERT
> > > >AS
> > > >declare @ordID int
> > > >
> > > >Set NoCount On
> > > >
> > > >-- /////////////////////////////////////////////////
> > > >Insert Into [dbo].[tblProduct]
> > > >(
> > > >    [prodName]
> > > >  , [prodCurPrice]
> > > >)
> > > >select
> > > >    prodName
> > > >  , prodCurPrice
> > > >from inserted where (prodName not in (select prodName from
> >tblProduct))
> > > >
> > > >-- /////////////////////////////////////////////////
> > > >Insert Into [dbo].[tblOrder]
> > > >(
> > > >    [ordDate]
> > > >  , [custId]
> > > >  , [ordCurTotal]
> > > >)
> > > >select top 1
> > > >    ordDate
> > > >  , custId
> > > >  , ordCurTotal
> > > >from inserted
> > > >
> > > >Set @ordID = Cast(SCOPE_IDENTITY() As [int])
> > > >-- /////////////////////////////////////////////////
> > > >Insert Into [dbo].[tblOrderItem]
> > > >(
> > > >    [ordId]
> > > >  , [prodId]
> > > >  , [oitAmount]
> > > >)
> > > >select
> > > >    @ordId
> > > >  , p.prodId
> > > >  , oitAmount
> > > >from inserted i inner join tblProduct p on i.prodName = p.prodName
> > > >-- /////////////////////////////////////////////////
> > > >
> > > >Set NoCount Off
> > > >----- Original Message -----
> > > >From: "Billy Pang" <tuxedo_man at hotmail.com>
> > > >To: <dba-sqlserver at databaseadvisors.com>
> > > >Sent: Friday, July 16, 2004 1:18 AM
> > > >Subject: Re: [dba-SQLServer] tsql help - propogate that pk
> > > >
> > > >
> > > > > Hi Shamil:
> > > > >
> > > > > Thanks for your assistance.  Your solution will only work based
> > > > > on
> >the
> > > > > assumption that records can only be inserted into tblOrder one
> > > > > at
> >a
> > > >time; if
> > > > > insert statement for tblOrder table has "select" as source, then
> >the
> > > > > tblorderid fk in tblorderitem table may contain incorrect value.
> > > > >
> > > > > Using below example as example, execute the following insert
> > > >statements:
> > > > >
> > > > > <--- cut here --->
> > > > > insert into qselOrder(ordDate, custId, ordCurTotal, prodName,
> > > >prodCurPrice,
> > > > > oitAmount)
> > > > > values(getdate(),1,2,'product name',4,5)
> > > > >
> > > > > insert into qselOrder(ordDate, custId, ordCurTotal, prodName,
> > > >prodCurPrice,
> > > > > oitAmount)
> > > > > select ordDate, custId, ordCurTotal, prodName, prodCurPrice,
> >oitAmount
> > > >from
> > > > > qselOrder
> > > > >
> > > > > insert into qselOrder(ordDate, custId, ordCurTotal, prodName,
> > > >prodCurPrice,
> > > > > oitAmount)
> > > > > select ordDate, custId, ordCurTotal, prodName, prodCurPrice,
> >oitAmount
> > > >from
> > > > > qselOrder
> > > > > <--- cut here --->
> > > > >
> > > > > assuming tblorder table is empty from very beginning, it will
> >insert
> > > >four
> > > > > records into tblorder table as expected and four records into
> > > >tblorderitems
> > > > > table as expected.  however, the fourth record in tblorder table
> >does
> > > >not
> > > > > have any related records in tblorderitems.
> > > > >
> > > > > One way around this problem is to load the values within the
> > > >"inserted"
> > > > > pseudo table into a table variable (assuming ss2k) or temporary
> >table
> > > >and
> > > > > loop through them using database cursor.  However, I am
> > > > > wondering
> >if
> > > >there
> > > > > is another way.
> > > > >
> > > > > sincerely,
> > > > > Billy
> > > > >
> > > >
> > > >_______________________________________________
> > > >dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com
> > > >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > > >http://www.databaseadvisors.com
> > > >
> > >
> > > _________________________________________________________________
> > > Add photos to your e-mail with MSN Premium. Get 2 months FREE*
> > >
> >http://join.msn.com/?pgmarket=en-ca&page=byoa/prem&xAPID=1994&DI=1034&S
> >U=http://hotmail.com/enca&HL=Market_MSNIS_Taglines
> > >
> > > _______________________________________________
> > > dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > > http://www.databaseadvisors.com
> > >
> >
> >_______________________________________________
> >dba-SQLServer mailing list
> >dba-SQLServer at databaseadvisors.com
> >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> >http://www.databaseadvisors.com
> >
>
>_________________________________________________________________
>MSN Premium helps eliminate e-mail viruses. Get 2 months FREE*
>http://join.msn.com/?pgmarket=en-ca&page=byoa/prem&xAPID=1994&DI=1034&SU
>=http://hotmail.com/enca&HL=Market_MSNIS_Taglines
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>

_________________________________________________________________
Powerful Parental Controls Let your child discover the best the Internet has 
to offer. 
http://join.msn.com/?pgmarket=en-ca&page=byoa/prem&xAPID=1994&DI=1034&SU=http://hotmail.com/enca&HL=Market_MSNIS_Taglines 
  Start enjoying all the benefits of MSN® Premium right now and get the 
first two months FREE*.

More information about the dba-SQLServer mailing list