UPDATE TRIGGER, INSERTED and DELETED tables
Hello, I have a table with complex primary key( consisting of two fields ). MSSQL2005
CREATE TABLE PersonPhone(
PerId INT NOT NULL,
PhnId INT NOT NULL,
PhnType BIT NOT NULL,
CONSTRAINT PK_PersonPhone_PerId_PhnId PRIMARY KEY (PerId, PhnId),
For this table I have INSTEAD OF UPDATE trigger.
CREATE TRIGGER TRG_ISND_UPD_VW_PersonPhone
INSTEAD OF UPDATE
In the body of the trigger I access tables INSERTED and DELETED(containing new and old values of changed records respectively). Here is my question: How do I find which records in table DELETED correspond to which records in table INSERTED, if value of primary key field is changed( I have a primary key field in my UPDATE clause: UPDATE PersonPhone SET PhnId = 10..............)? Of course I can create unique key for the table(IDENTITY) and also make the existing one - an alternative key. But I think, there is a more elegant way of doing this.
I can use cursors to iterate over INSERTED and DELETED tables simultaneously.But nowhere in the documentation of MSSQL2005 I found something related to the sequence of records in these tables(whether the first one in DELETED corresponds to the first one in INSERTED).