tsql autid trigger.sql

trigger.sql

/*
DROP TABLE GUESTS
go
CREATE TABLE GUESTS (
  ID int IDENTITY(1,1) PRIMARY KEY,
  NAME VARCHAR(50),
  SURNAME VARCHAR(50),
)

DROP TABLE Audit
GO
CREATE TABLE Audit(
  ID int
, FieldName VARCHAR(128)
, Old VARCHAR(1000)
, modified_at datetime
)

insert into guests (name) values ('Frank')
select id,name from  guests
delete from audit
select * from Audit

*/

ALTER TRIGGER TR_GUESTS_AUDIT ON GUESTS FOR UPDATE    AS
    DECLARE @now DATETIME
    select @now = GETDATE()
    insert
      into audit
        (id,fieldname,old,modified_at)
      select i.id,'name',d.name,@now
        from inserted i
        join deleted d on i.id = d.id
        where isnull(i.name,'[NULL]') <>  isNull(d.name,'[NULL]')


delete from audit
select * from guests where id = 1

update guests  set name = 'Frank' where id =1
update guests  set name = NULL where id =1

update guests  set name = 'Horst' where id =1
select * from audit






GO

trigger1.sql

select top 10 * from users_workplace

-- CREATE TABLE Audit(
--   ID int
-- , FieldName VARCHAR(128)
-- , Old VARCHAR(1000)
-- , modified_at datetime
-- )