Auditing Using Triggers, Merge, CTE  

Posted by ReelTym


CREATE SCHEMA Audit
GO

IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME = 'Domain_Locale_Audit_FK_Audit_Host' )
ALTER TABLE Domain.Locale_Audit DROP CONSTRAINT Domain_Locale_Audit_FK_Audit_Host;
GO
IF OBJECT_ID( 'Audit.Host' ) IS NOT NULL
DROP TABLE Audit.Host;
GO
CREATE TABLE Audit.Host
(
ID int IDENTITY(1,1),
[Name] sysname NOT NULL,
CONSTRAINT Audit_Host_PK PRIMARY KEY CLUSTERED ( ID )
);
GO
IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME = 'Domain_Locale_Audit_FK_Audit_App' )
ALTER TABLE Domain.Locale_Audit DROP CONSTRAINT Domain_Locale_Audit_FK_Audit_App;
GO
IF OBJECT_ID( 'Audit.App' ) IS NOT NULL
DROP TABLE Audit.App;
GO
CREATE TABLE Audit.App
(
ID int IDENTITY(1,1),
[Name] sysname NOT NULL,
CONSTRAINT Audit_App_PK PRIMARY KEY CLUSTERED ( ID )
);
GO
IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME = 'Domain_Locale_Audit_FK_Audit_SystemUser' )
ALTER TABLE Domain.Locale_Audit DROP CONSTRAINT Domain_Locale_Audit_FK_Audit_SystemUser;
GO
IF OBJECT_ID( 'Audit.SystemUser' ) IS NOT NULL
DROP TABLE Audit.SystemUser;
GO
CREATE TABLE Audit.SystemUser
(
ID int IDENTITY(1,1),
[Name] sysname NOT NULL,
CONSTRAINT Audit_SystemUser_PK PRIMARY KEY CLUSTERED ( ID )
);
GO
IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME = 'Domain_Locale_Audit_FK_Audit_Action' )
ALTER TABLE Domain.Locale_Audit DROP CONSTRAINT Domain_Locale_Audit_FK_Audit_Action;
GO
IF OBJECT_ID( 'Audit.Action' ) IS NOT NULL
DROP TABLE Audit.Action;
GO
CREATE TABLE Audit.Action
(
ID int IDENTITY(1,1),
[Name] sysname NOT NULL,
CONSTRAINT Audit_Action_PK PRIMARY KEY CLUSTERED ( ID )
);
GO

CREATE SCHEMA Domain;
GO

