/*
Author: Scott Whigham from
http://www.LearnSqlServer.com/ Description: This script demos how to use triggers to for data validation. Since it uses INSTEAD OF triggers, it will
only run on SQL Server 2000 and above. You'll need the Northwind database to work with this script.
Misc Notes: This is neat for debugging/development since it shows you the problems in your SQL. It isn't a recommended
solution for production work.
Versions: SQL Server 2005, 2000
Creation Date: August 28, 2006
For more scripts like this one, visit
http://forums.learnsqlserver.com/codesamples.aspx*/
USE Northwind
GO
SET NOCOUNT ON
GO
--CREATE the OrderDetailsLog table
CREATE TABLE OrderDetailsLog (
LogID INT NOT NULL IDENTITY PRIMARY KEY,
LogDate datetime NOT NULL DEFAULT GETDATE(),
Login sysname NOT NULL DEFAULT SUSER_SNAME(),
Host sysname NOT NULL DEFAULT HOST_NAME(),
App sysname NOT NULL DEFAULT APP_NAME(),
OrderID INT NOT NULL,
ProductID INT NOT NULL,
Quantity INT NOT NULL,
Remarks VARCHAR(100)
)
GO
CREATE TRIGGER tr_OrderDetails_iofi_validate ON OrderDetails
INSTEAD OF INSERT
AS
--INSTEAD OF Trigger Logs Invalid Rows and Accepts Only Valid Rows
IF @@rowcount = 0 RETURN
-- Violation of PRIMARY KEY
IF EXISTS(SELECT *
FROM inserted AS I JOIN OrderDetails AS OD
ON I.OrderID = OD.OrderID AND I.ProductID = OD.ProductID)
INSERT INTO OrderDetailsLog(OrderID, ProductID, Quantity, Remarks)
SELECT I.*, 'Violation of PRIMARY KEY' AS Remarks
FROM inserted AS I JOIN OrderDetails AS OD
ON I.OrderID = OD.OrderID AND I.ProductID = OD.ProductID
-- Violation of FOREIGN KEY to Orders
IF EXISTS(SELECT * FROM inserted AS I
WHERE NOT EXISTS(SELECT * FROM Orders AS O
WHERE O.OrderID = I.OrderID))
INSERT INTO OrderDetailsLog(OrderID, ProductID, Quantity, Remarks)
SELECT *, 'Violation of FOREIGN KEY to Orders' AS Remarks
FROM inserted AS I
WHERE NOT EXISTS(SELECT * FROM Orders AS O
WHERE O.OrderID = I.OrderID)
-- Violation of FOREIGN KEY to Products
IF EXISTS(SELECT * FROM inserted AS I
WHERE NOT EXISTS(SELECT * FROM Products AS P
WHERE P.ProductID = I.ProductID))
INSERT INTO OrderDetailsLog(OrderID, ProductID, Quantity, Remarks)
SELECT *, 'Violation of FOREIGN KEY to Products' AS Remarks
FROM inserted AS I
WHERE NOT EXISTS(SELECT * FROM Products AS P
WHERE P.ProductID = I.ProductID)
-- Violation of CHECK (Quantity > 0
IF EXISTS(SELECT * FROM inserted
WHERE Quantity <= 0)
INSERT INTO OrderDetailsLog(OrderID, ProductID, Quantity, Remarks)
SELECT *, 'Violation of CHECK (Quantity > 0)' AS Remarks
FROM inserted
WHERE Quantity <= 0
-- Insert only valid rows to the OrderDetails table
INSERT INTO OrderDetails
SELECT * FROM Inserted AS I
WHERE
NOT EXISTS(SELECT * FROM OrderDetails AS OD
WHERE OD.OrderID = I.OrderID
AND OD.ProductID = I.ProductID)
AND
EXISTS(SELECT * FROM Orders AS O WHERE O.OrderID = I.OrderID)
AND
EXISTS(SELECT * FROM Products AS P WHERE P.ProductID = I.ProductID)
AND
Quantity > 0
GO
--Test Inserts into the OrderDetails Table. After each INSERT, look in the OrderDetailsLog table
INSERT INTO OrderDetails VALUES(10248, 1, 10) -- Successful
INSERT INTO OrderDetails VALUES(10248, 1, 10) -- Violates PK
INSERT INTO OrderDetails VALUES(10247, 1, 10) -- Violates FK to Orders
INSERT INTO OrderDetails VALUES(10248, 0, 10) -- Violates FK to Products
INSERT INTO OrderDetails VALUES(10248, 2, -1) -- Violates CHECK
INSERT INTO OrderDetails VALUES(10247, 0, -1) -- Violates both FKs and also CHECK
--Script to Add Rows to the OrderDetails Table
INSERT INTO OrderDetails VALUES(10248, 2, 20)
INSERT INTO OrderDetails VALUES(10248, 3, 30)
INSERT INTO OrderDetails VALUES(10249, 1, 5)
INSERT INTO OrderDetails VALUES(10249, 2, 10)
INSERT INTO OrderDetails VALUES(10249, 3, 15)
INSERT INTO OrderDetails VALUES(10250, 1, 20)
INSERT INTO OrderDetails VALUES(10250, 2, 20)
INSERT INTO OrderDetails VALUES(10250, 3, 20)
select * from OrderDetailsLog table
