Shamil Salakhetdinov
shamil at users.mns.ru
Fri Jul 16 15:23:32 CDT 2004
> If SQL Server is able to immitate this behavior (without
> using scope_identity(), of course :) ), then I am would
> like to know.
Billy,
I think it can't - sorry I can't help here.
Shamil
----- Original Message -----
From: "Billy Pang" <tuxedo_man at hotmail.com>
To: <dba-sqlserver at databaseadvisors.com>
Sent: Saturday, July 17, 2004 12:05 AM
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
>