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">