Relationship between inserted and deleted tables?

Hi all,

I just ran across an issue on a SQL 2000 sp4 db where RI was being maintained solely with triggers. I am attempting to change the primary key of a parent table and cascade the results to all its children without using the vendor-supplied trigger code (long story...) using an INSTEAD OF trigger.

My question is: does SQL Server create any kind of relationship between the inserted and deleted tables that I could exploit since the key field is unavailable?

I am trying to avoid having to add a surrogate key to each of the children just for this activity (as there are many M rows in each and no other suitable unique column combinations that span all the child tables).

-DC

As far as I understood your question, no. But the deleted and the inserted tables will always have the full structure of the modified table, so the key column should be available for you ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi Jens,

This code should help clear up my question. Case 2 is what I am talking about.

create table tt (pk int primary key,col2 int,col3 int)

create table tg (pk int primary key,col2 int,col3 int)

insert tt values (1,2,3)

insert tg values (1,3,7)

go

create trigger tr1

on tt

instead of update

as

begin

select pk,col2,col3 from inserted

select pk,col2,col3 from deleted

end

go

set nocount on

print 'Case 1. Non-key field updated. Can join on pk'

print ' '

update tt set col2=9 where col2=2

print ' '

print 'Case 2. Key field updated. Cannot join on pk'

print ' '

update tt set pk=3 where pk=1

drop table tt

go

drop table tg

go

|||

OK, got it. YOu will have to join on all non key columns then. for performance reasons, you could check the updated column and use the update on the PK if the OK was not updated and the covering join when then PK was updated.

|||

Much appreciated, Jens. I will try that. Thanks!

sql