Shamil Salakhetdinov
shamil at users.mns.ru
Fri Jul 16 13:22:25 CDT 2004
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 >