/*
Triggers pp. 328 - 338
Uses: pp. 329

Triggers implement "active database rules." Triggers are 
the equivalent of click event handlers and code-behind in 
the event-driven paradigm.
There are three types of "click events": 
   INSERT, UPDATE, and DELETE.

Triggers can fire before, after, and instead of an event.

Basic structure:


CREATE/ALTER TRIGGER TriggerName ON TableName
FOR INSERT/UPDATE/DELETE
AS
BEGIN

   SQL statements

END

What about READ? Reads have to be handled with a trace.
In Windows select:
  Start --> All Programs --> Microsoft SQL Server --> Profiler

Or from Enterprise Manager select:
   Tools --> Sql Profiler

Choose SQLprofilerStandard as the template name; click run.

A trace file can be saved as a file or a table in a database
for analysis (e.g., with the index analyzer)
 
*/

USE Northwind

SELECT * FROM Products

/*
One of the major uses of triggers is to implement audit log files.
I will demostrate a row-level audit file. There are many other
types of log files.
At a minimum, the log file should record:
  Who changed the row
  What the row looked like before and after the change
  When the row was changed
  Why the row was changed  

In order to implement the "who" attribute, the affected table
must contain a column that stores the id of the user
who "last" changed a row(s). Parameters cannot be passed to a trigger.
If the information is not in the table, the trigger cannot access it.

I have added a column called "LastChangedBy" to the Products table.
Each change to the Products table should now include a value for the
user who changed the row.

How to enforce that a user id is updated? NOT NULL.

*/

SELECT * FROM Products

ALTER TABLE Products
ADD LastChangedBy INT NULL

ALTER TABLE Products
DROP COLUMN LastChangedBy

CREATE TABLE logTable
(
LogID INT IDENTITY(1,1) PRIMARY KEY,
--AttributeList... 
ChangeTime DATETIME DEFAULT GETDATE(),
ChangeType NVARCHAR(10) NOT NULL, --{INSERT, UPDATE, DELETE}
ChangeImage NVARCHAR(10) NOT NULL, --{BEFORE, AFTER}
ChangedBy INT NOT NULL
)

--logProducts is an example of a "shadow" table.

CREATE TABLE logProducts
(
LogID INT IDENTITY(1,1) PRIMARY KEY,
ProductID INT NULL,
ProductName NVARCHAR(100) NULL,
SupplierID INT NULL,
CategoryID INT NULL,
UnitsOnOrder INT NULL, 
ChangeTime DATETIME DEFAULT GETDATE(),
ChangeType NVARCHAR(10) NOT NULL, --{INSERT, UPDATE, DELETE}
ChangeImage NVARCHAR(10) NOT NULL, --{BEFORE, AFTER}
ChangedBy INT NOT NULL
)

SELECT * FROM LogProducts ORDER BY LogID DESC

/*
Triggers have access to two system maintained 
tables: inserted and deleted
When a new row is INSERTed, 
   the inserted table contains the values of the new row
When a row is DELETEd, 
   the deleted table contains the values of the deleted row
When a row is UPDATEd, 
   the inserted table contains the new values for the row
   the deleted table contains the old values for the row

*/
SELECT * FROM Employees
SELECT * FROM Suppliers
SELECT * FROM Categories
SELECT * FROM Products
SELECT * FROM Products WHERE CategoryID = 7

SELECT * FROM LogProducts ORDER BY LogID DESC

DROP TABLE logProducts

--INSERT
INSERT INTO Products (ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, Reorderlevel, Discontinued, LastChangedBy)
    VALUES('Dried Apples', 7, 3, '5lb box', 12.00, 50, 0, 25, 0, 2)

INSERT INTO Products (ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, Reorderlevel, Discontinued, LastChangedBy)
    VALUES('Kool-aid', 7, 1, 'Individual Packages', 1.30, 500, 0, 25, 0, 4)

SELECT * FROM Products ORDER BY ProductID DESC

DELETE Products WHERE ProductName = 'Dried Apples'
DELETE Products WHERE ProductName = 'Kool-aid'

--DROP TRIGGER trigInsertProduct
 
CREATE TRIGGER trigInsertProduct ON Products
FOR INSERT --Conceptually the event handler for the table's INSERT event
AS
BEGIN
  --This SELECT is for debugging and testing only
  SELECT 'contents of inserted table' AS SysMessage, * FROM inserted
  SELECT 'contents of deleted table' AS SysMessage, * FROM deleted
END

ALTER TRIGGER trigInsertProduct ON Products
FOR INSERT
AS
BEGIN

  INSERT INTO logProducts (ProductID, ProductName, SupplierID, categoryID,
       UnitsOnOrder, ChangeTime, ChangeType, ChangeImage, ChangedBy)
  SELECT ProductID, ProductName, SupplierID, categoryID,
       UnitsOnOrder, GETDATE(), 'INSERT' AS ChangeType, 'AFTER' AS ChangeImage, LastChangedBy
  FROM inserted

  --This SELECT is for debugging and testing only
  SELECT 'contents of inserted table' AS SysMessage, * FROM inserted
END

--Insert the row again...
INSERT INTO Products (ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, Reorderlevel, Discontinued, LastChangedBy)
    VALUES('Dried Apples', 7, 3, '5lb box', 12.00, 50, 0, 25, 0, 3)

SELECT * FROM LogProducts ORDER BY LogID DESC

SELECT * FROM LogProducts
WHERE ProductID = 101 
ORDER BY LogID DESC


SELECT * FROM Products WHERE ProductName = 'Dried Apples'

--UPDATE
--UPDATE Products SET UnitsOnOrder = UnitsOnOrder + 5 WHERE ProductName = 'Dried Apples'
UPDATE Products 
  SET UnitsOnOrder = UnitsOnOrder + 5,
      LastChangedBy = 4 
WHERE ProductName = 'Dried Apples'

DROP TRIGGER trigUpdateProduct

CREATE TRIGGER trigUpdateProduct ON Products
FOR UPDATE
AS
BEGIN

  --These SELECT statements are for debugging and testing only
  SELECT 'contents of inserted table' AS SysMessage, * FROM inserted
  SELECT 'contents of deleted table' AS SysMessage, * FROM deleted
END


ALTER TRIGGER trigUpdateProduct ON Products
FOR UPDATE
AS
BEGIN


  INSERT INTO logProducts (ProductID, ProductName, SupplierID, categoryID,
       UnitsOnOrder, ChangeTime, ChangeType, ChangeImage, ChangedBy)
  SELECT ProductID, ProductName, SupplierID, categoryID,
       UnitsOnOrder, GETDATE(), 'UPDATE' AS ChangeType, 'AFTER' AS ChangeImage, LastChangedBy
  FROM inserted

  INSERT INTO logProducts (ProductID, ProductName, SupplierID, categoryID,
       UnitsOnOrder, ChangeTime, ChangeType, ChangeImage, ChangedBy)
  SELECT ProductID, ProductName, SupplierID, categoryID,
       UnitsOnOrder, GETDATE(), 'UPDATE' AS ChangeType, 'BEFORE' AS ChangeImage, LastChangedBy
  FROM deleted

  --These SELECT statements are for debugging and testing only
  SELECT 'contents of inserted table' AS SysMessage, * FROM inserted
  SELECT 'contents of deleted table' AS SysMessage, * FROM deleted
END

UPDATE Products SET UnitsOnOrder = UnitsOnOrder + 5 WHERE ProductName = 'Dried Apples'

SELECT * FROM LogProducts ORDER BY LogID DESC

/*
Our trigger will work for multiple updates
*/

UPDATE Products SET UnitsOnOrder = UnitsOnOrder + 5 WHERE ProductName IN ('Dried Apples', 'Kool-aid')

SELECT * FROM Products
--DELETE
DELETE Products WHERE ProductName = 'Dried Apples'
DELETE Products WHERE ProductName = 'Kool-aid'


DROP TRIGGER trigDeleteProduct

CREATE TRIGGER trigDeleteProduct ON Products
FOR DELETE
AS
BEGIN

  INSERT INTO logProducts (ProductID, ProductName, SupplierID, categoryID,
       UnitsOnOrder, ChangeTime, ChangeType, ChangeImage, ChangedBy)
  SELECT ProductID, ProductName, SupplierID, categoryID,
       UnitsOnOrder, GETDATE(), 'DELETE' AS ChangeType, 'AFTER' AS ChangeImage, LastChangedBy
  FROM deleted

  --These SELECT statements are for debugging and testing only
  SELECT 'contents of inserted table' AS SysMessage, * FROM inserted
  SELECT 'contents of deleted table' AS SysMessage, * FROM deleted
END

/*
We can then use this audit table to 
   analyze table usage
   confirm ACID properties
   verify a transaction committed
   return a row(s) to a previous state (say un-delete)

*/
--The history of changes to 'Dried Apples'
SELECT * FROM LogProducts WHERE ProductName = 'Dried Apples' ORDER BY ChangeTime Desc

--Confirm that UnitsOnOrder was incremented by 5
--Highlight and run as a single transaction

BEGIN

  DECLARE @BeforeAmount INT, @AfterAmount INT
  DECLARE @ProductName NVARCHAR(250), @ChangeTime DATETIME

  SET @BeforeAmount = (SELECT UnitsOnOrder AS UnitsOnOrderBefore
                       FROM LogProducts
                       WHERE ProductName = 'Dried Apples' AND 
                             ChangeTime = '2006-11-27 16:28:24.390' AND
                             ChangeImage = 'BEFORE' AND
                             ChangeType = 'UPDATE')

  SET @AfterAmount = (SELECT UnitsOnOrder AS UnitsOnOrderBefore
                      FROM LogProducts
                      WHERE ProductName = 'Dried Apples' AND 
                            ChangeTime = '2006-11-27 16:28:24.390' AND
                            ChangeImage = 'AFTER' AND
                            ChangeType = 'UPDATE')

SELECT @AfterAmount - @BeforeAmount AS DeltaAmount

END

/*
Reverse the delete operation
In this case, we are losing some information because the 
row-level audit table we created does not contain all 
attributes from Products. If the audit table were to be 
used to un-delete, it should contain all attributes.

*/

SELECT * FROM LogProducts WHERE ProductName = 'Kool-aid' ORDER BY ChangeTime Desc
SELECT * FROM Products order by productid desc

INSERT INTO Products (ProductName, SupplierID, CategoryID, UnitsOnOrder, LastChangedBy)
  SELECT ProductName, SupplierID, CategoryID, UnitsOnOrder, ChangedBy
  FROM LogProducts
  WHERE ChangeTime = '2007-04-23 13:04:49.810' AND
        ChangeType = 'DELETE'


SELECT * FROM Products WHERE ProductName = 'Dried Apples'
SELECT * FROM LogProducts WHERE ProductName = 'Dried Apples' ORDER BY ChangeTime Desc

/*
Stored procdures or sprocs pp. 338
advantages
http://msdn2.microsoft.com/en-us/library/ms978510.aspx

Basic structure in SQL Server

CREATE/ALTER PROC ProcedureName
@InputParameter...,
@OutputParameter,
AS
BEGIN
   Procedural SQL statements
END

GRANT EXECUTE ON StoredProcedureName TO UserName

EXEC StoredProcedureName paraterlist

Encapsulate INSERT, DELETE, and UPDATE inside an sproc
http://msdn2.microsoft.com/en-us/library/ms978348.aspx


*/

CREATE PROC sprocName
@Parameter1 NVARCHAR(100), 
@Parameter2 INT, 
@Parameter3 NVARCHAR(100) OUTPUT, 
@Parameter4 INT = NULL --Optional parameter
AS
BEGIN

   INSERT INTO TableName (Parameter1, Parameter2, Parameter4)
       VALUES (@Parameter1, @Parameter2, @Parameter4)

END

INSERT INTO Products (ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, Reorderlevel, Discontinued, LastChangedBy)
    VALUES('Dried Apples', 7, 3, '5lb box', 12.00, 50, 0, 25, 0, 3)

DROP PROC InsertProduct

CREATE PROC InsertProduct
@ProductName NVARCHAR(100), 
@SupplierID INT,
@CategoryID INT,
@QuantityPerUnit NVARCHAR(40),
@UnitPrice MONEY,
@UnitsInStock INT,
@UnitsOnOrder INT,
@ReorderLevel INT,
@Discontinued BIT,
@LastChangedBy INT
/*
Created by: Harold Pardue
Create time:
Purpose of sproc: Insert new row into Products table
Last changed:
Reason for change:
*/
AS
BEGIN
  BEGIN TRAN
  --Declare local error handling variables
  DECLARE @ErrorCode INT, @TranSuccessful BIT
  -- 0=false, 1=true
  SET @TranSuccessful = 1

  INSERT INTO Products (ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, 
            UnitsInStock, UnitsOnOrder, Reorderlevel, Discontinued, LastChangedBy)
     VALUES(@ProductName, @SupplierID, @CategoryID, @QuantityPerUnit, @UnitPrice, 
            @UnitsInStock, @UnitsOnOrder, @Reorderlevel, @Discontinued, @LastChangedBy)

  SET @ErrorCode = @@ERROR; IF (@ErrorCode <> 0) SET @TranSuccessful = 0 --False

  IF @TranSuccessful = 0
     BEGIN 
        ROLLBACK TRAN
        RAISERROR('Transaction rolled back...', 16, 1)
     END
  ELSE 
     COMMIT TRAN
END

EXEC InsertProduct 'Mango Lime Pickle', 7, 4, 'Jars',5.66, 500, 0, 2, 0, 4

DELETE Products WHERE ProductName = 'Mango Lime Pickle'

--GRANT EXEC ON InsertProduct TO hpardue

SELECT * FROM LogProducts ORDER BY LogID DESC

SELECT * FROM Products ORDER BY ProductID DESC

UPDATE Products 
SET UnitsOnOrder = UnitsOnOrder + 5 
WHERE ProductName IN ('Dried Apples', 'Kool-aid')

DROP PROC UpdateUnitsOnOrder

CREATE PROC UpdateUnitsOnOrder
@ProductID INT,
@UnitsOnOrderIncrement INT
/*
Created by: Harold Pardue
Create time:
Purpose of sproc: Update UnitsOnOrder for a given row in Products table
Last changed:
Reason for change:
*/
AS
BEGIN
  BEGIN TRAN
  --Declare local error handling variables
  DECLARE @ErrorCode INT, @TranSuccessful BIT
  -- 0=false, 1=true
  SET @TranSuccessful = 1

  UPDATE Products 
  SET UnitsOnOrder = UnitsOnOrder + @UnitsOnOrderIncrement 
  WHERE ProductID = @ProductID
  
  SET @ErrorCode = @@ERROR; IF (@ErrorCode <> 0) SET @TranSuccessful = 0 --False

  IF @TranSuccessful = 0
     ROLLBACK TRAN
  ELSE
     COMMIT TRAN
END

SELECT * FROM Products ORDER BY ProductID DESC

EXEC UpdateUnitsOnOrder 111, 10

DELETE Products WHERE ProductName = 'Mango Lime Pickle'

DROP PROC DeleteProduct

CREATE PROC DeleteProduct
@ProductID INT
/*
Created by: Harold Pardue
Create time:
Purpose of sproc: Delete a row from the Products table
Last changed:
Reason for change:
*/
AS
BEGIN
  BEGIN TRAN
  --Declare local error handling variables
  DECLARE @ErrorCode INT, @TranSuccessful BIT
  -- 0=false, 1=true
  SET @TranSuccessful = 1

  DELETE Products 
  WHERE ProductID = @ProductID
  
  SET @ErrorCode = @@ERROR; IF (@ErrorCode <> 0) SET @TranSuccessful = 0 --False

  IF @TranSuccessful = 0
     ROLLBACK TRAN
  ELSE
     COMMIT TRAN
END

EXEC DeleteProduct 111