December 4, 2002 at 3:37 am
I just wonder if it's safe to assume that the row order is the same for INSERTED and DELETED when I use one cursor for each of them in a trigger?
In my case I have two cursors like:
SELECT * FROM INSERTED
and
SELECT * FROM DELETED
I need to be sure that when I compare the
values in each row, they correspond to the same original row...
(I can not be sure that the table have a PK, thus the cursor solution...)
/StPi
December 4, 2002 at 3:47 am
I suggest not to use cursor in a trigger because it could have severe performance impact.
December 4, 2002 at 5:14 am
I am pretty sure they are but there are no guarantees and if not the process could potnetially work for a long time and then fail without notice.
December 4, 2002 at 7:25 am
OK, thank you...
I guess I have to contact Microsoft to
be totally sure!
/StPi
December 4, 2002 at 8:29 am
One of the concepts about relational database theory is you don't assume any specific ordering unless explicitly qualified by an ORDER BY clause. Our development group was tripped up by this when an index changed (though it was against a regular table, not inserted or deleted).
Also, what are you trying to compare? You could join inserted and deleted based on the primary key of the table to be able to determine what matches up. This eliminates the cursor and the ordering issue.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
December 4, 2002 at 8:55 am
You should have a PK on every table of substance in your db. There really isn't any good reason NOT to have at least one column. If you have multiple indexes on the column, then you should choose at least (and most) one to be a clustered index.
Then you won't have this problem.
December 4, 2002 at 9:04 am
There was a PK and there was a clustered index. The clustered index changed due to an analysis of usage in order to provide greater performance... developers' stored procedures didn't have the ORDER BY clause and DBAs didn't have veto power. They had been warned. We also warned changing the clustered index could dramatically affect their queries. Hence, they learned their lesson.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
December 4, 2002 at 1:21 pm
LMAO.....
That's generally the way I've seen developers learn to pay attention to DBA's
I call it "letting that ole dog knaw a little", and you'd be surprised how many times they generally need to be bit before the pay attention.
December 4, 2002 at 1:34 pm
I spend so much time arguing poitn and counterpoint to some of my fellow developers that I start making their mistakes in developement. But as I tell them, you don't wanna listen, fine see what happens when I make a change after I warn you.
December 5, 2002 at 4:51 am
YES, The best thing is to have a PK!
The trouble here is that I want to make a
Stored Procedure that produces trigger code
for any table. That's why I can't assume that
there is a PK...
(The SP checks for changes and outputs them
to an Audit table...)
Maybe I should demand that each table MUST
have a PK anyway...
But I'm still interested in the order issue!
/StPi
December 5, 2002 at 5:03 am
You cannot depend on the recordset coming back in a particular order unless you specify an ORDER BY clause. Generally, you'll see behavior where the order comes back matching the physical structure of the data, but the only way you are guaranteed order is to use ORDER BY.
As far as having a single procedure that produces trigger information, if you have a PK on every table then you can autogenerate the triggers.
However, let me throw this caution out and it may not apply. AFTER triggers don't capture text, ntext, and image data columns in the inserted and deleted tables. INSTEAD OF triggers do, because the idea is to intercpt before a data operation and if all is well you perform the operation in the trigger.
If you're looking to auto-generate triggers on tables, there are 3rd party programs that do this. Lockwood tech has auto-audit which I won in a contest here a while back but I've not tested it extensively to give an opinion. Lumigent has Integra (again, I've not tested).
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply