Audit DB using SQL Server triggers

Triggers (MSSMS)
1. In Object Explorer, Expand Databases expand the database to be Audited  expand Tables
 expand the table to be Audited
2. Right-click Triggersselect New Trigger.
3. On the Query menu, click Specify Values for Template Parameters(Ctrl-Shift-M) to open the
Specify Values for Template Parameters dialog box.

In the Specify Values for Template Parameters dialog box, enter the following values for the
parameters shown.

4. Click OK.
In the Query Editor, replace the comment-- Insert statements for trigger here with the following
statement:
DECLARE @login_name VARCHAR(30)

SELECT @login_name = login_name

FROM sys.dm_exec_sessions
WHERE session_id = @@SPID

IF EXISTS ( SELECT 0 FROM Deleted )

BEGIN
IF EXISTS ( SELECT 0 FROM Inserted )
BEGIN
INSERT INTO dbo.StudentAudit1
(
StudentID,
StuName,
StuFname,
StuGFName,
1
 Gender,
Age,
Nationality ,
ModifiedBy ,
ModifiedDate ,
Operation
)
SELECT
D.StudentID,
D.StuName,
D.StuFname,
D.StuGFName,
D.Gender,
D.Age,
D.Nationality,
@login_name,
GETDATE(),
'U'
FROM Deleted D
END
ELSE
BEGIN
INSERT INTO dbo.StudentAudit1
(
StudentID,
StuName,
StuFname,
StuGFName,
Gender,
Age,
Nationality ,
ModifiedBy ,
ModifiedDate ,
Operation
)
SELECT
D.StudentID,
D.StuName,
D.StuFname,
D.StuGFName,
D.Gender,
D.Age,
D.Nationality,
@login_name,
GETDATE(),
'D'
FROM Deleted D
END
END
ELSE

2
 BEGIN
INSERT INTO dbo.StudentAudit1
(
StudentID,
StuName,
StuFname,
StuGFName,
Gender,
Age,
Nationality ,
ModifiedBy ,
ModifiedDate ,
Operation
)
SELECT
I.StudentID,
I.StuName,
I.StuFname,
I.StuGFName,
I.Gender,
I.Age,
I.Nationality,
@login_name,
GETDATE(),
'I'
FROM Inserted I
END

5. To verify the syntax is valid, on the Query menu, click Parse.(ctrl+F5) If an error message is
returned, compare the statement with the information above and correct as needed and repeat this
step.
6. To create the DML trigger, from the Query menu, click Execute. The DML trigger is created as
an object in the database.
7. To see the DML trigger listed in Object Explorer, right-click Triggers and select Refresh.

Using T-SQL
The simplified SQL syntax to define the trigger is as follows.
CREATE TRIGGER [schema_name.]trigger_name
ON table_name
{FOR | AFTER | INSTEAD OF} {[INSERT] [,] [UPDATE] [,] [DELETE]}
AS
{sql_statements}
 The schema_name is the name of the schema to which the new trigger belongs. The schema
name is optional.
 The rigger_name is the user-defined name for the new trigger.
 The table_name is the table to which the trigger applies.
 The event is listed in the AFTER clause. The event could be INSERT, UPDATE, or DELETE.
A single trigger can fire in response to one or more actions against the table.
 The NOT FOR REPLICATION option instructs SQL Server not to fire the trigger when data
modification is made as part of a replication process.

3
  The sql_statements is one or more Transact-SQL used to carry out actions once an event occurs.

Using T-SQL
USE DBSampleTrigger
CREATE TABLE STUDENT
(
StudentID int PRIMARY KEY ,
StuName Varchar(20) NOT NULL,
StuFname Varchar(20) NOT NULL,
StuGFName Varchar(20) NOT NULL,
Gender Char(1) NOT NULL,
Age int check (Age>=18) NOT NULL,
Nationality Varchar(30) NOT NULL
)
GO
USE DBSampleTrigger
CREATE TABLE StudentAudit
(
AuditID INTEGER NOT NULL IDENTITY(1, 1),
StudentID int NOT NULL ,
StuName Varchar(20) NOT NULL,
StuFname Varchar(20) NOT NULL,
StuGFName Varchar(20) NOT NULL,
Gender Char(1) NOT NULL,
Age int check (Age>=18) NOT NULL,
Nationality Varchar(30) NOT NULL,
ModifiedBy VARCHAR(128) ,
ModifiedDate DATETIME ,
Operation CHAR(1),
PRIMARY KEY CLUSTERED ( AuditID)
)
Go
CREATE TRIGGER TRAuditStudent ON DBSampleTrigger.dbo.STUDENT
FOR INSERT, UPDATE, DELETE
AS
DECLARE @login_name VARCHAR(30)

