Billy Pang
tuxedo_man at hotmail.com
Fri Jul 16 15:05:02 CDT 2004
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&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
>
_________________________________________________________________
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