IF EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tr_Main]') AND Type IN (N'TR')) BEGIN DROP TRIGGER [dbo].[tr_Main] END GO CREATE TRIGGER [dbo].[tr_Main] ON [dbo].[Main] AFTER INSERT,DELETE,UPDATE AS BEGIN IF EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted) BEGIN --Action = 'UPDATE' END ELSE IF EXISTS(SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted) BEGIN --Action = 'INSERT' END ELSE IF NOT EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted) BEGIN --Action = 'DELETE' END END
As for the body of my Triggers, Database tables changes (add new columns) because of new software requirements, I wanted to make it as simple and easy as possible so that minimal work or non at all is required on the SQL Triggers should the SQL Table change. What I did was, I've created a SQL Stored Procedure that will handle all my data checks and data warehouse updates. On the Triggers, I've converted the records on the inserts/updates and deleted results into a XML format and send the value to the Stored Procedure. You can convert the results to XML by using the following syntax:
--Action = 'UPDATE' SELECT @Xml = (SELECT (SELECT * FROM deleted FOR XML PATH(''),TYPE, ELEMENTS) AS DELETED, (SELECT * FROM inserted FOR XML PATH(''),TYPE, ELEMENTS) AS INSERTED FROM Main AS MAIN WHERE ID = @ID FOR XML AUTO, ELEMENTS)
I've also send through a status variable so that the SQL Stored Procedure knows if it was an INSERT, UPDATE or DELETE. The Stored Procedure also required the SQL Database Table name, Primary Key column name and the Primary Key value. That combination will be used on the SQL Stored Procedure to determine which data checks and data warehouse updates to do. Below is an example of my SQL Trigger:
IF EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tr_Main]') AND Type IN (N'TR')) BEGIN DROP TRIGGER [dbo].[tr_Main] END GO CREATE TRIGGER [dbo].[tr_Main] ON [dbo].[Main] AFTER INSERT,DELETE,UPDATE AS BEGIN DECLARE @temp_InsertedOrDelete AS TABLE (ID INT) DECLARE @Action AS NVARCHAR(10) = '' DECLARE @Table AS NVARCHAR(255) = '' DECLARE @Column AS NVARCHAR(255) = '' DECLARE @ID AS INT = 0 DECLARE @Xml AS XML SET @Table = 'Main' SET @Column = 'ID' IF EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted) BEGIN SET @Action = 'UPDATE' INSERT INTO @temp_InsertedOrDelete (ID) SELECT ISNULL(ID,0) FROM inserted WHILE (SELECT COUNT(ID) FROM @temp_InsertedOrDelete) <> 0 BEGIN SELECT TOP 1 @ID = ISNULL(ID,0) FROM @temp_InsertedOrDelete SELECT @Xml = (SELECT (SELECT * FROM deleted FOR XML PATH(''),TYPE, ELEMENTS) AS DELETE, (SELECT * FROM inserted FOR XML PATH(''),TYPE, ELEMENTS) AS INSERT FROM Main AS MAIN WHERE ID = @ID FOR XML AUTO, ELEMENTS) ---------------------------------- --UPDATE THE DATAWAREHOUSE BEGIN-- ---------------------------------- -------------------------------- --UPDATE THE DATAWAREHOUSE END-- -------------------------------- DELETE FROM @temp_InsertedOrDelete WHERE ISNULL(ID,0) = @ID END END ELSE IF EXISTS(SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted) BEGIN SET @Action = 'INSERT' INSERT INTO @temp_InsertedOrDelete (ID) SELECT ISNULL(ID,0) FROM inserted WHILE (SELECT COUNT(ID) FROM @temp_InsertedOrDelete) <> 0 BEGIN SELECT TOP 1 @ID = ISNULL(ID,0) FROM @temp_InsertedOrDelete SELECT @Xml = (SELECT (SELECT * FROM inserted FOR XML PATH(''),TYPE, ELEMENTS) AS INSERT FROM Main AS MAIN WHERE ID = @ID FOR XML AUTO, ELEMENTS) --------------------------------------- --INSERT INTO THE DATAWAREHOUSE BEGIN-- --------------------------------------- ------------------------------------- --INSERT INTO THE DATAWAREHOUSE END-- ------------------------------------- DELETE FROM @temp_InsertedOrDelete WHERE ISNULL(ID,0) = @ID END END ELSE IF NOT EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted) BEGIN SET @Action = 'DELETE' INSERT INTO @temp_InsertedOrDelete (ID) SELECT ISNULL(ID,0) FROM deleted WHILE (SELECT COUNT(ID) FROM @temp_InsertedOrDelete) <> 0 BEGIN SELECT TOP 1 @ID = ISNULL(ID,0) FROM @temp_InsertedOrDelete SELECT @Xml = (SELECT (SELECT * FROM deleted FOR XML PATH(''),TYPE, ELEMENTS) AS DELETED FROM (SELECT @ID AS ID) AS MAIN WHERE ID = @ID FOR XML AUTO, ELEMENTS) --------------------------------------- --DELETE FROM THE DATAWAREHOUSE BEGIN-- --------------------------------------- ------------------------------------- --DELETE FROM THE DATAWAREHOUSE END-- ------------------------------------- DELETE FROM @temp_InsertedOrDelete WHERE ISNULL(ID,0) = ID END END END GO
As part of the Stored Procedure, I've added error handling by using TRY and CATCH and then write the errors to a SQL Table to view and help me to debug.
Saturday, July 23, 2016
: