Triggers
-----------------------------------------------------------
-- Total Training for SQL Server 2005
--
-- Part 3 - T-SQL Programming
-- Lesson 2 - Triggers and Audit Trail
--
-- Paul Nielsen
-----------------------------------------------------------
-----------------------------------------------------------
-- Introducing After Triggers
USE FAMILY
go
CREATE TRIGGER TriggerOne ON Person
AFTER Insert -- old syntax FOR
AS
PRINT 'In the After Trigger'
go
INSERT Person(PersonID, LastName, FirstName, Gender)
VALUES (50, 'Flanigan', 'Brandie','F')
-----------------------------------------------------------------------
-- Using "Instead of" Triggers
USE FAMILY
CREATE TRIGGER TriggerTwo ON Person
INSTEAD OF Insert
AS
PRINT 'In the Instead-of Trigger'
go
INSERT Person(PersonID, LastName, FirstName, Gender)
VALUES (99, 'McDoogle', 'John','M')
SELECT *
FROM Person
WHERE PersonID = 99
DROP TRIGGER TriggerTwo
/*
Transaction Flow
1 - Identity Insert Check
2 - Nullability Constraint
3 - Data Type Check
4 - Instead of Trigger
5 - Primary Key Constraint
6 - Check Constraints
7 - Foreign Key Constraint
8 - DML execution and update to Transaction Log
9 - After Trigger
10 - Commit Transaction
*/
--------------------------------------------------------
-- Disabling Triggers
ALTER TABLE Person
DISABLE TRIGGER TriggerOne
SELECT OBJECTPROPERTY(OBJECT_ID('TriggerOne'),'ExecIsTriggerDisabled')
ALTER TABLE Person
ENABLE TRIGGER TriggerOne
SELECT OBJECTPROPERTY(OBJECT_ID('TriggerOne'),'ExecIsTriggerDisabled')
-- Viewing Trigger Status
SELECT SubString(S2.Name,1,30) as [Table],
SubString(S.Name, 1,30) as [Trigger],
CASE (SELECT -- Correlated subquery
OBJECTPROPERTY(OBJECT_ID(S.Name),
'ExecIsTriggerDisabled'))
WHEN 0 THEN 'Enabled'
WHEN 1 THEN 'Disabled'
END AS Status
FROM Sysobjects S
JOIN Sysobjects S2
ON S.parent_obj = S2.ID
WHERE S.Type = 'TR'
ORDER BY [Table], [Trigger]
-------------------------------------------------------
-- Using the Update() function
ALTER TRIGGER TriggerOne ON Person
AFTER Insert, Update
AS
IF Update(LastName)
PRINT 'You modified the LastName column'
ELSE
PRINT 'The LastName column is untouched.'
UPDATE Person
SET LastName = 'Johnson'
WHERE PersonID = 25
UPDATE Person
SET FirstName = 'Joe'
WHERE PersonID = 25
-----------------------------------------------
-- Selecting Transaction Data
USE Family
ALTER TRIGGER TriggerOne ON Person
AFTER Insert, Update
AS
SET NoCount ON
IF Update(LastName)
SELECT 'You modified the LastName column to ' + Inserted.LastName
FROM Inserted
UPDATE Person
SET LastName = 'Johnson'
WHERE PersonID = 32
ALTER TRIGGER TriggerOne ON Person
AFTER Insert, Update
AS
SELECT CAST(D.PersonID as VARCHAR(4)) + ': ' + D.LastName + ' changed to ' + I.LastName
FROM Inserted I
JOIN Deleted D
ON I.PersonID = D.PersonID
UPDATE Person
SET LastName = 'Carter'
WHERE LastName = 'Johnson'
-------------------------------------------------
-- Nesting and Recursion
-- Nested - Server wide setting
EXEC sp_configure 'Nested Triggers', 1
Reconfigure
-- Recursive Trigger - Database wide setting
USE OBXKites
ALTER DATABASE OBXKites SET RECURSIVE_TRIGGERS ON
ALTER TABLE Product
ADD Created DATETIME NOT NULL DEFAULT GetDate()
ALTER TABLE Product
ADD Modified DATETIME NOT NULL DEFAULT GetDate()
-- assumes created and modified date columns have been added to the Product table.
CREATE TRIGGER Products_ModifiedDate ON dbo.Product
AFTER UPDATE
AS
SET NoCount ON
-- Check nest level
PRINT Trigger_NestLevel() -- for demo purposes, remove from production code
If Trigger_NestLevel() > 1
RETURN
-- block manual update of created/modified columns
IF (UPDATE(Created) or UPDATE(Modified))
AND Trigger_NestLevel() = 1
BEGIN
RAISERROR('Update failed.', 16, 1)
ROLLBACK
RETURN
END
-- Update the Modified date
UPDATE Product
SET modified = getdate()
FROM Product
JOIN Inserted
ON Product.ProductID = Inserted.ProductID
-- end of trigger
go
UPDATE PRODUCT
SET ProductName = 'Modifed Trigger'
WHERE Code = '1002'
SELECT Code, ProductName, Created, Modified
FROM Product
WHERE Code = '1002'
--------------------------------------------------------
-- Enforcing Complex RI
-- Complex Business Rules Validation
-- Custom RI
-- ensure the guide's qual date is good and the revoke date is null
-- for the given guideID and TourID
USE CHA2
go
CREATE TRIGGER LeadQualied ON Event_mm_Guide
AFTER INSERT, UPDATE
AS
SET NoCount ON
IF EXISTS(
SELECT *
FROM Inserted
JOIN dbo.Event
ON Inserted.EventID = Event.EventID
LEFT JOIN dbo.Tour_mm_Guide
ON Tour_mm_Guide.TourID = Event.TourID
AND Inserted.GuideID = Tour_mm_Guide.GuideID
WHERE
Inserted.IsLead = 1
AND
(QualDate > Event.DateBegin
OR
RevokeDate IS NOT NULL
OR
QualDate IS NULL )
)
BEGIN
RAISERROR('Lead Guide is not Qualified.',16,1)
ROLLBACK TRANSACTION
END
go
-- test
INSERT Event_mm_Guide (EventID, GuideID, IsLead)
VALUES (10, 1, 1)
INSERT Event_mm_Guide (EventID, GuideID, IsLead)
VALUES (10, 2, 1)
-------------------------------------------------------
-- Auditing Data Changes
USE OBXKites
CREATE TABLE dbo.Audit (
AuditID UNIQUEIDENTIFIER RowGUIDCol NOT NULL
CONSTRAINT DF_Audit_AuditID DEFAULT (NEWID())
CONSTRAINT PK_Audit PRIMARY KEY NONCLUSTERED (AuditID),
AuditDate DATETIME NOT NULL,
SysUser VARCHAR(50) NOT NULL,
Application VARCHAR(50) NOT NULL,
TableName VARCHAR(50)NOT NULL,
Operation CHAR(1) NOT NULL,
PrimaryKey VARCHAR(50) NOT NULL,
RowDescription VARCHAR(50) NULL,
SecondaryRow VARCHAR(50) NULL,
[Column] VARCHAR(50) NOT NULL,
OldValue VARCHAR(50) NULL,
NewValue VARCHAR(50) NULL
)
--------------------------------------------------------------
-- Fixed Audit Trail Trigger
Use OBXKites
Go
CREATE TRIGGER Product_Audit
ON dbo.Product
AFTER Insert, Update
NOT FOR REPLICATION
AS
DECLARE @Operation CHAR(1)
IF EXISTS(SELECT * FROM Deleted)
SET @Operation = 'U'
ELSE
SET @Operation = 'I'
IF UPDATE(ProductCategoryID)
INSERT dbo.Audit
(AuditDate, SysUser, Application, TableName, Operation,
PrimaryKey, RowDescription, SecondaryRow, [Column],
OldValue, NewValue)
SELECT GetDate(), suser_sname(), APP_NAME(), 'Product', @Operation,
Inserted.ProductID, Inserted.Code, NULL, 'ProductCategoryID',
OPC.ProductCategoryName, NPC.ProductCategoryName
FROM Inserted
LEFT OUTER JOIN Deleted
ON Inserted.ProductID = Deleted.ProductID
AND Inserted.ProductCategoryID
<> Deleted.ProductCategoryID
-- fetch ProductCategory Names
LEFT OUTER JOIN dbo.ProductCategory OPC
ON Deleted.ProductCategoryID = OPC.ProductCategoryID
JOIN dbo.ProductCategory NPC
ON Inserted.ProductCategoryID = NPC.ProductCategoryID
IF UPDATE(Code)
INSERT dbo.Audit
(AuditDate, SysUser, Application, TableName, Operation,
PrimaryKey, RowDescription, SecondaryRow, [Column],
OldValue, NewValue)
SELECT GetDate(), suser_sname(), APP_NAME(), 'Product', @Operation,
Inserted.ProductID, Inserted.Code, NULL, 'Code',
Deleted.Code, Inserted.Code
FROM Inserted
LEFT OUTER JOIN Deleted
ON Inserted.ProductID = Deleted.ProductID
AND Inserted.Code <> Deleted.Code
IF UPDATE(ProductName)
INSERT dbo.Audit
(AuditDate, SysUser, Application, TableName, Operation,
PrimaryKey, RowDescription, SecondaryRow, [Column],
OldValue, NewValue)
SELECT GetDate(), suser_sname(), APP_NAME(), 'Product', @Operation,
Inserted.ProductID, Inserted.Code, NULL, 'Name',
Deleted.ProductName, Inserted.ProductName
FROM Inserted
LEFT OUTER JOIN Deleted
ON Inserted.ProductID = Deleted.ProductID
AND Inserted.ProductName <> Deleted.ProductName
IF UPDATE(ProductDescription)
INSERT dbo.Audit
(AuditDate, SysUser, Application, TableName, Operation,
PrimaryKey, RowDescription, SecondaryRow, [Column],
OldValue, NewValue)
SELECT GetDate(), suser_sname(), APP_NAME(), 'Product', @Operation,
Inserted.ProductID, Inserted.Code, NULL, 'ProductDescription',
Deleted.ProductDescription, Inserted.ProductDescription
FROM Inserted
LEFT OUTER JOIN Deleted
ON Inserted.ProductID = Deleted.ProductID
AND Inserted.ProductDescription <> Deleted.ProductDescription
IF UPDATE(ActiveDate)
INSERT dbo.Audit
(AuditDate, SysUser, Application, TableName, Operation,
PrimaryKey, RowDescription, SecondaryRow, [Column],
OldValue, NewValue)
SELECT GetDate(), suser_sname(), APP_NAME(), 'Product', @Operation,
Inserted.ProductID, Inserted.Code, NULL, 'ActiveDate',
Deleted.ActiveDate, Inserted.ActiveDate
FROM Inserted
LEFT OUTER JOIN Deleted
ON Inserted.ProductID = Deleted.ProductID
AND Inserted.ActiveDate != Deleted.ActiveDate
IF UPDATE(DiscontinueDate)
INSERT dbo.Audit
(AuditDate, SysUser, Application, TableName, Operation,
PrimaryKey, RowDescription, SecondaryRow, [Column],
OldValue, NewValue)
SELECT GetDate(), suser_sname(), APP_NAME(), 'Product', @Operation,
Inserted.ProductID, Inserted.Code, NULL, 'DiscontinueDate',
Deleted.DiscontinueDate, Inserted.DiscontinueDate
FROM Inserted
LEFT OUTER JOIN Deleted
ON Inserted.ProductID = Deleted.ProductID
AND Inserted.DiscontinueDate != Deleted.DiscontinueDate
go
-- Test the Fixed Audit Trail
EXEC pProduct_AddNew 'Kite', 200, 'The MonstaKite', 'Man what a big Kite!'
SELECT TableName, RowDescription, [Column], NewValue
FROM Audit
SELECT *
FROM Audit
UPDATE dbo.Product
SET ProductDescription = 'Biggie Sized'
WHERE Code = 200
SELECT AuditDate, OldValue, NewValue
FROM dbo.Audit
WHERE TableName = 'Product'
AND RowDescription = '200'
AND [Column] = 'ProductDescription'
--Rolling back an Audit Trail change
CREATE PROCEDURE pAudit_RollBack (
@AuditID UNIQUEIDENTIFIER)
AS
SET NoCount ON
DECLARE
@SQLString NVARCHAR(4000),
@TableName NVARCHAR(50),
@PrimaryKey NVARCHAR(50),
@Column NVARCHAR(50),
@NewValue NVARCHAR(50)
SELECT
@TableName = TableName,
@PrimaryKey = PrimaryKey,
@Column = [Column],
@NewValue = OldValue
FROM dbo.Audit
WHERE AuditID = @AuditID
SET @SQLString =
'UPDATE ' + @TableName
+ ' SET ' + @Column + ' = ''' + @NewValue +''''
+ ' WHERE ' + @TableName + 'ID = ''' + @PrimaryKey + ''''
EXEC sp_executeSQL @SQLString
go
--Test RollBack
DECLARE @AuditRollBack UNIQUEIDENTIFIER
SELECT @AuditRollBack = AuditID
FROM dbo.Audit
WHERE TableName = 'Product'
AND RowDescription = '200'
AND OldValue = 'Man what a big Kite!'
EXEC pAudit_RollBack @AuditRollBack
SELECT ProductDescription
FROM dbo.Product
WHERE Code = 200
0 Response to "Triggers"
Post a Comment