Question

NEED TO ADD COMMENTS ON WHAT EACH PIECE OF SQL CODE DOES

CREATE TABLE Production.ProductAuditTrail
(AuditID INT IDENTITY(1,1),
AuditDate DATETIME NOT NULL,
ChangeUser SYSNAME NOT NULL,
ProductID INT NOT NULL,
BeforeListPrice MONEY NOT NULL,
AfterListPrice MONEY NOT NULL)
GO

CREATE TRIGGER tu_ProductAuditTrail
ON Production.Product
FOR UPDATE
AS
INSERT INTO Production.ProductAuditTrail
(AuditDate, ChangeUser, ProductID, BeforeListPrice, AfterListPrice)
SELECT GETDATE(), SUSER_SNAME(), i.ProductID, d.ListPrice, i.ListPrice
FROM inserted i INNER JOIN deleted d ON i.ProductID = d.ProductID
GO

SELECT * FROM Production.ProductAuditTrail
GO
UPDATE Production.Product
SET ListPrice = ListPrice + 1
WHERE ProductID = 514
GO
SELECT * FROM Production.ProductAuditTrail
GO

Answer 1

CREATE TABLE Production.ProductAuditTrail   //Production is database name and ProductAuditTrail is a table that has to be created in that DB.
(AuditID INT IDENTITY(1,1),     // A SQL Server IDENTITY column is a special type of column that is used                                              to automatically generate key values based on a provided seed (starting point) and        increment
AuditDate DATETIME NOT NULL, //Date and time in YYYY-MM-DD HH:MI:SS format

ChangeUser SYSNAME NOT NULL, // SYSNAME is a system-supplied user-defined data type that is functionally equivalent to nvarchar(128), except that it is not nullable. sysname is used to reference database object names.

ProductID INT NOT NULL,
BeforeListPrice MONEY NOT NULL,

AfterListPrice MONEY NOT NULL) // The money data type is an abstract data type. Money values are stored significant to two decimal places. These values are rounded to their amounts in dollars and cents or other currency units on input and output, and arithmetic operations on the money data type retain two-decimal-place precision.

CREATE TRIGGER tu_ProductAuditTrail   

ON Production.Product    

FOR UPDATE       // triggers are special stored procedures that are executed automatically in response to the database object, database, and server events. Here there is a trigger on the Production.Product table when the table is updated.

AS
INSERT INTO Production.ProductAuditTrail
(AuditDate, ChangeUser, ProductID, BeforeListPrice, AfterListPrice)
SELECT GETDATE(), SUSER_SNAME(), i.ProductID, d.ListPrice, i.ListPrice
FROM inserted i INNER JOIN deleted d ON i.ProductID = d.ProductID       // Triggering condition for the table tbased on ProductID when insertion and deletion is taken place

SELECT * FROM Production.ProductAuditTrail        // lists out the records for all the columns in Production.ProductAuditTrail table

UPDATE Production.Product
SET ListPrice = ListPrice + 1
WHERE ProductID = 514      //Updates the Production.Product table by setting ListPrice = ListPrice + 1 where ProductID=514.

SELECT * FROM Production.ProductAuditTrail // lists out the records for all the columns in Production.ProductAuditTrail table