Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
| Search | Today's Posts | Mark Forums Read
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old June 11th, 2008, 08:58 AM
Registered User
 
Join Date: Jun 2008
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default UPDATE TRIGGER, INSERTED and DELETED tables

Hello, I have a table with complex primary key( consisting of two fields ). MSSQL2005
 Example:
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
ON PersonPhone
WITH ENCRYPTION
INSTEAD OF UPDATE
AS
  --SQL Code
GO


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).
  #2 (permalink)  
Old June 11th, 2008, 11:19 AM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

I don't know of anyway to do this without something like an IDENTITY column or an alternate key of some sort... why would you be updating a primary key in such a fashion? Primary Keys are usually sacred when it comes to updates.

--Jeff Moden


Similar Threads
Thread Thread Starter Forum Replies Last Post
delay trigger until all records are inserted rob209 SQL Server 2000 5 March 26th, 2013 06:17 AM
After Update Trigger debbiecoates SQL Server 2000 2 February 15th, 2008 04:55 AM
insert,update trigger rolle SQL Server 2000 3 September 29th, 2005 08:41 PM
Help With INSERT + UPDATE Trigger HenryE SQL Server 2000 1 December 11th, 2003 06:26 PM
Insert Update Trigger mstuart60 SQL Server 2000 10 September 30th, 2003 06:54 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.