Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2002
    Posts
    7

    Unanswered: is order of rows in inserted,deleted tables always identical

    Hi,

    I need to figure out in the update trigger of a table which row in inserted
    table corresponds to which row in deleted table when primary key
    of the table is updated. Is it guaranteed that row no N in inserted corresponds to row no N in deleted . If not , is there any way to
    find out the matching pair of rows? I guess, not.

    Thanks
    Nikhil

  2. #2
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    drop table tblA
    GO
    create table tblA([id] int NOT NULL primary key)
    GO
    insert tblA([id]) values (1)
    insert tblA([id]) values (2)
    insert tblA([id]) values (3)
    insert tblA([id]) values (4)
    insert tblA([id]) values (5)
    GO
    create trigger ti_tblA_Upd on tblA instead of update as
    declare @inserted table (Ord int identity(1,1) primary key,[id] int)
    declare @deleted table (Ord int identity(1,1) primary key,[id] int)
    insert @inserted select [id] from inserted
    insert @deleted select [id] from deleted
    select i.[id],d.[id]
    from @deleted d
    join @inserted i on d.Ord=i.Ord
    update a set a.[id]=i.[id]
    from tblA a
    join @deleted d on a.[id]=d.[id]
    join @inserted i on d.Ord=i.Ord
    GO
    select [id]^3%5,[id] from tblA
    update tblA set [id]=[id]^3%5
    select [id] from tblA

  3. #3
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    Cursors cannot be used. Set operations (1->2)+(2->1) would fail.

    It is a better idea to add one more unique and unupdatable column to the table (unique identity or marked rowid).

    Or deny updates of PK at all.

    Good luck!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •