Geek Answers Handbook

Defining an old primary key in SQL Trigger

I did it before, somewhere I'm sure of it!

I have a SQL Server 2000 table in which I need to log field changes in updates and insert them into a second logging table. The following is a simplified version of the structure used:

MainTable
ID varchar(10) PRIMARY KEY
DESCRIPTION varchar(50)

LogTable
OLDID varchar(10)
NEWID varchar(10)

For any other field, something like this would work just fine:

Select i.DESCRIPTION As New, d.DESCRIPTION As Old 
From Inserted i
LEFT JOIN Deleted d On i.ID=d.ID

... But, obviously, the connection will not work if the ID has been changed.

I cannot change the Tables in a way, the only power I have in this database is to create a trigger.

Alternatively, is there someone who can teach me how to travel in time, and I will go back in time and ask myself then how did I do this? Greetings :)

Edit:

, . , , , .

, , . , .. :)

+5
8

, INSERTED DELETED, , ?

+2
DECLARE @OldKey int, @NewKey int;

SELECT @Oldkey = [ID] FROM DELETED;
SELECT @NewKey = [ID] FROM INSERTED;

, . "" . > 1 INSERTED.

+3

, . , 4 :

1  Val1
2  Val2
3  Val3
4  Val4

:

UPDATE MainTable SET
ID = CASE ID WHEN 1 THEN 2 WHEN 2 THEN 1 ELSE ID END
Description = CASE ID WHEN 3 THEN 'Val4' WHEN 4 THEN 'Val3' ELSE Description END

, , 1 2, , 3 4. , , ? , , , .

(, "" UNIQUE), , , , , .

+1

/, , , , , - INSTEAD OF. , / , /.

+1

---- ---- , , :

ALTER TABLE YourTableName ADD
    PrivateID int NOT NULL IDENTITY (1, 1)
GO

---- ---- / . ?

, .

, , SCOPE_IDENTITY(), , . . , , . , ...

0

SQL Server : . . , , :

  • -
  • - ,

  • deleted - ,
  • -

  • deleted - , UPDATE
  • insert - , UPDATE

. , , , : ( , DateChanged):

INSERT INTO MyAuditTable
(ID, FirstColumn, SecondColumn, ThirdColumn, Operation, DateChanged)
VALUES
SELECT ID, FirstColumn, SecondColumn, ThirdColumn, 'Update-Before', GETDATE()
FROM deleted
UNION ALL
SELECT ID, FirstColumn, SecondColumn, ThirdColumn, 'Update-After', GETDATE()
FROM inserted
0

MainTable (, , ), , . .

INSERT INTO LOG(OLDID, NEWID)
SELECT deleted.id AS OLDID, inserted.id AS NEWID
FROM inserted 
INNER JOIN deleted 
    ON inserted.correlationid = deleted.correlationid

: .

0

, , , () , , . Oracle MySQL , , SQL Server.

, , , , - :

if exists
  (
  select *
    from inserted changed
           join deleted old
   where changed.rowID = old.rowID
     and changed.id != old.id
  )
... [roll it all back]

Google ROWID. , SQL Server ; .

, , , , , , , - . MOST, :

if exists
  (
  select *
    from inserted lost
           left join updated match
             on match.id = lost.id
   where match.id is null
  union
  select *
    from deleted new
           left join inserted match
             on match.id = new.id
    where match.id is null
  )
  -- roll it all back

, ...

update myTable
   set id = case
              when id = 1 then 2 
              when id = 2 then 1
              else id
              end

, , . . for-each-row, Oracle, MySQL... , , SQL Server. , , . , , APPEARS SQL Server 2008 R2. script , , -Oracle.

If anyone can point me somewhere where my assumption is documented and guaranteed by Microsoft, I would be a very grateful guy ...

begin try
  drop table kpTest;
end try
begin catch
end catch
go

create table kpTest( id int primary key, name nvarchar(10) )
go

