Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Create LOG for tables in SQL Server

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)

like image 845
Salah Sanjabian Avatar asked Oct 14 '25 08:10

Salah Sanjabian


1 Answers

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
like image 80
gbn Avatar answered Oct 17 '25 17:10

gbn



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!