Create LOG for tables in SQL Server

Question

I have a table Sample and another SampleLog

With these structure I want to write codes to log. You can see my codes after structures of tables

CREATE TABLE [dbo].[Sample](
   [ID] [int] NULL,
   [Name] [varchar](10) NULL
)

CREATE TABLE [dbo].[SampleLog](
   [ID] [int] NULL,
   [Name] [varchar](10) NULL,
   [Date] [datetime] NULL,
   [UserName] [varchar](100) NULL,
   [Type] [char](1) NULL
) 

I have written this code but it doesn't work for Delete and Update .

CREATE TRIGGER SampleTrigger ON Sample 
AFTER INSERT, UPDATE, DELETE
AS
   DECLARE 
      @ID int ,
      @Name varchar(10),
      @Date datetime,
      @UserName VARCHAR(128) ,
      @Type CHAR(1) ,
      @sql nvarchar(500)

  SELECT         
      @UserName = SYSTEM_USER ,
      @Date = CONVERT(VARCHAR(8), GETDATE(), 112) 
              + ' ' + CONVERT(VARCHAR(12), GETDATE(), 114)

  IF EXISTS (SELECT * FROM inserted)
  BEGIN
     IF EXISTS (SELECT * FROM deleted)
     BEGIN
        SELECT @Type = 'U'
        select @ID = ID from deleted
        select @Name = Name from deleted
   END
   ELSE
   BEGIN
      SELECT @Type = 'I'
      select @ID = ID from inserted
      select @Name = Name from inserted
   END
 END
 ELSE
BEGIN
   SELECT @Type = 'D'
   select @ID = ID from deleted
   select @Name = Name from deleted
END

insert into SampleLog(ID, Name, Date, UserName, Type) 
values(@ID, @Name, @Date, @UserName, @Type)

SQL Server gives me this error

The row values updateed or deleted either do not make the row unique or they alter multiple rows(2 rows)

Answer 1

You've coded for single row updates and deletes. Think sets!

CREATE TRIGGER SampleTrigger ON Sample after INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON;

insert into SampleLog
    (ID,Name,Date,UserName,Type)
SELECT
    D.ID, D.NAME, GETDATE(), SYSTEM_USER,
    CASE WHEN I.ID IS NULL THEN 'D' ELSE 'U' END
FROM
    DELETED D
    LEFT JOIN
    INSERTED I ON D.ID = I.ID
UNION ALL
SELECT
    I.ID, I.NAME, GETDATE(), SYSTEM_USER, 'I'
FROM
    INSERTED I
    LEFT JOIN
    DELETED D ON D.ID = I.ID
WHERE
    D.ID IS NULL
GO