Triggers - how to join INSERTED to DELETED

[Moptop]

Hi - can anybody help me with this query.

In an update trigger, how do you know which record in the Inserted table refers to which record in the Deleted table in the case of an update statement updating multiple records.

My guess is that the two tables are in the same sequence. The only way that I can come up with is to SELECT the pseudo tables into temp tables with an identity column.

Is this the only way?

Thanks

Mark

 

[JohnDTampaBay]

The INSERTED and DELETED have the same structure as the base table. Whatever column(s) make up the key for the base table also make up the key for the pseudo-tables. For example, if you are updating the Customers table with a key of CustomerID, the key for the INSERTED and DELETED tables will also be the CustomerID column.

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[Moptop]

Hi John - Thanks for responding.
I understand that bit fine.
The problem comes though when in your example somebody has run the following script:

UPDATE CUSTOMERS
SET CustomerID = CM.NewCustomerID
FROM CUSTOMERS C
INNER JOIN CUSTOMERS_MIGRATION CM ON C.CustomerID = CM.OldCustomerID

in this case, you are amending the primary key on multiple, so how would it work in this case?

Thanks

Mark

 

[JohnDTampaBay]

By definition, a primary key uniquely identifies a single row. If any given CustomerID does not identify a single, specific row, then CustomerID is not the primary key. Perhaps CustomerID combined with another column creates the primary key. If so, these are the columns to join on. If you have no column(s) that uniquely identify a row, then you have no way of knowing what rows were updated.

You could add an identity column to the Customers table and use that as a surrogate primary key. That way the INSERTED and DELETED tables can join on the indentity column. Good luck.

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[Moptop]

Hi
In our database, the primary keys change over the lifespan of the customer. for instance customer 1111 can become 2222 and then later on be renumbered to 3333. I am trying to come up with a plan for auditing data changes. My current plan is to add a GUID column to every table that is defaulted to NEWID() but is never changed otherwise. This could then become my unique column for joining on. The downside of this approach is the storage needed for adding GUIDs to every column on every table. The database is very large already.

I didn't like the idea of storing the INSERTED and DELETED tables in temp tables with an Identity column because I think that there will be a big performance hit.

I thought that I would ask the question here first as there seems to be some pretty bright people on this forum

What do you think about my GUID approach?

Yours

Mark

 

[JohnDTampaBay]

How about int or bigint instead of GUID? They aren't quite as heavy as GUID, easier for humans to deal with, and they have quite a range, especially if you include the negatives.

bigint
Integer (whole number) data from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807). Storage size is 8 bytes.

int
Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647). Storage size is 4 bytes. The SQL-92 synonym for int is integer.



--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[Moptop]

All valid points, but I am thinking that with GUIDs I might get a few more tracking benefits when the data gets to our data warehouse as each record will have a unique key - BUT - it must be said that I haven't yet made the decision to do it this way yet. I will do some more investigation first.

Thanks for your advice. Its good to know that I haven't missed a really obvoius T-SQL command somewhere.

Thanks

Mark