Using the code below, CREATE 1 Test case and its expected result for the first trigger & CREATE 1 Test case and its expected result for the second trigger. Please include screenshots of test cases with their results.

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

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 record 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;