begin try
  drop trigger kpTest_ioU;
end try
begin catch
end catch
go

create trigger kpTest_ioU on kpTest
instead of update
as
begin
  if exists
    (
    select *
      from inserted lost
             left join deleted match
               on match.id = lost.id
     where match.id is null
    union
    select *
      from deleted new
             left join inserted match
               on match.id = new.id
      where match.id is null
    )
      raisError( 'Changed primary key', 16, 1 )
  else
    update kpTest
       set name = i.name
      from kpTest
             join inserted i
               on i.id = kpTest.id
    ;
end
go

insert into kpTest( id, name ) values( 0, 'zero' );
insert into kpTest( id, name ) values( 1, 'one' );
insert into kpTest( id, name ) values( 2, 'two' );
insert into kpTest( id, name ) values( 3, 'three' );

select * from kpTest;

/*
0   zero
1   one
2   two
3   three
*/

-- This throws an error, appropriately
update kpTest set id = 5, name = 'FIVE' where id = 1
go

select * from kpTest;

/*
0   zero
1   one
2   two
3   three
*/

-- This allows the change, inappropriately
update kpTest 
   set id = case   
              when id = 1 then 2
              when id = 2 then 1
              else id
              end
     , name = UPPER( name )
go

select * from kpTest

/*
0   ZERO
1   TWO   -- WRONG WRONG WRONG
2   ONE   -- WRONG WRONG WRONG
3   THREE
*/

-- Put it back
update kpTest 
   set id = case   
              when id = 1 then 2
              when id = 2 then 1
              else id
              end
     , name = LOWER( name )
go

select * from kpTest;

/*
0   zero
1   one
2   two
3   three
*/

drop trigger kpTest_ioU
go

create trigger kpTest_ioU on kpTest
instead of update
as
begin
  declare newIDs cursor for select id, name from inserted;
  declare oldIDs cursor for select id from deleted;
  declare @thisOldID int;
  declare @thisNewID int;
  declare @thisNewName nvarchar(10);
  declare @errorFound int;
  set @errorFound = 0;
  open newIDs;
  open oldIDs;
  fetch newIDs into @thisNewID, @thisNewName;
  fetch oldIDs into @thisOldID;
  while @@FETCH_STATUS = 0 and @errorFound = 0
    begin
      if @thisNewID != @thisOldID
        begin
          set @errorFound = 1;
          close newIDs;
          deallocate newIDs;
          close oldIDs;
          deallocate oldIDs;
          raisError( 'Primary key changed', 16, 1 );
        end
      else
        begin
          update kpTest
             set name = @thisNewName
           where id = @thisNewID
          ;
          fetch newIDs into @thisNewID, @thisNewName;
          fetch oldIDs into @thisOldID;
        end
    end;
  if @errorFound = 0
    begin
      close newIDs;
      deallocate newIDs;
      close oldIDs;
      deallocate oldIDs;
    end
end
go

-- Succeeds, appropriately
update kpTest
   set name = UPPER( name )
go

select * from kpTest;

/*
0   ZERO
1   ONE
2   TWO
3   THREE
*/

-- Succeeds, appropriately
update kpTest
   set name = LOWER( name )
go

select * from kpTest;

/*
0   zero
1   one
2   two
3   three
*/


-- Fails, appropriately
update kpTest 
   set id = case   
              when id = 1 then 2
              when id = 2 then 1
              else id
              end
go

select * from kpTest;

/*
0   zero
1   one
2   two
3   three
*/

-- Fails, appropriately
update kpTest 
   set id = id + 1
go

select * from kpTest;

/*
0   zero
1   one
2   two
3   three
*/

-- Succeeds, appropriately
update kpTest 
   set id = id, name = UPPER( name )
go

select * from kpTest;

/*
0   ZERO
1   ONE
2   TWO
3   THREE
*/

drop table kpTest
go
0
source

More articles:


All Articles