Question

Create a table with two columns. Name the table First Initial _ Last Name (e.g. John Dow will create table j_dow). You have to audit all DML statements on your table. To do this you write two triggers: 1. To log any DML statements that users might run on this table. The results must be stored in the First Initial _ Last Name _ Log table (e.g. John Dow will create table j_dow_log). The table should have unique event ID, values for both the Oracle and the system user who ran the query, the time it was executed and the type of DML query user ran. 2. To capture any data that was changed in the table. The results must be stored in the First Initial _ Last Name _ History table (e.g. John Dow will create table j_dow_history). The table should reference the event ID from the log table, and store both old and new values for both columns.

Answer 1

Answer:

Here i have user SQL Server 2012 for creating the sql scripts, tables and views

Below is the SQL query for creating tables j_dow, j_dow_log and j_dow_history

j_dow Table

This table holds the user data and has two columns First Name and Last Name, the script to create j_dow table is given below

CREATE TABLE [dbo].[j_dow](

[Id] [int] IDENTITY(1,1) NOT NULL,

[FirstName] [varchar](100) NULL,

[LastName] [varchar](100) NULL

) ON [PRIMARY]

GO

j_dow_log Table

This table holds the log such as user who triggers and the session both combined in one column EventId (username_session), the time the script executed and the sql script ran by the user

CREATE TABLE [dbo].[j_dow_log](

[EventId] [varchar](150) NULL,

[TimeExecuted] [datetime] NULL,

[QueryCommand] [varchar](max) NULL,

[Id] [int] IDENTITY(1,1) NOT NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

j_dow_history Table

This table holds the log information of INSERT, UPDATE and DELETE. If user do any DML operation in the j_dow table this table records the old value and new value and the column name it get changed

CREATE TABLE [dbo].[j_dow_history](

[Id] [int] IDENTITY(1,1) NOT NULL,

[LogEventId] [varchar](150) NULL,

[OldValue] [varchar](150) NULL,

[NewValue] [varchar](150) NULL,

[ColumnName] [varchar](150) NULL

) ON [PRIMARY]

GO

The select query screen for all three tables are below

Now we have to create trigger for j_dow table one to log the sql query tracking and other to log the data change

The Query is given below

• j_dow_log trigger for recording the time of executing and captures user info who executed it

CREATE TRIGGER [dbo].[j_dow_trigger] ON [dbo].[j_dow]

FOR INSERT, UPDATE, DELETE

AS

DECLARE @TEMP TABLE

(EventType NVARCHAR(30), Parameters INT, EventInfo NVARCHAR(4000))

INSERT INTO @TEMP EXEC('DBCC INPUTBUFFER(@@SPID)')

DECLARE @SQLQuery VARCHAR(MAX)

(SELECT Top 1 @SQLQuery = EventInfo FROM @TEMP)

INSERT INTO j_dow_log (EventId, TimeExecuted, QueryCommand)

SELECT

SUSER_NAME() + '_' + convert(varchar(50), @@spid)

,

CURRENT_TIMESTAMP

,

@SQLQuery

• And the other trigger to reocrd the data change is given below

CREATE TRIGGER [dbo].[j_dow_history_trigger] ON [dbo].[j_dow]

FOR INSERT, UPDATE, DELETE

AS BEGIN

IF @@ROWCOUNT = 0 RETURN;

Declare @MaxEventId INT

SELECT @MaxEventId = MAX(Id) FROM j_dow_log

INSERT INTO j_dow_history

SELECT @MaxEventId,d.FirstName AS [OldValue], i.FirstName AS [NewValue], 'FirstName'

FROM inserted i

FULL OUTER JOIN deleted d ON i.Id = d.Id

INSERT INTO j_dow_history

SELECT @MaxEventId, d.LastName AS [OldValue], i.LastName AS [NewValue], 'LastName'

FROM inserted i

FULL OUTER JOIN deleted d ON i.Id = d.Id

END;

• Once after executing all the scripts that given above the table will look like in the below screen

When trigger executes you can see following messages in output window

Thank you:)