IF OBJECT_ID( 'Domain.Locale' ) IS NOT NULL
DROP TABLE Domain.Locale;
GO
CREATE TABLE Domain.Locale
(
ID tinyint IDENTITY(1,1),
Code char(5) NOT NULL,
Description nvarchar(max) NOT NULL,

CONSTRAINT Domain_Locale_PK PRIMARY KEY CLUSTERED ( ID ),
CONSTRAINT Domain_Locale_UC_Code UNIQUE ( Code )
);
GO
IF OBJECT_ID( 'Domain.Locale_Audit' ) IS NOT NULL
DROP TABLE Domain.Locale_Audit;
GO
CREATE TABLE Domain.Locale_Audit
(
ID tinyint,
Code char(5) NOT NULL,
Description nvarchar(max) NOT NULL,

Ordinal bigint IDENTITY(1,1) NOT NULL,
HostID int NOT NULL,
AppID int NOT NULL,
SystemUserID int NOT NULL,
ActionID int NOT NULL,
WhenUpdated datetime2(7) NOT NULL,
ColumnsUpdated varbinary(max) NOT NULL,

CONSTRAINT Domain_Locale_Audit_PK PRIMARY KEY CLUSTERED ( ID, Ordinal ),
CONSTRAINT Domain_Locale_Audit_FK_Audit_Host FOREIGN KEY ( HostID ) REFERENCES Audit.Host ( ID ),
CONSTRAINT Domain_Locale_Audit_FK_Audit_App FOREIGN KEY ( AppID ) REFERENCES Audit.App ( ID ),
CONSTRAINT Domain_Locale_Audit_FK_Audit_SystemUser FOREIGN KEY ( SystemUserID ) REFERENCES Audit.SystemUser ( ID ),
CONSTRAINT Domain_Locale_Audit_FK_Audit_Action FOREIGN KEY ( ActionID ) REFERENCES Audit.Action ( ID )
);
GO
IF OBJECT_ID( 'Domain_Locale_Audit_IDX_ActionID_HostID_SystemUserID_AppID' ) IS NOT NULL
DROP INDEX Domain_Locale_Audit_IDX_ActionID_HostID_SystemUserID_AppID ON Domain.Locale_Audit;
GO
CREATE NONCLUSTERED INDEX Domain_Locale_Audit_IDX_ActionID_HostID_SystemUserID_AppID
ON [Domain].[Locale_Audit]
(
ActionID ASC,
SystemUserID ASC,
AppID ASC,
HostID ASC,
ID ASC,
Ordinal ASC
)
INCLUDE ( Code, Description, WhenUpdated, ColumnsUpdated )
WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
GO
IF OBJECT_ID( 'Domain.Locale_Audit_VW' ) IS NOT NULL
DROP VIEW Domain.Locale_Audit_VW;
GO
CREATE VIEW Domain.Locale_Audit_VW
AS
WITH Ordered AS
(
SELECT
l.ID,
l.Code,
l.Description,

ROW_NUMBER() OVER( PARTITION BY l.ID ORDER BY Ordinal DESC ) AS CurrentFirst,
ROW_NUMBER() OVER( PARTITION BY l.ID ORDER BY Ordinal ) AS OriginalFirst,
l.Ordinal,
h.Name as Host,
a.Name as App,
su.Name as SystemUser,
ac.Name as Action,
l.WhenUpdated,
l.ColumnsUpdated

FROM Domain.Locale_Audit l
JOIN Audit.Host h ON l.HostID = h.ID
JOIN Audit.App a ON l.AppID = a.ID
JOIN Audit.SystemUser su ON l.SystemUserID = su.ID
JOIN Audit.Action ac ON l.ActionID = ac.ID
)
SELECT
*
FROM Ordered
GO
IF OBJECT_ID( 'Domain.Locale_Audit_Current_VW' ) IS NOT NULL
DROP VIEW Domain.Locale_Audit_Current_VW;
GO
CREATE VIEW Domain.Locale_Audit_Current_VW
AS
SELECT
*
FROM Locale_Audit_VW
WHERE CurrentFirst = 1
GO
CREATE TRIGGER Domain_Locale_TR_Audit
ON Domain.Locale
FOR DELETE, INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON
DECLARE @HostID int, @AppID int, @SystemUserID int, @ActionID int
DECLARE @Lookup TABLE ( ID int )

--Lookup HostID
MERGE Audit.Host AS t
USING ( SELECT Host_Name() AS [Name] ) AS s ON ( t.Name = s.Name )
WHEN MATCHED THEN
UPDATE SET t.Name = s.Name
WHEN NOT MATCHED BY TARGET THEN
INSERT ( Name ) VALUES ( s.Name )
OUTPUT Inserted.ID INTO @Lookup;
SELECT @HostID = ID FROM @Lookup;

--Lookup AppID
MERGE Audit.App AS t
USING ( SELECT App_Name() AS [Name] ) AS s
ON ( t.Name = s.Name )
WHEN MATCHED THEN
UPDATE SET
t.Name = s.Name
WHEN NOT MATCHED BY TARGET THEN
INSERT ( Name )
VALUES ( s.Name )
OUTPUT Inserted.ID INTO @Lookup;
SELECT @AppID = ID FROM @Lookup;

--Lookup SystemUserID
MERGE Audit.SystemUser AS t
USING ( SELECT System_User AS [Name] ) AS s
ON ( t.Name = s.Name )
WHEN MATCHED THEN
UPDATE SET
t.Name = s.Name
WHEN NOT MATCHED BY TARGET THEN
INSERT ( Name )
VALUES ( s.Name )
OUTPUT Inserted.ID INTO @Lookup;
SELECT @SystemUserID = ID FROM @Lookup;

--Lookup ActionID
DECLARE @Action sysname;
SELECT @Action =
CASE
WHEN NOT EXISTS ( SELECT 1 FROM Deleted ) THEN 'Inserted'
WHEN NOT EXISTS ( SELECT 1 FROM Inserted ) THEN 'Deleted'
ELSE 'Updated'
END
MERGE Audit.Action AS t
USING ( SELECT @Action AS [Name] ) AS s
ON ( t.Name = s.Name )
WHEN MATCHED THEN
UPDATE SET
t.Name = s.Name
WHEN NOT MATCHED BY TARGET THEN
INSERT ( Name )
VALUES ( s.Name )
OUTPUT Inserted.ID INTO @Lookup;
SELECT @ActionID = ID FROM @Lookup;

