/*
ISC 561
ERD, 
Transforming data models into database design,
Database implementation (SQL-DDL/DML)

This sql script contains DDL and Transact-SQL 
to illustrate TRIGGERs

pp. 275-281, 393-407

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.

Basic form of a trigger

CREATE TRIGGER Trigger_Name ON Table|View
FOR INSERT, UPDATE, DELETE
AS 
  BEGIN
    Condition
    Action
  END

Let's do some simple examples first...

DROP TRIGGER trigInsertDemo
DROP TRIGGER trigUpdateDemo
DROP TRIGGER trigDeleteDemo
DROP TABLE tblDemoTrigger
DROP TABLE RowLevelAuditTblDemoTrigger

*/
USE Northwind


--DROP TABLE tblDemoTrigger
CREATE TABLE tblDemoTrigger
(
SurrogateKey INT IDENTITY(1,1) PRIMARY KEY,
Attribute1 INT,
Attribute2 NVARCHAR(500),
Attribute3 DATETIME,
)

--Single row CRUD operation
INSERT INTO tblDemoTrigger (Attribute1, Attribute2, Attribute3)
   VALUES (10, 'string1', '2006-10-31')

SELECT * FROM tblDemoTrigger

--Create an INSERT trigger
--DROP TRIGGER trigInsertDemo
CREATE TRIGGER trigInsertDemo ON tblDemoTrigger
FOR INSERT
AS
BEGIN
  /*
   Echo the contents of the "inserted" table
   The "inserted" table contains the values of the newly inserted row
   in tblDemoTrigger or the "after-image".
  */
  SELECT 'inserted' AS Buffer, inserted.* FROM inserted
  --Action...

END

INSERT INTO tblDemoTrigger (Attribute1, Attribute2, Attribute3)
   VALUES (20, 'string2', '2006-10-31')

SELECT * FROM tblDemoTrigger

UPDATE tblDemoTrigger SET Attribute1 = 200 WHERE SurrogateKey = 1

--Create an UPDATE trigger
--DROP TRIGGER trigUpdateDemo
CREATE TRIGGER trigUpdateDemo ON tblDemoTrigger
FOR UPDATE
AS
BEGIN
  /*
   Echo the contents of the "inserted" and "deleted" tables
   The "inserted" table contains the after-image of the updated row.
   The "deleted" table contains the before-image of the updated row.
  */
  SELECT 'deleted' AS Buffer, deleted.*, 'before' AS RowImage FROM deleted
  SELECT 'inserted' AS Buffer, inserted.*, 'after' AS RowImage FROM inserted
  --Action...
END

UPDATE tblDemoTrigger SET Attribute1 = 30 WHERE SurrogateKey = 2

SELECT * FROM tblDemoTrigger

--Create a DELETE trigger
--DROP TRIGGER trigDeleteDemo
CREATE TRIGGER trigDeleteDemo ON tblDemoTrigger
FOR DELETE
AS
BEGIN
  /*
   Echo the contents of the "deleted" table
   The "deleted" table contains the values of the deleted row.
  */
  SELECT 'deleted' AS Buffer, deleted.*, 'before' AS RowImage FROM deleted
  --Action...
END

DELETE tblDemoTrigger WHERE SurrogateKey = 2

SELECT * FROM tblDemoTrigger

--Multi-row CRUD operation
--Insert values from Orders in the Northwind database as an example
INSERT INTO tblDemoTrigger (Attribute1, Attribute2, Attribute3)
   SELECT EmployeeID, CustomerID, OrderDate FROM Orders WHERE OrderID < 10256

SELECT * FROM tblDemoTrigger

--Update ALL rows that contain the word "software" in the keywords
--Set the Attribute1 to 888

SELECT * FROM tblDemoTrigger WHERE Attribute2 LIKE '%HA%'

UPDATE tblDemoTrigger SET Attribute1 = 888 WHERE Attribute2 LIKE '%SU%'

/*
Using a trigger to implement a Row-Level Audit table.
See http://www.ddj.com/showArticle.jhtml;jsessionid=33ZCMV5XELMXYQSNDLOSKH0CJUNN2JVN?articleID=184406340

Perhaps the biggest issue for database is that the need for "data quality, 
data integrity, and accuracy of IT systems" has been legislated.



*/

SELECT * FROM tblDemoTrigger

--Create row-level audit table for tblDemoTrigger
--DROP TABLE RowLevelAuditTblDemoTrigger
CREATE TABLE RowLevelAuditTblDemoTrigger
(
AuditLogID INT IDENTITY(1,1) PRIMARY KEY,
SurrogateKey INT,
Attribute1 INT,
Attribute2 NVARCHAR(500),
Attribute3 DATETIME,
ChangedBy INT,                         --PK of person making change
ChangeTime DATETIME DEFAULT GETDATE(), --Time change occurred
ChangeType NVARCHAR(10),               --INSERT, UPDATE, DELETE
ImageType NVARCHAR(10)                 --BEFORE, AFTER     
)
SELECT * FROM tblDemoTrigger
SELECT * FROM RowLevelAuditTblDemoTrigger

/*
Where will the ChangedBy attribute come from?
Todd Schraml in the ddj article suggests we include LastChangedBy
in the base table tblDemoTrigger. Every time a row is udated,
the LastChangedBy is updated.
*/

SELECT * FROM tblDemoTrigger

ALTER TABLE tblDemoTrigger
ADD LastChangedBy INT

--There are no "users" in our demo table so I'll just make up a PK value
UPDATE tblDemoTrigger SET LastChangedBy = 23

INSERT INTO tblDemoTrigger (Attribute1, Attribute2, Attribute3, LastChangedBy)
   VALUES (50, 'string5', '2006-10-31', 45)

SELECT * FROM tblDemoTrigger

--Alter the existing TRIGGER to include an INSERT statement into the row-level audit table
--DROP TRIGGER trigInsertDemo
ALTER TRIGGER trigInsertDemo ON tblDemoTrigger
FOR INSERT
AS
BEGIN
  /*
   Echo the contents of the "inserted" table
   The "inserted" table contains the values of the newly inserted row
   in tblDemoTrigger or the "after-image".
  */
  SELECT 'inserted' AS Buffer, inserted.* FROM inserted
  --Action...
  INSERT INTO RowLevelAuditTblDemoTrigger (SurrogateKey, Attribute1, Attribute2, Attribute3, ChangedBy, ChangeTime, ChangeType, ImageType)
    SELECT SurrogateKey, Attribute1, Attribute2, Attribute3, LastChangedBy, GETDATE() AS ChangeTime, 'INSERT' AS ChangeType, 'AFTER' AS ImageType
    FROM inserted

END

INSERT INTO tblDemoTrigger (Attribute1, Attribute2, Attribute3, LastChangedBy)
   VALUES (60, 'string6', '2006-10-31', 45)

SELECT * FROM tblDemoTrigger
SELECT * FROM RowLevelAuditTblDemoTrigger

--Return creation time and record
SELECT * FROM RowLevelAuditTblDemoTrigger WHERE SurrogateKey = 12 AND ChangeType = 'INSERT'



--Alter the existing TRIGGER to include an INSERT statement into the row-level audit table
--DROP TRIGGER trigUpdateDemo
ALTER TRIGGER trigUpdateDemo ON tblDemoTrigger
FOR UPDATE
AS
BEGIN
  /*
   Echo the contents of the "inserted" and "deleted" tables
   The "inserted" table contains the after-image of the updated row.
   The "deleted" table contains the before-image of the updated row.
  */
  SELECT 'deleted' AS Buffer, deleted.*, 'before' AS RowImage FROM deleted
  SELECT 'inserted' AS Buffer, inserted.*, 'after' AS RowImage FROM inserted
  --Action...
  INSERT INTO RowLevelAuditTblDemoTrigger (SurrogateKey, Attribute1, Attribute2, Attribute3, ChangedBy, ChangeTime, ChangeType, ImageType)
    SELECT SurrogateKey, Attribute1, Attribute2, Attribute3, LastChangedBy, GETDATE() AS ChangeTime, 'UPDATE' AS ChangeType, 'BEFORE' AS ImageType
    FROM deleted

  INSERT INTO RowLevelAuditTblDemoTrigger (SurrogateKey, Attribute1, Attribute2, Attribute3, ChangedBy, ChangeTime, ChangeType, ImageType)
    SELECT SurrogateKey, Attribute1, Attribute2, Attribute3, LastChangedBy, GETDATE() AS ChangeTime, 'UPDATE' AS ChangeType, 'AFTER' AS ImageType
    FROM inserted

END

UPDATE tblDemoTrigger 
SET Attribute1 = 30,
    LastChangedBy = 23 
WHERE SurrogateKey = 12

SELECT * FROM tblDemoTrigger
SELECT * FROM RowLevelAuditTblDemoTrigger

--Alter the existing TRIGGER to include an INSERT statement into the row-level audit table
--DROP TRIGGER trigDeleteDemo
ALTER TRIGGER trigDeleteDemo ON tblDemoTrigger
FOR DELETE
AS
BEGIN
  /*
   Echo the contents of the "deleted" table
   The "deleted" table contains the values of the deleted row.
  */
  SELECT 'deleted' AS Buffer, deleted.*, 'before' AS RowImage FROM deleted
  --Action...

  INSERT INTO RowLevelAuditTblDemoTrigger (SurrogateKey, Attribute1, Attribute2, Attribute3, ChangedBy, ChangeTime, ChangeType, ImageType)
    SELECT SurrogateKey, Attribute1, Attribute2, Attribute3, LastChangedBy, GETDATE() AS ChangeTime, 'DELETE' AS ChangeType, 'BEFORE' AS ImageType
    FROM deleted

END

DELETE tblDemoTrigger WHERE SurrogateKey = 12

SELECT * FROM tblDemoTrigger
SELECT * FROM RowLevelAuditTblDemoTrigger WHERE SurrogateKey = 12
--Essentially, this provides a record of the entire life cycle or life span of a record.