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)
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With