INSERT INTO Domain.Locale_Audit ( HostID, AppID, SystemUserID, ActionID, ColumnsUpdated, WhenUpdated, ID, Code, Description )
SELECT @HostID, @AppID, @SystemUserID, @ActionID, COLUMNS_UPDATED(), GETDATE(), s.ID, s.Code, s.Description FROM
(
SELECT * FROM Inserted UNION ALL
SELECT * FROM Deleted WHERE NOT EXISTS( SELECT 1 FROM Inserted )
) s;
END;
GO

SET IDENTITY_INSERT Domain.Locale ON;
SET NOCOUNT ON;
DECLARE @Source TABLE ( ID tinyint identity(1,1) primary key clustered, Code char(5) not null, Description nvarchar(max) not null );
DECLARE @Results TABLE ( [Action] sysname, d_ID tinyint, d_Code char(5), d_Description nvarchar(max), i_ID tinyint, i_Code char(5), i_Description nvarchar(max) );
INSERT INTO @Source ( Description, Code )
VALUES
( 'Afrikaans - South Africa', 'af-ZA' ),
( 'Albanian - Albania', 'sq-AL' ),
( 'Arabic - Algeria', 'ar-DZ' ),
( 'Arabic - Morocco', 'ar-MA' ),
( 'Arabic - Oman', 'ar-OM' ),
( 'Arabic - Qatar', 'ar-QA' ),
( 'Arabic - Saudi Arabia', 'ar-SA' ),
( 'Arabic - Syria', 'ar-SY' ),
( 'Arabic - Tunisia', 'ar-TN' ),
( 'Arabic - United Arab Emirates', 'ar-AE' ),
( 'Arabic - Yemen', 'ar-YE' ),
( 'Arabic - Bahrain', 'ar-BH' ),
( 'Arabic - Egypt', 'ar-EG' ),
( 'Arabic - Jordan', 'ar-JO' ),
( 'Arabic - Kuwait', 'ar-KW' ),
( 'Arabic - Lebanon', 'ar-LB' ),
( 'Arabic - Libya', 'ar-LY' ),
( 'Armenian - Armenia', 'hy-AM' ),
( 'Azeri - Azerbaijan', 'az-AZ' ),
( 'Basque - Basque', 'eu-ES' ),
( 'Belarusian - Belarus', 'be-BY' ),
( 'Bulgarian - Bulgaria', 'bg-BG' ),
( 'Catalan - Spain', 'ca-ES' ),
( 'Chinese - China (Simplified Chinese)', 'zh-CN' ),
( 'Chinese - Hong Kong SAR', 'zh-HK' ),
( 'Chinese - Macao SAR', 'zh-MO' ),
( 'Chinese - Singapore', 'zh-SG' ),
( 'Chinese - Taiwan (Traditional Chinese)', 'zh-TW' ),
( 'Croatian - Croatia', 'hr-HR' ),
( 'Czech - Czech Republic', 'cs-CZ' ),
( 'Danish - Denmark', 'da-DK' ),
( 'Dutch - Belgium', 'nl-BE' ),
( 'Dutch - The Netherlands', 'nl-NL' ),
( 'English - Australia', 'en-AU' ),
( 'English - Belize', 'en-BZ' ),
( 'English - Canada', 'en-CA' ),
( 'English - Caribbean', 'en-CB' ),
( 'English - Ireland', 'en-IE' ),
( 'English - Jamaica', 'en-JM' ),
( 'English - New Zealand', 'en-NZ' ),
( 'English - Philippines', 'en-PH' ),
( 'English - South Africa', 'en-ZA' ),
( 'English - Trinidad and Tobago', 'en-TT' ),
( 'English - United Kingdom', 'en-GB' ),
( 'English - United States', 'en-US' ),
( 'English - Zimbabwe', 'en-ZW' ),
( 'Estonian - Estonia', 'et-EE' ),
( 'Faroese - Faroe Islands', 'fo-FO' ),
( 'Farsi - Iran', 'fa-IR' ),
( 'Finnish - Finland', 'fi-FI' ),
( 'French - Belgium', 'fr-BE' ),
( 'French - Canada', 'fr-CA' ),
( 'French - France', 'fr-FR' ),
( 'French - Luxembourg', 'fr-LU' ),
( 'French - Monaco', 'fr-MC' ),
( 'French - Switzerland', 'fr-CH' ),
( 'Galician - Galician', 'gl-ES' ),
( 'Georgian - Georgia', 'ka-GE' ),
( 'German - Austria', 'de-AT' ),
( 'German - Germany', 'de-DE' ),
( 'German - Liechtenstein', 'de-LI' ),
( 'German - Luxembourg', 'de-LU' ),
( 'German - Switzerland', 'de-CH' ),
( 'Greek - Greece', 'el-GR' ),
( 'Gujarati - India', 'gu-IN' ),
( 'Hebrew - Israel', 'he-IL' ),
( 'Hindi - India', 'hi-IN' ),
( 'Hungarian - Hungary', 'hu-HU' ),
( 'Icelandic - Iceland', 'is-IS' ),
( 'Indonesian - Indonesia', 'id-ID' ),
( 'Italian - Italy', 'it-IT' ),
( 'Italian - Switzerland', 'it-CH' ),
( 'Japanese - Japan', 'ja-JP' ),
( 'Kannada - India', 'kn-IN' ),
( 'Kazakh - Kazakhstan', 'kk-KZ' ),
( 'Korean - Korea', 'ko-KR' ),
( 'Kyrgyz - Kyrgyzstan', 'ky-KG' ),
( 'Latvian - Latvia', 'lv-LV' ),
( 'Lithuanian - Lithuania', 'lt-LT' ),
( 'Macedonian - Former Yugoslav Republic of Macedonia', 'mk-MK' ),
( 'Malay - Brunei', 'ms-BN' ),
( 'Malay - Malaysia', 'ms-MY' ),
( 'Marathi - India', 'mr-IN' ),
( 'Mongolian - Mongolia', 'mn-MN' ),
( 'Norwegian (Bokmål) - Norway', 'nb-NO' ),
( 'Norwegian (Nynorsk) - Norway', 'nn-NO' ),
( 'Polish - Poland', 'pl-PL' ),
( 'Portuguese - Brazil', 'pt-BR' ),
( 'Portuguese - Portugal', 'pt-PT' ),
( 'Punjabi - India', 'pa-IN' ),
( 'Romanian - Romania', 'ro-RO' ),
( 'Russian - Russia', 'ru-RU' ),
( 'Sanskrit - India', 'sa-IN' ),
( 'Serbian - Serbia', 'sr-SP' ),
( 'Slovak - Slovakia', 'sk-SK' ),
( 'Slovenian - Slovenia', 'sl-SI' ),
( 'Spanish - Argentina', 'es-AR' ),
( 'Spanish - Bolivia', 'es-BO' ),
( 'Spanish - Chile', 'es-CL' ),
( 'Spanish - Colombia', 'es-CO' ),
( 'Spanish - Costa Rica', 'es-CR' ),
( 'Spanish - Dominican Republic', 'es-DO' ),
( 'Spanish - Ecuador', 'es-EC' ),
( 'Spanish - El Salvador', 'es-SV' ),
( 'Spanish - Guatemala', 'es-GT' ),
( 'Spanish - Honduras', 'es-HN' ),
( 'Spanish - Mexico', 'es-MX' ),
( 'Spanish - Nicaragua', 'es-NI' ),
( 'Spanish - Panama', 'es-PA' ),
( 'Spanish - Paraguay', 'es-PY' ),
( 'Spanish - Peru', 'es-PE' ),
( 'Spanish - Puerto Rico', 'es-PR' ),
( 'Spanish - Spain', 'es-ES' ),
( 'Spanish - Uruguay', 'es-UY' ),
( 'Spanish - Venezuela', 'es-VE' ),
( 'Swahili - Kenya', 'sw-KE' ),
( 'Swedish - Finland', 'sv-FI' ),
( 'Swedish - Sweden', 'sv-SE' ),
( 'Tamil - India', 'ta-IN' ),
( 'Tatar - Russia', 'tt-RU' ),
( 'Telugu - India', 'te-IN' ),
( 'Thai - Thailand', 'th-TH' ),
( 'Turkish - Turkey', 'tr-TR' ),
( 'Ukrainian - Ukraine', 'uk-UA' ),
( 'Urdu - Pakistan', 'ur-PK' ),
( 'Uzbek - Uzbekistan', 'uz-UZ' ),
( 'Vietnamese - Vietnam', 'vi-VN' )

declare @count int = 0
while @count < id =" s.ID)" code =" s.Code," description =" s.Description" id =" s.ID)" code =" s.Code," description =" s.Description">

This entry was posted on Wednesday, May 06, 2009 and is filed under , . You can leave a response and follow any responses to this entry through the Subscribe to: Post Comments (Atom) .

0 comments