SELECT @login_name = login_name

FROM sys.dm_exec_sessions
WHERE session_id = @@SPID

IF EXISTS ( SELECT 0 FROM Deleted )

BEGIN
IF EXISTS ( SELECT 0 FROM Inserted )
BEGIN
INSERT INTO dbo.StudentAudit
(
StudentID,
StuName,

4
 StuFname,
StuGFName,
Gender,
Age,
Nationality ,
ModifiedBy ,
ModifiedDate ,
Operation
)
SELECT
D.StudentID,
D.StuName,
D.StuFname,
D.StuGFName,
D.Gender,
D.Age,
D.Nationality,
@login_name,
GETDATE(),
'U'
FROM Deleted D
END
ELSE
BEGIN
INSERT INTO dbo.StudentAudit
(
StudentID,
StuName,
StuFname,
StuGFName,
Gender,
Age,
Nationality ,
ModifiedBy ,
ModifiedDate ,
Operation
)
SELECT
D.StudentID,
D.StuName,
D.StuFname,
D.StuGFName,
D.Gender,
D.Age,
D.Nationality,
@login_name,
GETDATE(),
'D'
FROM Deleted D
END

5
 END
ELSE
BEGIN
INSERT INTO dbo.StudentAudit
(
StudentID,
StuName,
StuFname,
StuGFName,
Gender,
Age,
Nationality ,
ModifiedBy ,
ModifiedDate ,
Operation
)
SELECT
I.StudentID,
I.StuName,
I.StuFname,
I.StuGFName,
I.Gender,
I.Age,
I.Nationality,
@login_name,
GETDATE(),
'I'
FROM Inserted I
END

GO

USE DBSampleTrigger
INSERT INTO STUDENT (StudentID,StuName,StuFname,StuGFName,Gender,Age,Nationality)
VALUES
(1,'EMEBET','WORKU', 'NEGUSSE', 'F', 18, 'Ethiopian'),
(2,'YARED','SEMA','DEMILE','M', 21,'Ethiopian') ,
(3,'YOSEPH','TADELE','ZEWEDE', 'M',23,'Ethiopian')
GO

update DBSampleTrigger.dbo.STUDENT
Set StuFname='yyyy'
where StudentID=1

Delete
From DBSampleTrigger.dbo.STUDENT
where StudentID=2

6
Select *
FRom dbo.StudentAudit

Instead of triggers

use DBSampleTrigger
CREATE TABLE StudentAudit2
(
AuditID INTEGER NOT NULL IDENTITY(1, 1),
StudentID int NOT NULL ,
Action varchar (50),
ModifiedBy VARCHAR(128) ,
ModifiedDate DATETIME ,
PRIMARY KEY CLUSTERED ( AuditID)
)
Go

CREATE TRIGGER TRAuditStudent2 ON DBSampleTrigger.dbo.STUDENT

INSTEAD OF UPDATE
AS
DECLARE @login_name VARCHAR(30)

SELECT @login_name = login_name

FROM sys.dm_exec_sessions
WHERE session_id = @@SPID

BEGIN
SET NOCOUNT ON;

DECLARE @StudentID INT, @StuName VARCHAR(50)

SELECT @StudentID = INSERTED.StudentID,

@StuName = INSERTED.StuName
FROM INSERTED

IF UPDATE(StudentID)
BEGIN
RAISERROR('StudentID cannot be updated.', 16 ,1)
ROLLBACK
INSERT INTO StudentAudit2
VALUES(@StudentID, 'CustomerId cannot be updated.',@login_name,GETDATE())
7
 END
ELSE
BEGIN
UPDATE STUDENT
SET StuName = @StuName
WHERE StudentID = @StudentID
INSERT INTO StudentAudit2
VALUES(@StudentID, 'InsteadOf Update',@login_name,GETDATE())
END
END

INSERT INTO STUDENT (StudentID,StuName,StuFname,StuGFName,Gender,Age,Nationality)

VALUES
(5,'EMEBET','WORKU', 'NEGUSSE', 'F', 18, 'Ethiopian'),
(6,'YARED','SEMA','DEMILE','M', 21,'Ethiopian') ,
(7,'YOSEPH','TADELE','ZEWEDE', 'M',23,'Ethiopian')

update DBSampleTrigger.dbo.STUDENT
Set StuFname='tttt'
where StudentID=6

update DBSampleTrigger.dbo.STUDENT
Set StudentID=9
where StudentID=7

Select *
FRom dbo.StudentAudit2