sql server - Triggers: Tracking ID Updates -

for trigger tracking updates table, 2 temp tables may referenced: deleted , inserted. there way cross-reference 2 w/o using inner join on primary key?

i trying maintain referential integrity without foreign keys (don't ask), i'm using triggers. want updates primary key in table reflected in "foreign key" of look-up table b, , happen when update affects multiple records in table a.

all update trigger examples i've seen hinge on joining inserted , deleted tables track changes; , use updated table's id field (primary key) set join. if id field (guid) changed field in record (or set of records), there way track changes, can enforce changes in corresponding look-up table?

i've had issue (or rather, similar one), myself, hence resurrection...

my eventual approach disallow updates pk field precisely because break trigger. thankfully, had no business case support updating primary key column (these surrogate ids, anyway), away it.

sql server offers update function, use within triggers, check edge case:

create trigger your_trigger on your_table instead of update begin      if update(pk1) begin         rollback          declare @proc sysname, @table sysname          select top 1              @proc = object_name(@@procid)             ,@table = object_name(parent_id)         sys.triggers          object_id = @@procid          raiserror ('trigger %s prevents update of table %s due locked primary key', 16, -1, @proc, @table) nowait     end     else update t set          col1 = i.col1         ,col2 = i.col2         ,col3 = i.col3     your_table t     inner join inserted on t.pk1 = i.pk1  end go 

(note above untested, , contains manner of issues regards xact_state or trigger_nestlevel -- it's there demonstrate principle)

it gets bit messy, though, consider code generation this, handle changes table during development (maybe done ddl trigger on create/alter table).

if have composite primary key, can use if update(pk1) or update(pk2)... or bitwise work columns_updated function, give bitmask based on column ordinal (but i'm not going cover here -- see msdn/bol).

the other (simpler) option deny update on your_table(pk) public, remember member of sysadmins (and dbo) not honour this.