Arthur Fuller
artful at rogers.com
Thu Jul 22 10:00:54 CDT 2004
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