|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Can I do this?( RowNumber INT IDENTITY(1, 1) NOT NULL, BadgeNo CHAR(6) NOT NULL, LastName VARCHAR(35) NOT NULL, FirstName VARCHAR(25) NOT NULL, Street VARCHAR(125) NOT NULL, City VARCHAR(40) NOT NULL, State CHAR(2) NOT NULL, ZipCode CHAR(9) NOT NULL, HomePhone CHAR(10) NOT NULL ) DECLARE @deleted TABLE ( RowNumber INT IDENTITY(1, 1) NOT NULL, BadgeNo CHAR(6) NOT NULL, LastName VARCHAR(35) NOT NULL, FirstName VARCHAR(25) NOT NULL, Street VARCHAR(125) NOT NULL, City VARCHAR(40) NOT NULL, State CHAR(2) NOT NULL, ZipCode CHAR(9) NOT NULL, HomePhone CHAR(10) NOT NULL ) INSERT @deleted (BadgeNo, LastName, FirstName, Street, City, State, ZipCode, HomePhone) SELECT BadgeNo, LastName, FirstName, Street, City, State, ZipCode, HomePhone FROM deleted WITH(INDEX(0)) OPTION(MAXDOP 1) INSERT @inserted (BadgeNo, LastName, FirstName, Street, City, State, ZipCode, HomePhone) SELECT BadgeNo, LastName, FirstName, Street, City, State, ZipCode, HomePhone FROM deleted WITH(INDEX(0)) OPTION(MAXDOP 1) Will this reliably allow me to determine which changes occurred to which rows within the body of a trigger? The only thing I found that even touched on this issue in the entire Microsoft web site was a comparison between Oracle and SQL Server 2000 in which the trigger did a full outer join. But that doesn't deal with the possibility that a PK value could be swapped during the same update, which would lead to different results depending on whether cascading updates were enabled. Nor would it allow an instead of update trigger to function correctly, especially if there are tables referencing the table with the instead of trigger, since you can't enable cascading updates on a table with an instead of trigger. I found documentation that the insert operation is always serialized so that inserts match the order of the select statement; however, the select statement that produces the result set to be inserted can generate a parallel plan, hence OPTION(MAXDOP 1). WITH(INDEX(0)) forces a table scan, so I believe that what is inserted into the @deleted and @inserted table variables will always match the order of the inserted and deleted pseudotables. The question is: is the order of the inserted and deleted pseudotables undefined, or do they correspond? I've done some preliminary testing and so far the order has always been the same. The last thing that happens in an execution plan with an instead of trigger is a split operation, which I assume loads the deleted and inserted pseudotables with the old and new values respectively. Brian,
It's unclear to me what you are really asking with this question? >Will this reliably allow me to determine which changes occurred to which What changes are you referring to? Are you talking about which columns were >rows within the body of a trigger? updated or what? As for the order of selects you are never guaranteed an order unless you specify an ORDER BY clause. But that in no way implies that you can retrieve the rows out in an order they went in as. -- Show quoteAndrew J. Kelly SQL MVP "Brian Selzer" <br***@selzer-software.com> wrote in message news:e1PxMrNiFHA.1412@TK2MSFTNGP09.phx.gbl... > DECLARE @inserted TABLE > ( > RowNumber INT IDENTITY(1, 1) NOT NULL, > BadgeNo CHAR(6) NOT NULL, > LastName VARCHAR(35) NOT NULL, > FirstName VARCHAR(25) NOT NULL, > Street VARCHAR(125) NOT NULL, > City VARCHAR(40) NOT NULL, > State CHAR(2) NOT NULL, > ZipCode CHAR(9) NOT NULL, > HomePhone CHAR(10) NOT NULL > ) > DECLARE @deleted TABLE > ( > RowNumber INT IDENTITY(1, 1) NOT NULL, > BadgeNo CHAR(6) NOT NULL, > LastName VARCHAR(35) NOT NULL, > FirstName VARCHAR(25) NOT NULL, > Street VARCHAR(125) NOT NULL, > City VARCHAR(40) NOT NULL, > State CHAR(2) NOT NULL, > ZipCode CHAR(9) NOT NULL, > HomePhone CHAR(10) NOT NULL > ) > INSERT @deleted > (BadgeNo, LastName, FirstName, Street, City, State, ZipCode, HomePhone) > SELECT BadgeNo, LastName, FirstName, Street, City, State, ZipCode, > HomePhone > FROM deleted WITH(INDEX(0)) OPTION(MAXDOP 1) > INSERT @inserted > (BadgeNo, LastName, FirstName, Street, City, State, ZipCode, HomePhone) > SELECT BadgeNo, LastName, FirstName, Street, City, State, ZipCode, > HomePhone > FROM deleted WITH(INDEX(0)) OPTION(MAXDOP 1) > > Will this reliably allow me to determine which changes occurred to which > rows within the body of a trigger? The only thing I found that even > touched > on this issue in the entire Microsoft web site was a comparison between > Oracle and SQL Server 2000 in which the trigger did a full outer join. > But > that doesn't deal with the possibility that a PK value could be swapped > during the same update, which would lead to different results depending on > whether cascading updates were enabled. Nor would it allow an instead of > update trigger to function correctly, especially if there are tables > referencing the table with the instead of trigger, since you can't enable > cascading updates on a table with an instead of trigger. > > I found documentation that the insert operation is always serialized so > that > inserts match the order of the select statement; however, the select > statement that produces the result set to be inserted can generate a > parallel plan, hence OPTION(MAXDOP 1). WITH(INDEX(0)) forces a table > scan, > so I believe that what is inserted into the @deleted and @inserted table > variables will always match the order of the inserted and deleted > pseudotables. > > The question is: is the order of the inserted and deleted pseudotables > undefined, or do they correspond? I've done some preliminary testing and > so > far the order has always been the same. The last thing that happens in an > execution plan with an instead of trigger is a split operation, which I > assume loads the deleted and inserted pseudotables with the old and new > values respectively. > > Is the order of the inserted and deleted pseudotables undefined?
In an instead of trigger, the deleted pseudotable contains the old values, and the inserted pseudotable contains the new values. Assume that during the same update two or more primary key values were exchanged, for example: deleted inserted 2 8 5 5 8 2 In this example, PK 2 became PK 8 and PK 8 became PK 2. How can I determine that an update to the row that had PK 2 now has PK 8? If the order of the pseudotables do not correspond, then I'm forced to serialize the updates, that is, update one row at a time. Show quote "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message news:u3EUkZOiFHA.1464@TK2MSFTNGP14.phx.gbl... > Brian, > > It's unclear to me what you are really asking with this question? > > >Will this reliably allow me to determine which changes occurred to which > >rows within the body of a trigger? > > What changes are you referring to? Are you talking about which columns were > updated or what? As for the order of selects you are never guaranteed an > order unless you specify an ORDER BY clause. But that in no way implies > that you can retrieve the rows out in an order they went in as. > > -- > Andrew J. Kelly SQL MVP > > > "Brian Selzer" <br***@selzer-software.com> wrote in message > news:e1PxMrNiFHA.1412@TK2MSFTNGP09.phx.gbl... > > DECLARE @inserted TABLE > > ( > > RowNumber INT IDENTITY(1, 1) NOT NULL, > > BadgeNo CHAR(6) NOT NULL, > > LastName VARCHAR(35) NOT NULL, > > FirstName VARCHAR(25) NOT NULL, > > Street VARCHAR(125) NOT NULL, > > City VARCHAR(40) NOT NULL, > > State CHAR(2) NOT NULL, > > ZipCode CHAR(9) NOT NULL, > > HomePhone CHAR(10) NOT NULL > > ) > > DECLARE @deleted TABLE > > ( > > RowNumber INT IDENTITY(1, 1) NOT NULL, > > BadgeNo CHAR(6) NOT NULL, > > LastName VARCHAR(35) NOT NULL, > > FirstName VARCHAR(25) NOT NULL, > > Street VARCHAR(125) NOT NULL, > > City VARCHAR(40) NOT NULL, > > State CHAR(2) NOT NULL, > > ZipCode CHAR(9) NOT NULL, > > HomePhone CHAR(10) NOT NULL > > ) > > INSERT @deleted > > (BadgeNo, LastName, FirstName, Street, City, State, ZipCode, HomePhone) > > SELECT BadgeNo, LastName, FirstName, Street, City, State, ZipCode, > > HomePhone > > FROM deleted WITH(INDEX(0)) OPTION(MAXDOP 1) > > INSERT @inserted > > (BadgeNo, LastName, FirstName, Street, City, State, ZipCode, HomePhone) > > SELECT BadgeNo, LastName, FirstName, Street, City, State, ZipCode, > > HomePhone > > FROM deleted WITH(INDEX(0)) OPTION(MAXDOP 1) > > > > Will this reliably allow me to determine which changes occurred to which > > rows within the body of a trigger? The only thing I found that even > > touched > > on this issue in the entire Microsoft web site was a comparison between > > Oracle and SQL Server 2000 in which the trigger did a full outer join. > > But > > that doesn't deal with the possibility that a PK value could be swapped > > during the same update, which would lead to different results depending on > > whether cascading updates were enabled. Nor would it allow an instead of > > update trigger to function correctly, especially if there are tables > > referencing the table with the instead of trigger, since you can't enable > > cascading updates on a table with an instead of trigger. > > > > I found documentation that the insert operation is always serialized so > > that > > inserts match the order of the select statement; however, the select > > statement that produces the result set to be inserted can generate a > > parallel plan, hence OPTION(MAXDOP 1). WITH(INDEX(0)) forces a table > > scan, > > so I believe that what is inserted into the @deleted and @inserted table > > variables will always match the order of the inserted and deleted > > pseudotables. > > > > The question is: is the order of the inserted and deleted pseudotables > > undefined, or do they correspond? I've done some preliminary testing and > > so > > far the order has always been the same. The last thing that happens in an > > execution plan with an instead of trigger is a split operation, which I > > assume loads the deleted and inserted pseudotables with the old and new > > values respectively. > > > > > > > How can I determine that an update to the row that had PK 2 now has PK 8? Well, create an artificial/surrogate key to maintain the changes. In yourexample your PK's have changed so to identify the row that had PK 2 now has PK 8 try to join them by surrogate key As Andrew pointed out don't realy on INSERT statement's order . Only using ORDER BY clause will guarantee the sort of returing data Just my two cents Show quote "Brian Selzer" <br***@selzer-software.com> wrote in message news:eSjMmsQiFHA.2664@TK2MSFTNGP10.phx.gbl... > Is the order of the inserted and deleted pseudotables undefined? > > In an instead of trigger, the deleted pseudotable contains the old values, > and the inserted pseudotable contains the new values. Assume that during > the same update two or more primary key values were exchanged, for example: > > deleted inserted > 2 8 > 5 5 > 8 2 > > In this example, PK 2 became PK 8 and PK 8 became PK 2. > > How can I determine that an update to the row that had PK 2 now has PK 8? > > If the order of the pseudotables do not correspond, then I'm forced to > serialize the updates, that is, update one row at a time. > > > > "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message > news:u3EUkZOiFHA.1464@TK2MSFTNGP14.phx.gbl... > > Brian, > > > > It's unclear to me what you are really asking with this question? > > > > >Will this reliably allow me to determine which changes occurred to which > > >rows within the body of a trigger? > > > > What changes are you referring to? Are you talking about which columns > were > > updated or what? As for the order of selects you are never guaranteed an > > order unless you specify an ORDER BY clause. But that in no way implies > > that you can retrieve the rows out in an order they went in as. > > > > -- > > Andrew J. Kelly SQL MVP > > > > > > "Brian Selzer" <br***@selzer-software.com> wrote in message > > news:e1PxMrNiFHA.1412@TK2MSFTNGP09.phx.gbl... > > > DECLARE @inserted TABLE > > > ( > > > RowNumber INT IDENTITY(1, 1) NOT NULL, > > > BadgeNo CHAR(6) NOT NULL, > > > LastName VARCHAR(35) NOT NULL, > > > FirstName VARCHAR(25) NOT NULL, > > > Street VARCHAR(125) NOT NULL, > > > City VARCHAR(40) NOT NULL, > > > State CHAR(2) NOT NULL, > > > ZipCode CHAR(9) NOT NULL, > > > HomePhone CHAR(10) NOT NULL > > > ) > > > DECLARE @deleted TABLE > > > ( > > > RowNumber INT IDENTITY(1, 1) NOT NULL, > > > BadgeNo CHAR(6) NOT NULL, > > > LastName VARCHAR(35) NOT NULL, > > > FirstName VARCHAR(25) NOT NULL, > > > Street VARCHAR(125) NOT NULL, > > > City VARCHAR(40) NOT NULL, > > > State CHAR(2) NOT NULL, > > > ZipCode CHAR(9) NOT NULL, > > > HomePhone CHAR(10) NOT NULL > > > ) > > > INSERT @deleted > > > (BadgeNo, LastName, FirstName, Street, City, State, ZipCode, HomePhone) > > > SELECT BadgeNo, LastName, FirstName, Street, City, State, ZipCode, > > > HomePhone > > > FROM deleted WITH(INDEX(0)) OPTION(MAXDOP 1) > > > INSERT @inserted > > > (BadgeNo, LastName, FirstName, Street, City, State, ZipCode, HomePhone) > > > SELECT BadgeNo, LastName, FirstName, Street, City, State, ZipCode, > > > HomePhone > > > FROM deleted WITH(INDEX(0)) OPTION(MAXDOP 1) > > > > > > Will this reliably allow me to determine which changes occurred to which > > > rows within the body of a trigger? The only thing I found that even > > > touched > > > on this issue in the entire Microsoft web site was a comparison between > > > Oracle and SQL Server 2000 in which the trigger did a full outer join. > > > But > > > that doesn't deal with the possibility that a PK value could be swapped > > > during the same update, which would lead to different results depending > on > > > whether cascading updates were enabled. Nor would it allow an instead > of > > > update trigger to function correctly, especially if there are tables > > > referencing the table with the instead of trigger, since you can't > enable > > > cascading updates on a table with an instead of trigger. > > > > > > I found documentation that the insert operation is always serialized so > > > that > > > inserts match the order of the select statement; however, the select > > > statement that produces the result set to be inserted can generate a > > > parallel plan, hence OPTION(MAXDOP 1). WITH(INDEX(0)) forces a table > > > scan, > > > so I believe that what is inserted into the @deleted and @inserted table > > > variables will always match the order of the inserted and deleted > > > pseudotables. > > > > > > The question is: is the order of the inserted and deleted pseudotables > > > undefined, or do they correspond? I've done some preliminary testing > and > > > so > > > far the order has always been the same. The last thing that happens in > an > > > execution plan with an instead of trigger is a split operation, which I > > > assume loads the deleted and inserted pseudotables with the old and new > > > values respectively. > > > > > > > > > > > > If you are already planning to change your PK's then you are off to the
wrong path right from the start. If you know the PK will change then there is a chance it shouldn't be the PK. PK's should not change as a matter of normal business practices. In the real world there are times when a PK might change but that should be an exception. As such you should handle that with a carefully controlled script to update all the tables in the proper fashion. This is NOT the type of thing you should have coded in a trigger. The whole point of a PK is to be able to tell one row from another with no exceptions. And no you should not count on the physical placement of rows in any table including the Inserted and delete. You don't even know what order they actually will happen in a set based operation and shouldn't care if designed properly. -- Show quoteAndrew J. Kelly SQL MVP "Brian Selzer" <br***@selzer-software.com> wrote in message news:eSjMmsQiFHA.2664@TK2MSFTNGP10.phx.gbl... > Is the order of the inserted and deleted pseudotables undefined? > > In an instead of trigger, the deleted pseudotable contains the old values, > and the inserted pseudotable contains the new values. Assume that during > the same update two or more primary key values were exchanged, for > example: > > deleted inserted > 2 8 > 5 5 > 8 2 > > In this example, PK 2 became PK 8 and PK 8 became PK 2. > > How can I determine that an update to the row that had PK 2 now has PK 8? > > If the order of the pseudotables do not correspond, then I'm forced to > serialize the updates, that is, update one row at a time. > > > > "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message > news:u3EUkZOiFHA.1464@TK2MSFTNGP14.phx.gbl... >> Brian, >> >> It's unclear to me what you are really asking with this question? >> >> >Will this reliably allow me to determine which changes occurred to which >> >rows within the body of a trigger? >> >> What changes are you referring to? Are you talking about which columns > were >> updated or what? As for the order of selects you are never guaranteed an >> order unless you specify an ORDER BY clause. But that in no way implies >> that you can retrieve the rows out in an order they went in as. >> >> -- >> Andrew J. Kelly SQL MVP >> >> >> "Brian Selzer" <br***@selzer-software.com> wrote in message >> news:e1PxMrNiFHA.1412@TK2MSFTNGP09.phx.gbl... >> > DECLARE @inserted TABLE >> > ( >> > RowNumber INT IDENTITY(1, 1) NOT NULL, >> > BadgeNo CHAR(6) NOT NULL, >> > LastName VARCHAR(35) NOT NULL, >> > FirstName VARCHAR(25) NOT NULL, >> > Street VARCHAR(125) NOT NULL, >> > City VARCHAR(40) NOT NULL, >> > State CHAR(2) NOT NULL, >> > ZipCode CHAR(9) NOT NULL, >> > HomePhone CHAR(10) NOT NULL >> > ) >> > DECLARE @deleted TABLE >> > ( >> > RowNumber INT IDENTITY(1, 1) NOT NULL, >> > BadgeNo CHAR(6) NOT NULL, >> > LastName VARCHAR(35) NOT NULL, >> > FirstName VARCHAR(25) NOT NULL, >> > Street VARCHAR(125) NOT NULL, >> > City VARCHAR(40) NOT NULL, >> > State CHAR(2) NOT NULL, >> > ZipCode CHAR(9) NOT NULL, >> > HomePhone CHAR(10) NOT NULL >> > ) >> > INSERT @deleted >> > (BadgeNo, LastName, FirstName, Street, City, State, ZipCode, >> > HomePhone) >> > SELECT BadgeNo, LastName, FirstName, Street, City, State, ZipCode, >> > HomePhone >> > FROM deleted WITH(INDEX(0)) OPTION(MAXDOP 1) >> > INSERT @inserted >> > (BadgeNo, LastName, FirstName, Street, City, State, ZipCode, >> > HomePhone) >> > SELECT BadgeNo, LastName, FirstName, Street, City, State, ZipCode, >> > HomePhone >> > FROM deleted WITH(INDEX(0)) OPTION(MAXDOP 1) >> > >> > Will this reliably allow me to determine which changes occurred to >> > which >> > rows within the body of a trigger? The only thing I found that even >> > touched >> > on this issue in the entire Microsoft web site was a comparison between >> > Oracle and SQL Server 2000 in which the trigger did a full outer join. >> > But >> > that doesn't deal with the possibility that a PK value could be swapped >> > during the same update, which would lead to different results depending > on >> > whether cascading updates were enabled. Nor would it allow an instead > of >> > update trigger to function correctly, especially if there are tables >> > referencing the table with the instead of trigger, since you can't > enable >> > cascading updates on a table with an instead of trigger. >> > >> > I found documentation that the insert operation is always serialized so >> > that >> > inserts match the order of the select statement; however, the select >> > statement that produces the result set to be inserted can generate a >> > parallel plan, hence OPTION(MAXDOP 1). WITH(INDEX(0)) forces a table >> > scan, >> > so I believe that what is inserted into the @deleted and @inserted >> > table >> > variables will always match the order of the inserted and deleted >> > pseudotables. >> > >> > The question is: is the order of the inserted and deleted pseudotables >> > undefined, or do they correspond? I've done some preliminary testing > and >> > so >> > far the order has always been the same. The last thing that happens in > an >> > execution plan with an instead of trigger is a split operation, which I >> > assume loads the deleted and inserted pseudotables with the old and new >> > values respectively. >> > >> > >> >> > >
Show quote
> If you are already planning to change your PK's then you are off to the Unfortunately, the databases already exist. Applications already exist. I> wrong path right from the start. If you know the PK will change then there > is a chance it shouldn't be the PK. PK's should not change as a matter of > normal business practices. In the real world there are times when a PK > might change but that should be an exception. As such you should handle > that with a carefully controlled script to update all the tables in the > proper fashion. This is NOT the type of thing you should have coded in a > trigger. The whole point of a PK is to be able to tell one row from another > with no exceptions. And no you should not count on the physical placement of > rows in any table including the Inserted and delete. You don't even know > what order they actually will happen in a set based operation and shouldn't > care if designed properly. need to detect changes to several tables in one database in order to feed those changes into another database. If I had designed the database, the tables would have surrogates and I wouldn't have this issue. I cannot add surrogates, because all of the stored procedures, triggers and client applications would have to be rewritten, and because I don't currently have access to the source for some of the client applications. The client applications perform updates one row at a time, so I can definitely capture changes coming from a client app, but there are quite a few stored procedures that also update the tables. Each of these will have to be rewritten to serialize the updates. Aside from the obvious extra labor required to rewrite all of the stored procedures and the maintenance issues that arise whenever there is a new release, my concern is that that serialization will severely impact the performance of the existing applications. I've heard mention of log readers, but I am unfamiliar with their use or cost. It may be that a log reader is what I need in this situation. If you know anything about them, could you please enlighten me? Show quote "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message news:u0duRcTiFHA.3288@TK2MSFTNGP09.phx.gbl... > If you are already planning to change your PK's then you are off to the > wrong path right from the start. If you know the PK will change then there > is a chance it shouldn't be the PK. PK's should not change as a matter of > normal business practices. In the real world there are times when a PK > might change but that should be an exception. As such you should handle > that with a carefully controlled script to update all the tables in the > proper fashion. This is NOT the type of thing you should have coded in a > trigger. The whole point of a PK is to be able to tell one row from another > with no exceptions. And no you should not count on the physical placement of > rows in any table including the Inserted and delete. You don't even know > what order they actually will happen in a set based operation and shouldn't > care if designed properly. > > -- > Andrew J. Kelly SQL MVP > > > "Brian Selzer" <br***@selzer-software.com> wrote in message > news:eSjMmsQiFHA.2664@TK2MSFTNGP10.phx.gbl... > > Is the order of the inserted and deleted pseudotables undefined? > > > > In an instead of trigger, the deleted pseudotable contains the old values, > > and the inserted pseudotable contains the new values. Assume that during > > the same update two or more primary key values were exchanged, for > > example: > > > > deleted inserted > > 2 8 > > 5 5 > > 8 2 > > > > In this example, PK 2 became PK 8 and PK 8 became PK 2. > > > > How can I determine that an update to the row that had PK 2 now has PK 8? > > > > If the order of the pseudotables do not correspond, then I'm forced to > > serialize the updates, that is, update one row at a time. > > > > > > > > "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message > > news:u3EUkZOiFHA.1464@TK2MSFTNGP14.phx.gbl... > >> Brian, > >> > >> It's unclear to me what you are really asking with this question? > >> > >> >Will this reliably allow me to determine which changes occurred to which > >> >rows within the body of a trigger? > >> > >> What changes are you referring to? Are you talking about which columns > > were > >> updated or what? As for the order of selects you are never guaranteed an > >> order unless you specify an ORDER BY clause. But that in no way implies > >> that you can retrieve the rows out in an order they went in as. > >> > >> -- > >> Andrew J. Kelly SQL MVP > >> > >> > >> "Brian Selzer" <br***@selzer-software.com> wrote in message > >> news:e1PxMrNiFHA.1412@TK2MSFTNGP09.phx.gbl... > >> > DECLARE @inserted TABLE > >> > ( > >> > RowNumber INT IDENTITY(1, 1) NOT NULL, > >> > BadgeNo CHAR(6) NOT NULL, > >> > LastName VARCHAR(35) NOT NULL, > >> > FirstName VARCHAR(25) NOT NULL, > >> > Street VARCHAR(125) NOT NULL, > >> > City VARCHAR(40) NOT NULL, > >> > State CHAR(2) NOT NULL, > >> > ZipCode CHAR(9) NOT NULL, > >> > HomePhone CHAR(10) NOT NULL > >> > ) > >> > DECLARE @deleted TABLE > >> > ( > >> > RowNumber INT IDENTITY(1, 1) NOT NULL, > >> > BadgeNo CHAR(6) NOT NULL, > >> > LastName VARCHAR(35) NOT NULL, > >> > FirstName VARCHAR(25) NOT NULL, > >> > Street VARCHAR(125) NOT NULL, > >> > City VARCHAR(40) NOT NULL, > >> > State CHAR(2) NOT NULL, > >> > ZipCode CHAR(9) NOT NULL, > >> > HomePhone CHAR(10) NOT NULL > >> > ) > >> > INSERT @deleted > >> > (BadgeNo, LastName, FirstName, Street, City, State, ZipCode, > >> > HomePhone) > >> > SELECT BadgeNo, LastName, FirstName, Street, City, State, ZipCode, > >> > HomePhone > >> > FROM deleted WITH(INDEX(0)) OPTION(MAXDOP 1) > >> > INSERT @inserted > >> > (BadgeNo, LastName, FirstName, Street, City, State, ZipCode, > >> > HomePhone) > >> > SELECT BadgeNo, LastName, FirstName, Street, City, State, ZipCode, > >> > HomePhone > >> > FROM deleted WITH(INDEX(0)) OPTION(MAXDOP 1) > >> > > >> > Will this reliably allow me to determine which changes occurred to > >> > which > >> > rows within the body of a trigger? The only thing I found that even > >> > touched > >> > on this issue in the entire Microsoft web site was a comparison between > >> > Oracle and SQL Server 2000 in which the trigger did a full outer join. > >> > But > >> > that doesn't deal with the possibility that a PK value could be swapped > >> > during the same update, which would lead to different results depending > > on > >> > whether cascading updates were enabled. Nor would it allow an instead > > of > >> > update trigger to function correctly, especially if there are tables > >> > referencing the table with the instead of trigger, since you can't > > enable > >> > cascading updates on a table with an instead of trigger. > >> > > >> > I found documentation that the insert operation is always serialized so > >> > that > >> > inserts match the order of the select statement; however, the select > >> > statement that produces the result set to be inserted can generate a > >> > parallel plan, hence OPTION(MAXDOP 1). WITH(INDEX(0)) forces a table > >> > scan, > >> > so I believe that what is inserted into the @deleted and @inserted > >> > table > >> > variables will always match the order of the inserted and deleted > >> > pseudotables. > >> > > >> > The question is: is the order of the inserted and deleted pseudotables > >> > undefined, or do they correspond? I've done some preliminary testing > > and > >> > so > >> > far the order has always been the same. The last thing that happens in > > an > >> > execution plan with an instead of trigger is a split operation, which I > >> > assume loads the deleted and inserted pseudotables with the old and new > >> > values respectively. > >> > > >> > > >> > >> > > > > > > Brian,
What is it you need to do when they change the PK? Is it to update the children FK's? If so then why not turn on Cascade RI with Update? Then it will take care of it for you automatically. -- Show quoteAndrew J. Kelly SQL MVP "Brian Selzer" <br***@selzer-software.com> wrote in message news:%23qjgunUiFHA.3656@TK2MSFTNGP09.phx.gbl... >> If you are already planning to change your PK's then you are off to the >> wrong path right from the start. If you know the PK will change then > there >> is a chance it shouldn't be the PK. PK's should not change as a matter >> of >> normal business practices. In the real world there are times when a PK >> might change but that should be an exception. As such you should handle >> that with a carefully controlled script to update all the tables in the >> proper fashion. This is NOT the type of thing you should have coded in a >> trigger. The whole point of a PK is to be able to tell one row from > another >> with no exceptions. And no you should not count on the physical placement > of >> rows in any table including the Inserted and delete. You don't even know >> what order they actually will happen in a set based operation and > shouldn't >> care if designed properly. > > Unfortunately, the databases already exist. Applications already exist. > I > need to detect changes to several tables in one database in order to feed > those changes into another database. If I had designed the database, the > tables would have surrogates and I wouldn't have this issue. I cannot add > surrogates, because all of the stored procedures, triggers and client > applications would have to be rewritten, and because I don't currently > have > access to the source for some of the client applications. The client > applications perform updates one row at a time, so I can definitely > capture > changes coming from a client app, but there are quite a few stored > procedures that also update the tables. Each of these will have to be > rewritten to serialize the updates. Aside from the obvious extra labor > required to rewrite all of the stored procedures and the maintenance > issues > that arise whenever there is a new release, my concern is that that > serialization will severely impact the performance of the existing > applications. > > I've heard mention of log readers, but I am unfamiliar with their use or > cost. It may be that a log reader is what I need in this situation. If > you > know anything about them, could you please enlighten me? > > "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message > news:u0duRcTiFHA.3288@TK2MSFTNGP09.phx.gbl... >> If you are already planning to change your PK's then you are off to the >> wrong path right from the start. If you know the PK will change then > there >> is a chance it shouldn't be the PK. PK's should not change as a matter >> of >> normal business practices. In the real world there are times when a PK >> might change but that should be an exception. As such you should handle >> that with a carefully controlled script to update all the tables in the >> proper fashion. This is NOT the type of thing you should have coded in a >> trigger. The whole point of a PK is to be able to tell one row from > another >> with no exceptions. And no you should not count on the physical placement > of >> rows in any table including the Inserted and delete. You don't even know >> what order they actually will happen in a set based operation and > shouldn't >> care if designed properly. >> >> -- >> Andrew J. Kelly SQL MVP >> >> >> "Brian Selzer" <br***@selzer-software.com> wrote in message >> news:eSjMmsQiFHA.2664@TK2MSFTNGP10.phx.gbl... >> > Is the order of the inserted and deleted pseudotables undefined? >> > >> > In an instead of trigger, the deleted pseudotable contains the old > values, >> > and the inserted pseudotable contains the new values. Assume that > during >> > the same update two or more primary key values were exchanged, for >> > example: >> > >> > deleted inserted >> > 2 8 >> > 5 5 >> > 8 2 >> > >> > In this example, PK 2 became PK 8 and PK 8 became PK 2. >> > >> > How can I determine that an update to the row that had PK 2 now has PK > 8? >> > >> > If the order of the pseudotables do not correspond, then I'm forced to >> > serialize the updates, that is, update one row at a time. >> > >> > >> > >> > "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message >> > news:u3EUkZOiFHA.1464@TK2MSFTNGP14.phx.gbl... >> >> Brian, >> >> >> >> It's unclear to me what you are really asking with this question? >> >> >> >> >Will this reliably allow me to determine which changes occurred to > which >> >> >rows within the body of a trigger? >> >> >> >> What changes are you referring to? Are you talking about which >> >> columns >> > were >> >> updated or what? As for the order of selects you are never guaranteed > an >> >> order unless you specify an ORDER BY clause. But that in no way > implies >> >> that you can retrieve the rows out in an order they went in as. >> >> >> >> -- >> >> Andrew J. Kelly SQL MVP >> >> >> >> >> >> "Brian Selzer" <br***@selzer-software.com> wrote in message >> >> news:e1PxMrNiFHA.1412@TK2MSFTNGP09.phx.gbl... >> >> > DECLARE @inserted TABLE >> >> > ( >> >> > RowNumber INT IDENTITY(1, 1) NOT NULL, >> >> > BadgeNo CHAR(6) NOT NULL, >> >> > LastName VARCHAR(35) NOT NULL, >> >> > FirstName VARCHAR(25) NOT NULL, >> >> > Street VARCHAR(125) NOT NULL, >> >> > City VARCHAR(40) NOT NULL, >> >> > State CHAR(2) NOT NULL, >> >> > ZipCode CHAR(9) NOT NULL, >> >> > HomePhone CHAR(10) NOT NULL >> >> > ) >> >> > DECLARE @deleted TABLE >> >> > ( >> >> > RowNumber INT IDENTITY(1, 1) NOT NULL, >> >> > BadgeNo CHAR(6) NOT NULL, >> >> > LastName VARCHAR(35) NOT NULL, >> >> > FirstName VARCHAR(25) NOT NULL, >> >> > Street VARCHAR(125) NOT NULL, >> >> > City VARCHAR(40) NOT NULL, >> >> > State CHAR(2) NOT NULL, >> >> > ZipCode CHAR(9) NOT NULL, >> >> > HomePhone CHAR(10) NOT NULL >> >> > ) >> >> > INSERT @deleted >> >> > (BadgeNo, LastName, FirstName, Street, City, State, ZipCode, >> >> > HomePhone) >> >> > SELECT BadgeNo, LastName, FirstName, Street, City, State, ZipCode, >> >> > HomePhone >> >> > FROM deleted WITH(INDEX(0)) OPTION(MAXDOP 1) >> >> > INSERT @inserted >> >> > (BadgeNo, LastName, FirstName, Street, City, State, ZipCode, >> >> > HomePhone) >> >> > SELECT BadgeNo, LastName, FirstName, Street, City, State, ZipCode, >> >> > HomePhone >> >> > FROM deleted WITH(INDEX(0)) OPTION(MAXDOP 1) >> >> > >> >> > Will this reliably allow me to determine which changes occurred to >> >> > which >> >> > rows within the body of a trigger? The only thing I found that even >> >> > touched >> >> > on this issue in the entire Microsoft web site was a comparison > between >> >> > Oracle and SQL Server 2000 in which the trigger did a full outer > join. >> >> > But >> >> > that doesn't deal with the possibility that a PK value could be > swapped >> >> > during the same update, which would lead to different results > depending >> > on >> >> > whether cascading updates were enabled. Nor would it allow an > instead >> > of >> >> > update trigger to function correctly, especially if there are tables >> >> > referencing the table with the instead of trigger, since you can't >> > enable >> >> > cascading updates on a table with an instead of trigger. >> >> > >> >> > I found documentation that the insert operation is always serialized > so >> >> > that >> >> > inserts match the order of the select statement; however, the select >> >> > statement that produces the result set to be inserted can generate a >> >> > parallel plan, hence OPTION(MAXDOP 1). WITH(INDEX(0)) forces a >> >> > table >> >> > scan, >> >> > so I believe that what is inserted into the @deleted and @inserted >> >> > table >> >> > variables will always match the order of the inserted and deleted >> >> > pseudotables. >> >> > >> >> > The question is: is the order of the inserted and deleted > pseudotables >> >> > undefined, or do they correspond? I've done some preliminary >> >> > testing >> > and >> >> > so >> >> > far the order has always been the same. The last thing that happens > in >> > an >> >> > execution plan with an instead of trigger is a split operation, >> >> > which > I >> >> > assume loads the deleted and inserted pseudotables with the old and > new >> >> > values respectively. >> >> > >> >> > >> >> >> >> >> > >> > >> >> > > Brian,
The inserted/deleted pseudotables hold the changes of the transaction. As you know, a transaction is atomic, and thus should be treated as one set operation. It makes no sense to dissect it. But let's take you example. Let's suppose you have figured out in your example that the row with PK=2 was changed to PK=8 and that the row with PK=8 was changed to PK=2. If you have (unique) constraints on your destination table, then how are you going to process that data one row at a time without causing a constraint violation? Third argument: why is the relation between individual new and old rows important? Assume this was the situation before: INSERT INTO MyTable (PK, Col2, Col3) VALUES (2,'a','b') INSERT INTO MyTable (PK, Col2, Col3) VALUES (5,'c','d') INSERT INTO MyTable (PK, Col2, Col3) VALUES (8,'e','f') And this is the situation after: ( (2,'a','f') , (5,'c','d') , (8,'e','b') ) Then how would you know whether the statement that caused this was UPDATE MyTable SET PK =CASE PK WHEN 2 THEN 8 WHEN 8 THEN 2 ELSE PK END , Col2=CASE Col2 WHEN 'a' THEN 'e' WHEN 'e' THEN 'a' ELSE Col2 END or UPDATE MyTable SET Col3=CASE Col3 WHEN 'b' THEN 'f' WHEN 'f' THEN 'b' ELSE Col3 END And why would it be important to know which statement it was? The result is exactly the same. What do you think will go wrong if you match the inserted to the deleted rows in any arbitrary order? As long as you process all the rows of inserted and deleted you will be fine, because that will get the correct new state. If you had no constraints on your destination table, you could simple delete all rows that were in the deleted pseudotable and insert all rows that were in the inserted pseudotable, and you would have the exact same data set in both tables. My 5 cents, Gert-Jan Brian Selzer wrote: Show quote > > Is the order of the inserted and deleted pseudotables undefined? > > In an instead of trigger, the deleted pseudotable contains the old values, > and the inserted pseudotable contains the new values. Assume that during > the same update two or more primary key values were exchanged, for example: > > deleted inserted > 2 8 > 5 5 > 8 2 > > In this example, PK 2 became PK 8 and PK 8 became PK 2. > > How can I determine that an update to the row that had PK 2 now has PK 8? > > If the order of the pseudotables do not correspond, then I'm forced to > serialize the updates, that is, update one row at a time. > > "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message > news:u3EUkZOiFHA.1464@TK2MSFTNGP14.phx.gbl... > > Brian, > > > > It's unclear to me what you are really asking with this question? > > > > >Will this reliably allow me to determine which changes occurred to which > > >rows within the body of a trigger? > > > > What changes are you referring to? Are you talking about which columns > were > > updated or what? As for the order of selects you are never guaranteed an > > order unless you specify an ORDER BY clause. But that in no way implies > > that you can retrieve the rows out in an order they went in as. > > > > -- > > Andrew J. Kelly SQL MVP > > > > > > "Brian Selzer" <br***@selzer-software.com> wrote in message > > news:e1PxMrNiFHA.1412@TK2MSFTNGP09.phx.gbl... > > > DECLARE @inserted TABLE > > > ( > > > RowNumber INT IDENTITY(1, 1) NOT NULL, > > > BadgeNo CHAR(6) NOT NULL, > > > LastName VARCHAR(35) NOT NULL, > > > FirstName VARCHAR(25) NOT NULL, > > > Street VARCHAR(125) NOT NULL, > > > City VARCHAR(40) NOT NULL, > > > State CHAR(2) NOT NULL, > > > ZipCode CHAR(9) NOT NULL, > > > HomePhone CHAR(10) NOT NULL > > > ) > > > DECLARE @deleted TABLE > > > ( > > > RowNumber INT IDENTITY(1, 1) NOT NULL, > > > BadgeNo CHAR(6) NOT NULL, > > > LastName VARCHAR(35) NOT NULL, > > > FirstName VARCHAR(25) NOT NULL, > > > Street VARCHAR(125) NOT NULL, > > > City VARCHAR(40) NOT NULL, > > > State CHAR(2) NOT NULL, > > > ZipCode CHAR(9) NOT NULL, > > > HomePhone CHAR(10) NOT NULL > > > ) > > > INSERT @deleted > > > (BadgeNo, LastName, FirstName, Street, City, State, ZipCode, HomePhone) > > > SELECT BadgeNo, LastName, FirstName, Street, City, State, ZipCode, > > > HomePhone > > > FROM deleted WITH(INDEX(0)) OPTION(MAXDOP 1) > > > INSERT @inserted > > > (BadgeNo, LastName, FirstName, Street, City, State, ZipCode, HomePhone) > > > SELECT BadgeNo, LastName, FirstName, Street, City, State, ZipCode, > > > HomePhone > > > FROM deleted WITH(INDEX(0)) OPTION(MAXDOP 1) > > > > > > Will this reliably allow me to determine which changes occurred to which > > > rows within the body of a trigger? The only thing I found that even > > > touched > > > on this issue in the entire Microsoft web site was a comparison between > > > Oracle and SQL Server 2000 in which the trigger did a full outer join. > > > But > > > that doesn't deal with the possibility that a PK value could be swapped > > > during the same update, which would lead to different results depending > on > > > whether cascading updates were enabled. Nor would it allow an instead > of > > > update trigger to function correctly, especially if there are tables > > > referencing the table with the instead of trigger, since you can't > enable > > > cascading updates on a table with an instead of trigger. > > > > > > I found documentation that the insert operation is always serialized so > > > that > > > inserts match the order of the select statement; however, the select > > > statement that produces the result set to be inserted can generate a > > > parallel plan, hence OPTION(MAXDOP 1). WITH(INDEX(0)) forces a table > > > scan, > > > so I believe that what is inserted into the @deleted and @inserted table > > > variables will always match the order of the inserted and deleted > > > pseudotables. > > > > > > The question is: is the order of the inserted and deleted pseudotables > > > undefined, or do they correspond? I've done some preliminary testing > and > > > so > > > far the order has always been the same. The last thing that happens in > an > > > execution plan with an instead of trigger is a split operation, which I > > > assume loads the deleted and inserted pseudotables with the old and new > > > values respectively. > > > > > > > > > > >> The question is: is the order of the inserted and deleted pseudotables undefined, or do they correspond? << Jesus Christ!! Get a book on RDBMS Basics!! Tables have no ordering bydefinition! NEVER write SQL that assumes a particular PHYSICAL storage method. You are back to magnetic tape files in your head. Your other fundamental flaw is using proprietary "pseudo_ables" instead of Standard SQL derived tables, CTEs and VIEWs. So, what you're saying is that triggered actions should not be a part of an
RDBMS? How do you specify transition constraints. How do you specify integrity constraints (other than foreign key constraints) that involve more than one table or more than one row in a table. Triggers are an integral part of an RDBMS. The problem here involves only supporting set-based triggering actions. Because you don't appear to understand how a SQL Server trigger operates, I will now enlighten you. The deleted pseudotable in an update trigger contain the old values and the inserted pseudotable contains the new values for each row that is to be commited by a set-based update statement. (In case you didn't know, or can't comprehend this concept, in order to specify a transition constraint, you must know both the old and new values for each row.) The problem is that because the inserted and deleted pseudotables appear to be and can be operated on as tables, if more than one row is updated by an update statement, there is no way to reliably determine what the old value was and what the new value is on any particular row that was updated because the primary key value may have changed. Note that this problem only exists in the absence of surrogate keys, which, because they're immutable, provide the necessary linkage. Because SQL Server doesn't have an atomic, or for each row trigger, I was looking for a way to work around this limitation. I can't justify rewriting a ton of client applications for which I don't have source, nor can I justify rewriting a bunch of stored procedures that are working correctly. This is the real world, not the utopian, fairy-tale land of academia. By the way, I would prefer if you would refrain from taking the name of my Lord in vain. Thank you! "--CELKO--" <jcelko***@earthlink.net> wrote in message undefined, or do they correspond? <<news:1121472924.797830.75150@z14g2000cwz.googlegroups.com... > >> The question is: is the order of the inserted and deleted pseudotables Show quote > > Jesus Christ!! Get a book on RDBMS Basics!! Tables have no ordering by > definition! NEVER write SQL that assumes a particular PHYSICAL storage > method. You are back to magnetic tape files in your head. > > Your other fundamental flaw is using proprietary "pseudo_ables" instead > of Standard SQL derived tables, CTEs and VIEWs. > Brian,
What do you mean by "which rows" when you ask "Will this reliably allow me to determine which changes occurred to *which rows* within the body of a trigger?" Unfortunately, given the result of an update that might have changed a key column value, it is impossible to know "which row" it was before the update. Without an immutable key (and immutability is an independent property from surrogacy), the question of "which old row changed to which new row" is not well defined, because "which row" is not defined. What do you mean by "which row" if there is no permanent way to identify a row? If "which" has no constant meaning, then your question has no answer. If you have business rules that say what kinds of changes are allowed, you need either to allow only one-at-a-time changes (if that is how the rules are expressed), or you need to be sure the rules govern multi-row changes in some way. If there are legal and illegal ways to get from here to there, you can't know whether the law was broken if someone got from here to there. The problem you set forth is like this. Maria steals Boban's favorite puzzle (http://www.corwin.ca/gridlock/) and runs into her room with it. A few minutes later, she gives it back to Boban, solved. Boban thinks she cheated and just picked up the blue block instead of sliding the blocks according to the rules. Unfortunately, he will never know, because it's possible to get the puzzle into the solved state in many ways, some of them following the rules, and some of them not following the rules. Only if the puzzle records the actual sequence of moves in some way would it be possible to find out whether Maria cheated or not. Maria knows whether she cheated, but she isn't telling, and she doesn't have to. (In fact, the puzzle she gave Boban may not even be the same puzzle she stole from him!) If you want to be sure no one is cheating, watch their every step. Suppose you have a one-column table T(OnlyCol) that contains three rows, and the OnlyCol values are 3, 17, and 8 (or 17, 8, and 3, or whatever, since order doesn't matter). An update is performed, and afterwords the table contains the values 3, 4, and 5. You can certainly ask "What was the row with 17 changed to?" but there is no answer, because "the row with 17" only identifies a "before" row, and in the "after" picture, there is no way to identify "the row that used to have 17 in it". Many different updates could have yielded the result described on the three-row table, and perhaps some of them violate your business rules and others don't. update T set OnlyCol = case OnlyCol when 17 then 4 when 8 then 5 else OnlyCol end or update T set OnlyCol = case OnlyCol when 3 then 4 when 17 then 3 when 8 then 5 end are two possibilities. Even if the query text is known, SQL Server could do all kinds of things to effect the update. All it must do is make the operation atomic, and provide required information correctly, such as the inserted/deleted tables, the logs, and so on. No matter how it effects the update, @@rowcount will be 3, the inserted and deleted tables will each contain three rows (with the before and after sets of values), and the log will contain enough information to reconstruct the before and after states of the table. Beyond that, while the question "What was the 17 changed to?" might have meaning to a "fly on the disk," it could have one answer one day, another answer another day, and no answer a third day, even if the query text were the first choice above each time. On Monday, the processor could execute the query by truncating the table and then inserting 3, 4, 5 in that order (what is the answer to the question in that case?). On Tuesday, it could change wherever the 17 is stored to 5, the data where the 3 is stored to 4, and the 8 to a 3, even though the query appears to ask for a different update. And on Wednesday, the processor might change the 17 to 4, the 8 to 5, and leave the 3 untouched, though still providing 3-row inserted and deleted tables and a rowcount of 3. On any of these days, the location of the physical data might move from one place to another (this could happen when there is no query, even). In fact, the answer might be "The 17 was not changed" Maybe 3 new rows were added to the table, and the existing rows were marked invalid. Not many people stop to ask questions like "What do you mean by 'which row,' but they are important questions. Steve Kass Drew University Brian Selzer wrote: Show quote > DECLARE @inserted TABLE > ( > RowNumber INT IDENTITY(1, 1) NOT NULL, > BadgeNo CHAR(6) NOT NULL, > LastName VARCHAR(35) NOT NULL, > FirstName VARCHAR(25) NOT NULL, > Street VARCHAR(125) NOT NULL, > City VARCHAR(40) NOT NULL, > State CHAR(2) NOT NULL, > ZipCode CHAR(9) NOT NULL, > HomePhone CHAR(10) NOT NULL > ) > DECLARE @deleted TABLE > ( > RowNumber INT IDENTITY(1, 1) NOT NULL, > BadgeNo CHAR(6) NOT NULL, > LastName VARCHAR(35) NOT NULL, > FirstName VARCHAR(25) NOT NULL, > Street VARCHAR(125) NOT NULL, > City VARCHAR(40) NOT NULL, > State CHAR(2) NOT NULL, > ZipCode CHAR(9) NOT NULL, > HomePhone CHAR(10) NOT NULL > ) > INSERT @deleted > (BadgeNo, LastName, FirstName, Street, City, State, ZipCode, HomePhone) > SELECT BadgeNo, LastName, FirstName, Street, City, State, ZipCode, >HomePhone > FROM deleted WITH(INDEX(0)) OPTION(MAXDOP 1) > INSERT @inserted > (BadgeNo, LastName, FirstName, Street, City, State, ZipCode, HomePhone) > SELECT BadgeNo, LastName, FirstName, Street, City, State, ZipCode, >HomePhone > FROM deleted WITH(INDEX(0)) OPTION(MAXDOP 1) > >Will this reliably allow me to determine which changes occurred to which >rows within the body of a trigger? The only thing I found that even touched >on this issue in the entire Microsoft web site was a comparison between >Oracle and SQL Server 2000 in which the trigger did a full outer join. But >that doesn't deal with the possibility that a PK value could be swapped >during the same update, which would lead to different results depending on >whether cascading updates were enabled. Nor would it allow an instead of >update trigger to function correctly, especially if there are tables >referencing the table with the instead of trigger, since you can't enable >cascading updates on a table with an instead of trigger. > >I found documentation that the insert operation is always serialized so that >inserts match the order of the select statement; however, the select >statement that produces the result set to be inserted can generate a >parallel plan, hence OPTION(MAXDOP 1). WITH(INDEX(0)) forces a table scan, >so I believe that what is inserted into the @deleted and @inserted table >variables will always match the order of the inserted and deleted >pseudotables. > >The question is: is the order of the inserted and deleted pseudotables >undefined, or do they correspond? I've done some preliminary testing and so >far the order has always been the same. The last thing that happens in an >execution plan with an instead of trigger is a split operation, which I >assume loads the deleted and inserted pseudotables with the old and new >values respectively. > > > > I was hoping for a way to get around SQL Server's set-based only trigger
mechanism. Absent surrogates, I'm forced to serialize the updates to any table that has an update trigger. This is a serious limitation of SQL Server. The last operation performed by the query optimizer is a split operation. I assume that to be a split of the set of updates into corresponding sets of deletes and inserts that are to be inserted into the deleted and inserted pseudotables respectively. Both tables have the same heading and are uniform in length, so the heap structure generated by an insert would be identical. The only problem I can see is if the number of rows exceed the size of an extent, because a heap is scanned in the order an extent falls within a database file. I was also hoping that because the inserted and deleted pseudotables are not "really" tables that a workaround would be possible. By the way, surrogates imply immutability. A surrogate represents only the existence of an entity. An entity can be created or destroyed, thus coming into existence or becoming nonexistent. The value of a surrogate serves only to distinguish one existing entity from another. Once assigned during the creation of an entity, that value cannot change for as long as the entity exists. A change to that value would indicate that the entity no longer exists. Show quote "Steve Kass" <sk***@drew.edu> wrote in message news:uJbXjwbiFHA.2916@TK2MSFTNGP14.phx.gbl... > Brian, > > What do you mean by "which rows" when you ask "Will this reliably > allow me to determine which changes occurred to *which rows* > within the body of a trigger?" Unfortunately, given the result of > an update that might have changed a key column value, it is > impossible to know "which row" it was before the update. > > Without an immutable key (and immutability is an independent property > from surrogacy), the question of "which old row changed to which new > row" is not well defined, because "which row" is not defined. What > do you mean by "which row" if there is no permanent way to identify > a row? If "which" has no constant meaning, then your question has > no answer. > > If you have business rules that say what kinds of changes are allowed, > you need either to allow only one-at-a-time changes (if that is how the > rules are expressed), or you need to be sure the rules govern multi-row > changes in some way. If there are legal and illegal ways to get from > here to there, you can't know whether the law was broken if someone > got from here to there. > > The problem you set forth is like this. Maria steals Boban's > favorite puzzle (http://www.corwin.ca/gridlock/) and runs into her > room with it. A few minutes later, she gives it back to Boban, solved. > Boban thinks she cheated and just picked up the blue block instead of > sliding the blocks according to the rules. Unfortunately, he will never > know, because it's possible to get the puzzle into the solved state in > many ways, some of them following the rules, and some of them not > following the rules. Only if the puzzle records the actual sequence of > moves in some way would it be possible to find out whether Maria > cheated or not. Maria knows whether she cheated, but she isn't > telling, and she doesn't have to. (In fact, the puzzle she gave Boban > may not even be the same puzzle she stole from him!) > > If you want to be sure no one is cheating, watch their every step. > > Suppose you have a one-column table T(OnlyCol) that contains three rows, > and the OnlyCol values are 3, 17, and 8 (or 17, 8, and 3, or whatever, since > order doesn't matter). An update is performed, and afterwords the table > contains > the values 3, 4, and 5. > > You can certainly ask "What was the row with 17 changed to?" but > there is no answer, because "the row with 17" only identifies a > "before" row, and in the "after" picture, there is no way to identify > "the row that used to have 17 in it". > > Many different updates could have yielded the result described on > the three-row table, and perhaps some of them violate your > business rules and others don't. > > update T set > OnlyCol = case OnlyCol > when 17 then 4 > when 8 then 5 > else OnlyCol end > > or > > update T set > OnlyCol = case OnlyCol > when 3 then 4 > when 17 then 3 > when 8 then 5 > end > > are two possibilities. > > Even if the query text is known, SQL Server could do all kinds of > things to effect the update. All it must do is make the operation > atomic, and provide required information correctly, such as the > inserted/deleted tables, the logs, and so on. No matter how > it effects the update, @@rowcount will be 3, the inserted and > deleted tables will each contain three rows (with the before > and after sets of values), and the log will contain enough information > to reconstruct the before and after states of the table. > > Beyond that, while the question "What was the 17 changed to?" might > have meaning to a "fly on the disk," it could have one answer one > day, another answer another day, and no answer a third day, even if the > query text were the first choice above each time. On Monday, the > processor could execute the query by truncating the table and then > inserting 3, 4, 5 in that order (what is the answer to the question > in that case?). On Tuesday, it could change wherever the 17 is stored > to 5, the data where the 3 is stored to 4, and the 8 to a 3, even though > the query appears to ask for a different update. And on Wednesday, > the processor might change the 17 to 4, the 8 to 5, and leave the 3 > untouched, > though still providing 3-row inserted and deleted tables and a rowcount > of 3. > On any of these days, the location of the physical data might move from > one place to another (this could happen when there is no query, even). > > In fact, the answer might be "The 17 was not changed" Maybe 3 new > rows were added to the table, and the existing rows were marked > invalid. > > Not many people stop to ask questions like "What do you mean by > 'which row,' but they are important questions. > > Steve Kass > Drew University > > Brian Selzer wrote: > > > DECLARE @inserted TABLE > > ( > > RowNumber INT IDENTITY(1, 1) NOT NULL, > > BadgeNo CHAR(6) NOT NULL, > > LastName VARCHAR(35) NOT NULL, > > FirstName VARCHAR(25) NOT NULL, > > Street VARCHAR(125) NOT NULL, > > City VARCHAR(40) NOT NULL, > > State CHAR(2) NOT NULL, > > ZipCode CHAR(9) NOT NULL, > > HomePhone CHAR(10) NOT NULL > > ) > > DECLARE @deleted TABLE > > ( > > RowNumber INT IDENTITY(1, 1) NOT NULL, > > BadgeNo CHAR(6) NOT NULL, > > LastName VARCHAR(35) NOT NULL, > > FirstName VARCHAR(25) NOT NULL, > > Street VARCHAR(125) NOT NULL, > > City VARCHAR(40) NOT NULL, > > State CHAR(2) NOT NULL, > > ZipCode CHAR(9) NOT NULL, > > HomePhone CHAR(10) NOT NULL > > ) > > INSERT @deleted > > (BadgeNo, LastName, FirstName, Street, City, State, ZipCode, HomePhone) > > SELECT BadgeNo, LastName, FirstName, Street, City, State, ZipCode, > >HomePhone > > FROM deleted WITH(INDEX(0)) OPTION(MAXDOP 1) > > INSERT @inserted > > (BadgeNo, LastName, FirstName, Street, City, State, ZipCode, HomePhone) > > SELECT BadgeNo, LastName, FirstName, Street, City, State, ZipCode, > >HomePhone > > FROM deleted WITH(INDEX(0)) OPTION(MAXDOP 1) > > > >Will this reliably allow me to determine which changes occurred to which > >rows within the body of a trigger? The only thing I found that even touched > >on this issue in the entire Microsoft web site was a comparison between > >Oracle and SQL Server 2000 in which the trigger did a full outer join. But > >that doesn't deal with the possibility that a PK value could be swapped > >during the same update, which would lead to different results depending on > >whether cascading updates were enabled. Nor would it allow an instead of > >update trigger to function correctly, especially if there are tables > >referencing the table with the instead of trigger, since you can't enable > >cascading updates on a table with an instead of trigger. > > > >I found documentation that the insert operation is always serialized so that > >inserts match the order of the select statement; however, the select > >statement that produces the result set to be inserted can generate a > >parallel plan, hence OPTION(MAXDOP 1). WITH(INDEX(0)) forces a table scan, > >so I believe that what is inserted into the @deleted and @inserted table > >variables will always match the order of the inserted and deleted > >pseudotables. > > > >The question is: is the order of the inserted and deleted pseudotables > >undefined, or do they correspond? I've done some preliminary testing and so > >far the order has always been the same. The last thing that happens in an > >execution plan with an instead of trigger is a split operation, which I > >assume loads the deleted and inserted pseudotables with the old and new > >values respectively. > > > > > > > > Brian,
This is not a limitation of SQL Server. It is a limitation of your model, which does not keep track of the information you require. Or it's a misunderstanding, and you assumed that every RDBMS had the loaded-gun-aimed-at-foot feature you are accustomed to. A lot of people are shocked when they find out after an unintentional DELETE that there is no "Undo" in SQL Server's Query Analyzer that undoes data modification operations. I wouldn't call that a limitation or flaw. Perhaps other RDBMS systems automagically expose something extra beyond what the SQL model defines to help you, but SQL Server doesn't. As I pointed out, you cannot expect any RDBMS to be able to answer questions about "which row" when that phrase has no meaning in your model. You are expecting SQL Server to tell you what color things were before they were painted. Unless you keep a previous_color column, or put an RFID chip in each block, or do something so you can always make sense of the question of which block is *and was* which, you're out of luck. If you will need to answer "which block used to be green?" then put something in your model that allows you to answer the question, or as you realize you can do, never paint more than one block at a time. Speculation about what split does, about the structure of this or that heap, about extent sizes and so on are useless here. You can make no assumptions about anything beyond what can be queried from your model and the metadata and management data SQL Server maintains for it. Even if you find a workaround (and I'm not sure what that means, since the question you need to answer is ill-defined), you will have no guarantee that it will work tomorrow or next week. You are obviously in an awkward position, because you have a bunch of components that together don't meet your requirements. You can complain that the procedures don't have a @NoMulti parameter; you can complain that your business rules are incomplete; you can complain that the data model you were given contains no immutable key; and you can complain that SQL Server can't break the atomicity of a multi-row update. If these complaints fail to reveal any hidden switches or secret settings, then it's time to decide which components need to be swapped for more suitable ones, or what you might add. One possibility might be to capture the text of each multi-row update and analyze it lexically to impute a serialization. As far as what "surrogate" means, you're welcome to your definition. There is no SURROGATE keyword in SQL, and if someone updates a column value you refer to as a surrogate, you can imagine that something was destroyed and something else was created if you want. It's not possible to mandate or determine the difference between "destroyed then created" and "modified" within an atomic SQL update. No row (or entity) of a table can be reliably identified except by its column values. SK Brian Selzer wrote: Show quote >I was hoping for a way to get around SQL Server's set-based only trigger >mechanism. Absent surrogates, I'm forced to serialize the updates to any >table that has an update trigger. This is a serious limitation of SQL >Server. The last operation performed by the query optimizer is a split >operation. I assume that to be a split of the set of updates into >corresponding sets of deletes and inserts that are to be inserted into the >deleted and inserted pseudotables respectively. Both tables have the same >heading and are uniform in length, so the heap structure generated by an >insert would be identical. The only problem I can see is if the number of >rows exceed the size of an extent, because a heap is scanned in the order an >extent falls within a database file. I was also hoping that because the >inserted and deleted pseudotables are not "really" tables that a workaround >would be possible. > >By the way, surrogates imply immutability. A surrogate represents only the >existence of an entity. An entity can be created or destroyed, thus coming >into existence or becoming nonexistent. The value of a surrogate serves >only to distinguish one existing entity from another. Once assigned during >the creation of an entity, that value cannot change for as long as the >entity exists. A change to that value would indicate that the entity no >longer exists. > > >"Steve Kass" <sk***@drew.edu> wrote in message >news:uJbXjwbiFHA.2916@TK2MSFTNGP14.phx.gbl... > > >>Brian, >> >>What do you mean by "which rows" when you ask "Will this reliably >>allow me to determine which changes occurred to *which rows* >>within the body of a trigger?" Unfortunately, given the result of >>an update that might have changed a key column value, it is >>impossible to know "which row" it was before the update. >> >>Without an immutable key (and immutability is an independent property >>from surrogacy), the question of "which old row changed to which new >>row" is not well defined, because "which row" is not defined. What >>do you mean by "which row" if there is no permanent way to identify >>a row? If "which" has no constant meaning, then your question has >>no answer. >> >> If you have business rules that say what kinds of changes are allowed, >>you need either to allow only one-at-a-time changes (if that is how the >>rules are expressed), or you need to be sure the rules govern multi-row >>changes in some way. If there are legal and illegal ways to get from >>here to there, you can't know whether the law was broken if someone >>got from here to there. >> >> The problem you set forth is like this. Maria steals Boban's >>favorite puzzle (http://www.corwin.ca/gridlock/) and runs into her >>room with it. A few minutes later, she gives it back to Boban, solved. >>Boban thinks she cheated and just picked up the blue block instead of >>sliding the blocks according to the rules. Unfortunately, he will never >>know, because it's possible to get the puzzle into the solved state in >>many ways, some of them following the rules, and some of them not >>following the rules. Only if the puzzle records the actual sequence of >>moves in some way would it be possible to find out whether Maria >>cheated or not. Maria knows whether she cheated, but she isn't >>telling, and she doesn't have to. (In fact, the puzzle she gave Boban >>may not even be the same puzzle she stole from him!) >> >> If you want to be sure no one is cheating, watch their every step. >> >> Suppose you have a one-column table T(OnlyCol) that contains three rows, >>and the OnlyCol values are 3, 17, and 8 (or 17, 8, and 3, or whatever, >> >> >since > > >>order doesn't matter). An update is performed, and afterwords the table >>contains >>the values 3, 4, and 5. >> >> You can certainly ask "What was the row with 17 changed to?" but >>there is no answer, because "the row with 17" only identifies a >>"before" row, and in the "after" picture, there is no way to identify >>"the row that used to have 17 in it". >> >>Many different updates could have yielded the result described on >>the three-row table, and perhaps some of them violate your >>business rules and others don't. >> >>update T set >> OnlyCol = case OnlyCol >> when 17 then 4 >> when 8 then 5 >> else OnlyCol end >> >>or >> >>update T set >> OnlyCol = case OnlyCol >> when 3 then 4 >> when 17 then 3 >> when 8 then 5 >> end >> >>are two possibilities. >> >>Even if the query text is known, SQL Server could do all kinds of >>things to effect the update. All it must do is make the operation >>atomic, and provide required information correctly, such as the >>inserted/deleted tables, the logs, and so on. No matter how >>it effects the update, @@rowcount will be 3, the inserted and >>deleted tables will each contain three rows (with the before >>and after sets of values), and the log will contain enough information >>to reconstruct the before and after states of the table. >> >>Beyond that, while the question "What was the 17 changed to?" might >>have meaning to a "fly on the disk," it could have one answer one >>day, another answer another day, and no answer a third day, even if the >>query text were the first choice above each time. On Monday, the >>processor could execute the query by truncating the table and then >>inserting 3, 4, 5 in that order (what is the answer to the question >>in that case?). On Tuesday, it could change wherever the 17 is stored >>to 5, the data where the 3 is stored to 4, and the 8 to a 3, even though >>the query appears to ask for a different update. And on Wednesday, >>the processor might change the 17 to 4, the 8 to 5, and leave the 3 >>untouched, >>though still providing 3-row inserted and deleted tables and a rowcount >>of 3. >>On any of these days, the location of the physical data might move from >>one place to another (this could happen when there is no query, even). >> >>In fact, the answer might be "The 17 was not changed" Maybe 3 new >>rows were added to the table, and the existing rows were marked >>invalid. >> >>Not many people stop to ask questions like "What do you mean by >>'which row,' but they are important questions. >> >>Steve Kass >>Drew University >> >>Brian Selzer wrote: >> >> >> >>>DECLARE @inserted TABLE >>>( >>> RowNumber INT IDENTITY(1, 1) NOT NULL, >>> BadgeNo CHAR(6) NOT NULL, >>> LastName VARCHAR(35) NOT NULL, >>> FirstName VARCHAR(25) NOT NULL, >>> Street VARCHAR(125) NOT NULL, >>> City VARCHAR(40) NOT NULL, >>> State CHAR(2) NOT NULL, >>> ZipCode CHAR(9) NOT NULL, >>> HomePhone CHAR(10) NOT NULL >>>) >>>DECLARE @deleted TABLE >>>( >>> RowNumber INT IDENTITY(1, 1) NOT NULL, >>> BadgeNo CHAR(6) NOT NULL, >>> LastName VARCHAR(35) NOT NULL, >>> FirstName VARCHAR(25) NOT NULL, >>> Street VARCHAR(125) NOT NULL, >>> City VARCHAR(40) NOT NULL, >>> State CHAR(2) NOT NULL, >>> ZipCode CHAR(9) NOT NULL, >>> HomePhone CHAR(10) NOT NULL >>>) >>>INSERT @deleted >>> (BadgeNo, LastName, FirstName, Street, City, State, ZipCode, HomePhone) >>> SELECT BadgeNo, LastName, FirstName, Street, City, State, ZipCode, >>>HomePhone >>> FROM deleted WITH(INDEX(0)) OPTION(MAXDOP 1) >>>INSERT @inserted >>> (BadgeNo, LastName, FirstName, Street, City, State, ZipCode, HomePhone) >>> SELECT BadgeNo, LastName, FirstName, Street, City, State, ZipCode, >>>HomePhone >>> FROM deleted WITH(INDEX(0)) OPTION(MAXDOP 1) >>> >>>Will this reliably allow me to determine which changes occurred to which >>>rows within the body of a trigger? The only thing I found that even >>> >>> >touched > > >>>on this issue in the entire Microsoft web site was a comparison between >>>Oracle and SQL Server 2000 in which the trigger did a full outer join. >>> >>> >But > > >>>that doesn't deal with the possibility that a PK value could be swapped >>>during the same update, which would lead to different results depending >>> >>> >on > > >>>whether cascading updates were enabled. Nor would it allow an instead of >>>update trigger to function correctly, especially if there are tables >>>referencing the table with the instead of trigger, since you can't enable >>>cascading updates on a table with an instead of trigger. >>> >>>I found documentation that the insert operation is always serialized so >>> >>> >that > > >>>inserts match the order of the select statement; however, the select >>>statement that produces the result set to be inserted can generate a >>>parallel plan, hence OPTION(MAXDOP 1). WITH(INDEX(0)) forces a table >>> >>> >scan, > > >>>so I believe that what is inserted into the @deleted and @inserted table >>>variables will always match the order of the inserted and deleted >>>pseudotables. >>> >>>The question is: is the order of the inserted and deleted pseudotables >>>undefined, or do they correspond? I've done some preliminary testing and >>> >>> >so > > >>>far the order has always been the same. The last thing that happens in >>> >>> >an > > >>>execution plan with an instead of trigger is a split operation, which I >>>assume loads the deleted and inserted pseudotables with the old and new >>>values respectively. >>> >>> >>> >>> >>> >>> > > > > The argument does not hold water. The limitation is real and forces a
set-based update to be serialized into a series of single row updates in the absence of an immutable primary key. Triggers are mainly used to enforce complex integrity constraints that cannot be enforced through declarative means. Given that a Person can be either Single, Married, Widowed, or Divorced, How do you enforce the following transition constraint: Single --> Married Married --> Divorced Married --> Widowed Divorced --> Married Widowed --> Married There is no declarative integrity constraint available in SQL Server that provides this functionality. Integrity constraints must be able to be stored in the database, hence triggers. In order to prevent an update that violates this constraint, a trigger must be able to determine what the old values were and what the new values are for each individual row in a set-based update. In this case, I didn't design the database, so berating and belittling me for my flawed data model, or my lack of forsight in key selection has little effect in this case. > One possibility might be to capture the text of each multi-row In other words, change every client application and stored procedure to> update and analyze it lexically to impute a serialization. > serialize the updates. > As far as what "surrogate" means, you're welcome to your definition. The definition of how a surrogate behaves is independent of its> There is no SURROGATE keyword in SQL, and if someone updates > a column value you refer to as a surrogate, you can imagine that something > was destroyed and something else was created if you want. It's not > possible to mandate or determine the difference between "destroyed then > created" and "modified" within an atomic SQL update. No row (or entity) > of a table can be reliably identified except by its column values. implementation in a specific RDBMS. For Oracle, SEQUENCES are available. For DB2 IDENTITY can be used. For SQL Server, IDENTITY provides this capability. An IDENTITY value is immutable. Each value is unique within a table. It is a candidate key, and can and probably should be used as a primary key. Since updates to an IDENTITY value are not possible your statement is pure nonsense. Show quote "Steve Kass" <sk***@drew.edu> wrote in message news:uW#uOriiFHA.2472@TK2MSFTNGP15.phx.gbl... > Brian, > > This is not a limitation of SQL Server. It is a limitation of your > model, which does not keep track of the information you require. > Or it's a misunderstanding, and you assumed that every RDBMS > had the loaded-gun-aimed-at-foot feature you are accustomed > to. A lot of people are shocked when they find out after an > unintentional DELETE that there is no "Undo" in SQL Server's > Query Analyzer that undoes data modification operations. > I wouldn't call that a limitation or flaw. > > Perhaps other RDBMS systems automagically expose something > extra beyond what the SQL model defines to help you, but SQL > Server doesn't. As I pointed out, you cannot expect any RDBMS > to be able to answer questions about "which row" when that > phrase has no meaning in your model. > > You are expecting SQL Server to tell you what color things > were before they were painted. Unless you keep a previous_color > column, or put an RFID chip in each block, or do something so > you can always make sense of the question of which block is > *and was* which, you're out of luck. If you will need to answer > "which block used to be green?" then put something in your model > that allows you to answer the question, or as you realize you can > do, never paint more than one block at a time. > > Speculation about what split does, about the structure of this or that > heap, about extent sizes and so on are useless here. You can make > no assumptions about anything beyond what can be queried from your > model and the metadata and management data SQL Server maintains > for it. Even if you find a workaround (and I'm not sure what that means, > since the question you need to answer is ill-defined), you will have no > guarantee that it will work tomorrow or next week. > > You are obviously in an awkward position, because you have > a bunch of components that together don't meet your > requirements. You can complain that the procedures don't > have a @NoMulti parameter; you can complain that your business > rules are incomplete; you can complain that the data model you were > given contains no immutable key; and you can complain that > SQL Server can't break the atomicity of a multi-row update. > If these complaints fail to reveal any hidden switches or secret > settings, then it's time to decide which components need to > be swapped for more suitable ones, or what you might add. > One possibility might be to capture the text of each multi-row > update and analyze it lexically to impute a serialization. > > As far as what "surrogate" means, you're welcome to your definition. > There is no SURROGATE keyword in SQL, and if someone updates > a column value you refer to as a surrogate, you can imagine that something > was destroyed and something else was created if you want. It's not > possible to mandate or determine the difference between "destroyed then > created" and "modified" within an atomic SQL update. No row (or entity) > of a table can be reliably identified except by its column values. > > SK > > > Brian Selzer wrote: > > >I was hoping for a way to get around SQL Server's set-based only trigger > >mechanism. Absent surrogates, I'm forced to serialize the updates to any > >table that has an update trigger. This is a serious limitation of SQL > >Server. The last operation performed by the query optimizer is a split > >operation. I assume that to be a split of the set of updates into > >corresponding sets of deletes and inserts that are to be inserted into the > >deleted and inserted pseudotables respectively. Both tables have the same > >heading and are uniform in length, so the heap structure generated by an > >insert would be identical. The only problem I can see is if the number of > >rows exceed the size of an extent, because a heap is scanned in the order an > >extent falls within a database file. I was also hoping that because the > >inserted and deleted pseudotables are not "really" tables that a workaround > >would be possible. > > > >By the way, surrogates imply immutability. A surrogate represents only the > >existence of an entity. An entity can be created or destroyed, thus coming > >into existence or becoming nonexistent. The value of a surrogate serves > >only to distinguish one existing entity from another. Once assigned during > >the creation of an entity, that value cannot change for as long as the > >entity exists. A change to that value would indicate that the entity no > >longer exists. > > > > > >"Steve Kass" <sk***@drew.edu> wrote in message > >news:uJbXjwbiFHA.2916@TK2MSFTNGP14.phx.gbl... > > > > > >>Brian, > >> > >>What do you mean by "which rows" when you ask "Will this reliably > >>allow me to determine which changes occurred to *which rows* > >>within the body of a trigger?" Unfortunately, given the result of > >>an update that might have changed a key column value, it is > >>impossible to know "which row" it was before the update. > >> > >>Without an immutable key (and immutability is an independent property > >>from surrogacy), the question of "which old row changed to which new > >>row" is not well defined, because "which row" is not defined. What > >>do you mean by "which row" if there is no permanent way to identify > >>a row? If "which" has no constant meaning, then your question has > >>no answer. > >> > >> If you have business rules that say what kinds of changes are allowed, > >>you need either to allow only one-at-a-time changes (if that is how the > >>rules are expressed), or you need to be sure the rules govern multi-row > >>changes in some way. If there are legal and illegal ways to get from > >>here to there, you can't know whether the law was broken if someone > >>got from here to there. > >> > >> The problem you set forth is like this. Maria steals Boban's > >>favorite puzzle (http://www.corwin.ca/gridlock/) and runs into her > >>room with it. A few minutes later, she gives it back to Boban, solved. > >>Boban thinks she cheated and just picked up the blue block instead of > >>sliding the blocks according to the rules. Unfortunately, he will never > >>know, because it's possible to get the puzzle into the solved state in > >>many ways, some of them following the rules, and some of them not > >>following the rules. Only if the puzzle records the actual sequence of > >>moves in some way would it be possible to find out whether Maria > >>cheated or not. Maria knows whether she cheated, but she isn't > >>telling, and she doesn't have to. (In fact, the puzzle she gave Boban > >>may not even be the same puzzle she stole from him!) > >> > >> If you want to be sure no one is cheating, watch their every step. > >> > >> Suppose you have a one-column table T(OnlyCol) that contains three rows, > >>and the OnlyCol values are 3, 17, and 8 (or 17, 8, and 3, or whatever, > >> > >> > >since > > > > > >>order doesn't matter). An update is performed, and afterwords the table > >>contains > >>the values 3, 4, and 5. > >> > >> You can certainly ask "What was the row with 17 changed to?" but > >>there is no answer, because "the row with 17" only identifies a > >>"before" row, and in the "after" picture, there is no way to identify > >>"the row that used to have 17 in it". > >> > >>Many different updates could have yielded the result described on > >>the three-row table, and perhaps some of them violate your > >>business rules and others don't. > >> > >>update T set > >> OnlyCol = case OnlyCol > >> when 17 then 4 > >> when 8 then 5 > >> else OnlyCol end > >> > >>or > >> > >>update T set > >> OnlyCol = case OnlyCol > >> when 3 then 4 > >> when 17 then 3 > >> when 8 then 5 > >> end > >> > >>are two possibilities. > >> > >>Even if the query text is known, SQL Server could do all kinds of > >>things to effect the update. All it must do is make the operation > >>atomic, and provide required information correctly, such as the > >>inserted/deleted tables, the logs, and so on. No matter how > >>it effects the update, @@rowcount will be 3, the inserted and > >>deleted tables will each contain three rows (with the before > >>and after sets of values), and the log will contain enough information > >>to reconstruct the before and after states of the table. > >> > >>Beyond that, while the question "What was the 17 changed to?" might > >>have meaning to a "fly on the disk," it could have one answer one > >>day, another answer another day, and no answer a third day, even if the > >>query text were the first choice above each time. On Monday, the > >>processor could execute the query by truncating the table and then > >>inserting 3, 4, 5 in that order (what is the answer to the question > >>in that case?). On Tuesday, it could change wherever the 17 is stored > >>to 5, the data where the 3 is stored to 4, and the 8 to a 3, even though > >>the query appears to ask for a different update. And on Wednesday, > >>the processor might change the 17 to 4, the 8 to 5, and leave the 3 > >>untouched, > >>though still providing 3-row inserted and deleted tables and a rowcount > >>of 3. > >>On any of these days, the location of the physical data might move from > >>one place to another (this could happen when there is no query, even). > >> > >>In fact, the answer might be "The 17 was not changed" Maybe 3 new > >>rows were added to the table, and the existing rows were marked > >>invalid. > >> > >>Not many people stop to ask questions like "What do you mean by > >>'which row,' but they are important questions. > >> > >>Steve Kass > >>Drew University > >> > >>Brian Selzer wrote: > >> > >> > >> > >>>DECLARE @inserted TABLE > >>>( > >>> RowNumber INT IDENTITY(1, 1) NOT NULL, > >>> BadgeNo CHAR(6) NOT NULL, > >>> LastName VARCHAR(35) NOT NULL, > >>> FirstName VARCHAR(25) NOT NULL, > >>> Street VARCHAR(125) NOT NULL, > >>> City VARCHAR(40) NOT NULL, > >>> State CHAR(2) NOT NULL, > >>> ZipCode CHAR(9) NOT NULL, > >>> HomePhone CHAR(10) NOT NULL > >>>) > >>>DECLARE @deleted TABLE > >>>( > >>> RowNumber INT IDENTITY(1, 1) NOT NULL, > >>> BadgeNo CHAR(6) NOT NULL, > >>> LastName VARCHAR(35) NOT NULL, > >>> FirstName VARCHAR(25) NOT NULL, > >>> Street VARCHAR(125) NOT NULL, > >>> City VARCHAR(40) NOT NULL, > >>> State CHAR(2) NOT NULL, > >>> ZipCode CHAR(9) NOT NULL, > >>> HomePhone CHAR(10) NOT NULL > >>>) > >>>INSERT @deleted > >>> (BadgeNo, LastName, FirstName, Street, City, State, ZipCode, HomePhone) > >>> SELECT BadgeNo, LastName, FirstName, Street, City, State, ZipCode, > >>>HomePhone > >>> FROM deleted WITH(INDEX(0)) OPTION(MAXDOP 1) > >>>INSERT @inserted > >>> (BadgeNo, LastName, FirstName, Street, City, State, ZipCode, HomePhone) > >>> SELECT BadgeNo, LastName, FirstName, Street, City, State, ZipCode, > >>>HomePhone > >>> FROM deleted WITH(INDEX(0)) OPTION(MAXDOP 1) > >>> > >>>Will this reliably allow me to determine which changes occurred to which > >>>rows within the body of a trigger? The only thing I found that even > >>> > >>> > >touched > > > > > >>>on this issue in the entire Microsoft web site was a comparison between > >>>Oracle and SQL Server 2000 in which the trigger did a full outer join. > >>> > >>> > >But > > > > > >>>that doesn't deal with the possibility that a PK value could be swapped > >>>during the same update, which would lead to different results depending > >>> > >>> > >on > > > > > >>>whether cascading updates were enabled. Nor would it allow an instead of > >>>update trigger to function correctly, especially if there are tables > >>>referencing the table with the instead of trigger, since you can't enable > >>>cascading updates on a table with an instead of trigger. > >>> > >>>I found documentation that the insert operation is always serialized so > >>> > >>> > >that > > > > > >>>inserts match the order of the select statement; however, the select > >>>statement that produces the result set to be inserted can generate a > >>>parallel plan, hence OPTION(MAXDOP 1). WITH(INDEX(0)) forces a table > >>> > >>> > >scan, > > > > > >>>so I believe that what is inserted into the @deleted and @inserted table > >>>variables will always match the order of the inserted and deleted > >>>pseudotables. > >>> > >>>The question is: is the order of the inserted and deleted pseudotables > >>>undefined, or do they correspond? I've done some preliminary testing and > >>> > >>> > >so > > > > > >>>far the order has always been the same. The last thing that happens in > >>> > >>> > >an > > > > > >>>execution plan with an instead of trigger is a split operation, which I > >>>assume loads the deleted and inserted pseudotables with the old and new > >>>values respectively. > >>> > >>> > >>> > >>> > >>> > >>> > > > > > > > > Brian, First off I am not going to get deep into this debate but you example: > Given that a Person can be either Single, Married, Widowed, or Divorced, is not the same as the issue at hand. The marital status is not a PK and > How do you enforce the following transition constraint: > > Single --> Married > Married --> Divorced > Married --> Widowed > Divorced --> Married > Widowed --> Married thus you can always find what the value of the marital status was before and after using a set based approach because you can use the PK to match them up. You never actually stated what it is you need to do when the PK changes. Did you try the cascading RI? Will that work for you? -- Show quoteAndrew J. Kelly SQL MVP "Brian Selzer" <br***@selzer-software.com> wrote in message news:%233gRFSkiFHA.1048@tk2msftngp13.phx.gbl... > The argument does not hold water. The limitation is real and forces a > set-based update to be serialized into a series of single row updates in > the > absence of an immutable primary key. Triggers are mainly used to enforce > complex integrity constraints that cannot be enforced through declarative > means. > > Given that a Person can be either Single, Married, Widowed, or Divorced, > How do you enforce the following transition constraint: > > Single --> Married > Married --> Divorced > Married --> Widowed > Divorced --> Married > Widowed --> Married > > There is no declarative integrity constraint available in SQL Server that > provides this functionality. Integrity constraints must be able to be > stored in the database, hence triggers. In order to prevent an update > that > violates this constraint, a trigger must be able to determine what the old > values were and what the new values are for each individual row in a > set-based update. > > In this case, I didn't design the database, so berating and belittling me > for my flawed data model, or my lack of forsight in key selection has > little > effect in this case. > >> One possibility might be to capture the text of each multi-row >> update and analyze it lexically to impute a serialization. >> > In other words, change every client application and stored procedure to > serialize the updates. > > >> As far as what "surrogate" means, you're welcome to your definition. >> There is no SURROGATE keyword in SQL, and if someone updates >> a column value you refer to as a surrogate, you can imagine that >> something >> was destroyed and something else was created if you want. It's not >> possible to mandate or determine the difference between "destroyed then >> created" and "modified" within an atomic SQL update. No row (or entity) >> of a table can be reliably identified except by its column values. > > The definition of how a surrogate behaves is independent of its > implementation in a specific RDBMS. For Oracle, SEQUENCES are available. > For DB2 IDENTITY can be used. For SQL Server, IDENTITY provides this > capability. An IDENTITY value is immutable. Each value is unique within > a > table. It is a candidate key, and can and probably should be used as a > primary key. Since updates to an IDENTITY value are not possible your > statement is pure nonsense. > > "Steve Kass" <sk***@drew.edu> wrote in message > news:uW#uOriiFHA.2472@TK2MSFTNGP15.phx.gbl... >> Brian, >> >> This is not a limitation of SQL Server. It is a limitation of your >> model, which does not keep track of the information you require. >> Or it's a misunderstanding, and you assumed that every RDBMS >> had the loaded-gun-aimed-at-foot feature you are accustomed >> to. A lot of people are shocked when they find out after an >> unintentional DELETE that there is no "Undo" in SQL Server's >> Query Analyzer that undoes data modification operations. >> I wouldn't call that a limitation or flaw. >> >> Perhaps other RDBMS systems automagically expose something >> extra beyond what the SQL model defines to help you, but SQL >> Server doesn't. As I pointed out, you cannot expect any RDBMS >> to be able to answer questions about "which row" when that >> phrase has no meaning in your model. >> >> You are expecting SQL Server to tell you what color things >> were before they were painted. Unless you keep a previous_color >> column, or put an RFID chip in each block, or do something so >> you can always make sense of the question of which block is >> *and was* which, you're out of luck. If you will need to answer >> "which block used to be green?" then put something in your model >> that allows you to answer the question, or as you realize you can >> do, never paint more than one block at a time. >> >> Speculation about what split does, about the structure of this or that >> heap, about extent sizes and so on are useless here. You can make >> no assumptions about anything beyond what can be queried from your >> model and the metadata and management data SQL Server maintains >> for it. Even if you find a workaround (and I'm not sure what that means, >> since the question you need to answer is ill-defined), you will have no >> guarantee that it will work tomorrow or next week. >> >> You are obviously in an awkward position, because you have >> a bunch of components that together don't meet your >> requirements. You can complain that the procedures don't >> have a @NoMulti parameter; you can complain that your business >> rules are incomplete; you can complain that the data model you were >> given contains no immutable key; and you can complain that >> SQL Server can't break the atomicity of a multi-row update. >> If these complaints fail to reveal any hidden switches or secret >> settings, then it's time to decide which components need to >> be swapped for more suitable ones, or what you might add. >> One possibility might be to capture the text of each multi-row >> update and analyze it lexically to impute a serialization. >> >> As far as what "surrogate" means, you're welcome to your definition. >> There is no SURROGATE keyword in SQL, and if someone updates >> a column value you refer to as a surrogate, you can imagine that >> something >> was destroyed and something else was created if you want. It's not >> possible to mandate or determine the difference between "destroyed then >> created" and "modified" within an atomic SQL update. No row (or entity) >> of a table can be reliably identified except by its column values. >> >> SK >> & | |||||||||||||||||||||||
