| Author |
Topic |
|
es4net
Starting Member
4 Posts |
 Posted - 2002-02-11 : 16:58:04
|
| Hi,I have the following question on triggers:What will be the order of rows returned when a SELECT is done on the deleted and inserted tables ?For eg.SELECT * from deletedSELECT * from insertedDoes the "SELECT * from deleted" and the "SELECT * from inserted" return the rows in the same order?In this way it is possible to know that for an update, the first row in the deleted table(with old values for the row) corresponds to the first row in the inserted table(with the new values for the same row).It is possible to know the mapping between the deleted and inserted tables with primary keys. But what if there are no primary keys.Your reply is highly appreciated.Thanks in advance. |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-02-11 : 17:10:10
|
es4net,There is no ordering in a set and hence you can never be 100% sure that both the Inserted and Deleted table are ordered "correctly".You can (almost) treat the Insertered and Deleted table as if they where real tables...eg.Select I.*, D.* from Inserted as Iinner join Deleted as D on D.ID = I.ID HTHDavidMTomorrow is the same day as Today was the day before. |
 |
|
|
es4net
Starting Member
4 Posts |
Posted - 2002-02-11 : 17:32:01
|
| David,Thanks for your quick reply. I have more questions.I agree that we cannot rely on the ordering in the deleted and inserted tables. But, will ordering be the same in the deleted and inserted tables.ie. Does each row in deleted corresponds to a row in inserted in the same position of the row ?Your solution will not work, if the primary key (ID) is updated. Also I want a solution if there is no primary key.Thanks. |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-02-11 : 17:40:23
|
quote:
I agree that we cannot rely on the ordering in the deleted and inserted tables. But, will ordering be the same in the deleted and inserted tables.
You answered your own question...But I think SQL Server will have the same ordering for the deleted and insertered tables... (I still wouldn't trust it)So I take it you are doing a bulk update of Primary Keys...What is your final goal here?DavidMTomorrow is the same day as Today was the day before. |
|
|
|
es4net
Starting Member
4 Posts |
Posted - 2002-02-11 : 17:52:09
|
| David,In my trigger body, I need to track the updated row(s).I need to have the old values of the updated row(s) and the new valuesof the updated row(s).My table may or may NOT have primary keys. If the table has primary key, the primary key can also be updated.What is the way of "matching" the row in the deleted table to the row in the inserted table ?SQL server does NOT have row level triggers. So, there must be a way to achieve the row level trigger stuff.Are there any articles/news items that talks about such cases ?Your thoughts are appreciated.Thanks. |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-02-11 : 18:07:45
|
If the table does not have a PK you are (how can I put this politely.. screwed). Your only option to get around this is to use a Stored Procedure.....quote:
SQL server does NOT have row level triggers. So, there must be a way to achieve the row level trigger stuff.
Thank God it doesn't!!! SQL Server handles Set level triggers.. a set can contain just 0, 1 or infinite "rows". You will find that most usage of triggers is at a "row" level.....HTHDavidMTomorrow is the same day as Today was the day before. |
|
|
|
es4net
Starting Member
4 Posts |
Posted - 2002-02-11 : 18:18:49
|
| Thanks for your reply and your politeness :)What do you mean by using a stored preocedure ?Any more details ...? Example...(The case I would like to be handled is updating multiple rows and that too the primary key column(s))Thanks. |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-02-11 : 18:34:41
|
| If you want to make your life easier, add a surrogate key (identity or GUID) to each table. Those fields will never be touched by your application and guaranteed to be unique... Therefore you'll have something you can join on. |
|
|
|
|
Triggers: row order in deleted and inserted tables