/******************************************************
 *
 * Name:         autos-version6.sql
 *     
 * Design Phase:
 *     Author:   John Miner
 *     Date:     05-08-2012
 *     Purpose:  An example of DML triggers used
 *               to track schema changes.
 * 
 ******************************************************/


/*  
	Create the new database.
*/

-- Start with master database
USE MASTER;
GO

-- Delete existing databases.
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'AUTOS')
DROP DATABASE [AUTOS]
GO

-- Create a autos database
CREATE DATABASE AUTOS;
GO

-- Use the database
USE [AUTOS]
GO


/*  
	Create the new schemas.  USA (United States) & ADT (Audit)
*/

-- Delete existing schema.
IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'USA')
DROP SCHEMA [USA];
GO

-- Create a USA schema
CREATE SCHEMA [USA] AUTHORIZATION dbo;
GO

-- Delete existing schema.
IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'ADT')
DROP SCHEMA [ADT];
GO

-- Create a USA schema
CREATE SCHEMA [ADT] AUTHORIZATION dbo;
GO


/*  
	Create schema level auditing - table.
*/

-- Remove table if it exists
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ADT].[LOG_DDL_CHANGES]') AND type in (N'U'))
DROP TABLE [ADT].[LOG_DDL_CHANGES]
GO

-- Create a BRANDS table
CREATE TABLE [ADT].[LOG_DDL_CHANGES]
(
	[ChangeId] BIGINT IDENTITY(1,1) NOT NULL,
	[ChangeDate] [datetime] NOT NULL,
	[ChangeType] [varchar](20) NOT NULL,
	[ChangeBy] [nvarchar](256) NOT NULL,
	[AppName] [nvarchar](128) NOT NULL,
	[HostName] [nvarchar](128) NOT NULL,
	[SchemaName] [sysname] NULL,
	[ObjectName] [sysname] NULL,
	[Tsql] nvarchar(MAX) NULL,
 CONSTRAINT [pk_Ldc_ChangeId] PRIMARY KEY CLUSTERED ([ChangeId] ASC)
) 
GO

-- Add defaults for key information
ALTER TABLE [ADT].[LOG_DDL_CHANGES] ADD CONSTRAINT [df_Ldc_ChangeDate] DEFAULT (getdate()) FOR [ChangeDate];
ALTER TABLE [ADT].[LOG_DDL_CHANGES] ADD CONSTRAINT [df_Ldc_ChangeType] DEFAULT ('') FOR [ChangeType];
ALTER TABLE [ADT].[LOG_DDL_CHANGES] ADD CONSTRAINT [df_Ldc_ChangeBy] DEFAULT (coalesce(suser_sname(),'?')) FOR [ChangeBy];
ALTER TABLE [ADT].[LOG_DDL_CHANGES] ADD CONSTRAINT [df_Ldc_AppName] DEFAULT (coalesce(APP_NAME(),'?')) FOR [AppName];
ALTER TABLE [ADT].[LOG_DDL_CHANGES] ADD CONSTRAINT [df_Ldc_HostName] DEFAULT (coalesce(HOST_NAME(),'?')) FOR [HostName];
GO


/*  
	Create schema level auditing - trigger.
*/

-- Remove trigger if it exists
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[TRG_TRACK_DDL_CHANGES]'))
DROP TRIGGER [TRG_TRACK_DDL_CHANGES]
GO

-- Add the trigger to the db
CREATE TRIGGER [TRG_TRACK_DDL_CHANGES] ON DATABASE 
FOR 
  DDL_TRIGGER_EVENTS, DDL_FUNCTION_EVENTS, DDL_PROCEDURE_EVENTS, DDL_TABLE_VIEW_EVENTS, DDL_DATABASE_SECURITY_EVENTS 
AS 
BEGIN

    -- Declare local variables
    DECLARE @VAR_DATA XML;
    DECLARE @VAR_SCHEMA sysname;
    DECLARE @VAR_OBJECT sysname; 
    DECLARE @VAR_EVENT_TYPE sysname;

    -- Set local variables
    SET @VAR_DATA = EVENTDATA();
    SET @VAR_EVENT_TYPE = @VAR_DATA.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
    SET @VAR_SCHEMA = @VAR_DATA.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
    SET @VAR_OBJECT = @VAR_DATA.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname') 

    -- Do not track replication or statistics
	IF (( @VAR_EVENT_TYPE <> 'CREATE_STATISTICS') and ( @VAR_OBJECT not like 'syncobj_0x%')) 
	BEGIN
		INSERT [ADT].[LOG_DDL_CHANGES] ([ObjectName], [ChangeType], [Tsql], [SchemaName]) VALUES 
		( 
		    CONVERT(sysname, @VAR_OBJECT), 
		    @VAR_EVENT_TYPE, 
		    @VAR_DATA.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'), 
		    CONVERT(sysname, @VAR_SCHEMA)
		 );
	END

END;
GO


/*  
	Create data level auditing - table.
*/

-- Remove table if it exists
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ADT].[LOG_DML_CHANGES]') AND type in (N'U'))
DROP TABLE [ADT].[LOG_DML_CHANGES]
GO

CREATE TABLE [ADT].[LOG_DML_CHANGES]
(
	[ChangeId]BIGINT IDENTITY(1,1) NOT NULL,
	[ChangeDate] [datetime] NOT NULL,
	[ChangeType] [varchar](20) NOT NULL,
	[ChangeBy] [nvarchar](256) NOT NULL,
	[AppName] [nvarchar](128) NOT NULL,
	[HostName] [nvarchar](128) NOT NULL,
	[SchemaName] [sysname] NOT NULL,
	[ObjectName] [sysname] NOT NULL,
	[XmlRecSet] [xml] NULL,
 CONSTRAINT [pk_Ltc_ChangeId] PRIMARY KEY CLUSTERED ([ChangeId] ASC)
) 
GO


-- Add defaults for key information
ALTER TABLE [ADT].[LOG_DML_CHANGES] ADD CONSTRAINT [df_Ltc_ChangeDate] DEFAULT (getdate()) FOR [ChangeDate];
ALTER TABLE [ADT].[LOG_DML_CHANGES] ADD CONSTRAINT [df_Ltc_ChangeType] DEFAULT ('') FOR [ChangeType];
ALTER TABLE [ADT].[LOG_DML_CHANGES] ADD CONSTRAINT [df_Ltc_ChangeBy] DEFAULT (coalesce(suser_sname(),'?')) FOR [ChangeBy];
ALTER TABLE [ADT].[LOG_DML_CHANGES] ADD CONSTRAINT [df_Ltc_AppName] DEFAULT (coalesce(APP_NAME(),'?')) FOR [AppName];
ALTER TABLE [ADT].[LOG_DML_CHANGES] ADD CONSTRAINT [df_Ltc_HostName] DEFAULT (coalesce(HOST_NAME(),'?')) FOR [HostName];
GO



/*  
	Status Code Table.
*/

-- Remove table if it exists
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[USA].[BRANDS]') AND type in (N'U'))
DROP TABLE [USA].[BRANDS]
GO

-- Create a BRANDS table
CREATE TABLE [USA].[BRANDS]
(
MyId INT PRIMARY KEY CLUSTERED,
MyValue VARCHAR(20)
)
GO



/*  
	Status Code Table.
*/

-- Remove table if it exists
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[USA].[STATUS_CODE]') AND type in (N'U'))
DROP TABLE [USA].[STATUS_CODE]
GO

-- Create a STATUS CODE table
CREATE TABLE USA.STATUS_CODE
(
MyId INT PRIMARY KEY CLUSTERED,
MyValue VARCHAR(20)
)
GO



/*  
	Make Status Table.
*/

-- Remove table if it exists
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[USA].[MAKE_STATUS]') AND type in (N'U'))
DROP TABLE [USA].[MAKE_STATUS]
GO

-- Create MAKE STATUS table (Latest Record)
CREATE TABLE [USA].[MAKE_STATUS]
(
    [VehicleId] [varchar](17) NOT NULL,
    [BrandId] Int NOT NULL,
    [StartDate] [datetime] NOT NULL,
    [FinishDate] [datetime] NULL,
    [MessageText] [varchar](max) NULL,
    [StatusCode] [int] NULL,

    [Duration] AS 
        (datediff(second,[StartDate],[FinishDate])) PERSISTED,
    CONSTRAINT [pk_Make_Status] PRIMARY KEY CLUSTERED 
    ( 
        [VehicleId] ASC,  
        [StartDate] ASC
    )
)
GO



/*  
	Make History Table.
*/

-- Remove table if it exists
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[USA].[MAKE_HISTORY]') AND type in (N'U'))
DROP TABLE [USA].[MAKE_HISTORY]
GO

-- Create MAKE HISTORY table (All Records)
CREATE TABLE [USA].[MAKE_HISTORY]
(
    [VehicleId] [varchar](17) NOT NULL,
    [BrandId] Int NOT NULL,
    [StartDate] [datetime] NOT NULL,
    [FinishDate] [datetime] NULL,
    [MessageText] [varchar](max) NULL,
    [StatusCode] [int] NULL,

    [Duration] AS 
        (datediff(second,[StartDate],[FinishDate])) PERSISTED,
    CONSTRAINT [pk_Make_History] PRIMARY KEY CLUSTERED 
    ( 
        [VehicleId] ASC,  
        [StartDate] ASC
    )
)
GO


/*  
	Trigger to move data.
*/

-- Remove trigger if it exists
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[USA].[TRG_MAKE_STATUS_TO_HISTORY]'))
DROP TRIGGER [USA].[TRG_MAKE_STATUS_TO_HISTORY]
GO

-- Move modified record to history table
CREATE TRIGGER [USA].[TRG_MAKE_STATUS_TO_HISTORY] ON [USA].[MAKE_STATUS]
FOR INSERT, UPDATE
AS
    INSERT INTO [USA].[MAKE_HISTORY]
	SELECT
            [VehicleId],
            [BrandId],
            [StartDate],
            [FinishDate],
            [MessageText],
            [StatusCode]
	FROM inserted
GO



/*  
	Clear tables.
*/

-- Clear the tables
TRUNCATE TABLE [USA].[BRANDS]
GO

TRUNCATE TABLE [USA].[MAKE_HISTORY]
GO

TRUNCATE TABLE [USA].[MAKE_STATUS]
GO

TRUNCATE TABLE [USA].[STATUS_CODE]
GO



/*  
	Create upsert procedure (use MERGE if > 2008)
*/

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[USA].[UPSERT]') AND type in (N'P', N'PC'))
DROP PROCEDURE [USA].[UPSERT]
GO

-- Create the procedure
CREATE PROCEDURE [USA].[UPSERT] (
    @VehicleId [varchar](17),
    @BrandId [Int],
    @StartDate [datetime],
    @FinishDate [datetime],
    @MessageText [varchar](max),
    @StatusCode [int] )
AS
BEGIN

    -- Worry about concurrency
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 

    BEGIN TRAN
    
        -- Try the update first
        UPDATE [USA].[MAKE_STATUS] 
        SET 
            [BrandId] = @BrandId,
            [StartDate] = @StartDate,
            [FinishDate] = @FinishDate,
            [MessageText] = @MessageText,
            [StatusCode] = @StatusCode
        WHERE [VehicleId] = @VehicleId
          
        -- Did not update vehicle
        IF @@ROWCOUNT = 0        
        BEGIN
        
            -- Add new record
            INSERT INTO [USA].[MAKE_STATUS] SELECT
                @VehicleId,
                @BrandId,
                @StartDate,
                @FinishDate,
                @MessageText,
                @StatusCode
                
            -- Detected error
            IF @@ERROR <> 0
                ROLLBACK
               
        END
    
    -- Save the transcation
    COMMIT
    
END
GO


/*  
	Create data level auditing - triggers.
*/

-- Local variables
DECLARE @MYSCH AS sysname;
DECLARE @MYOBJ AS sysname;
DECLARE @MYSQL AS VARCHAR(MAX);

DECLARE @VARSCH AS sysname;
DECLARE @VAROBJ AS sysname;
DECLARE @VARFULL AS sysname;

DECLARE @RET AS VARCHAR(2);

-- Allocate cursor, return table names
DECLARE MYTRG CURSOR FAST_FORWARD FOR 
    SELECT 
         s.name AS SchemaName,
         o.name AS ObjectName
    FROM 
        sys.objects o (NOLOCK) JOIN sys.schemas s (NOLOCK) ON o.[schema_id] = s.[schema_id]
    WHERE o.[type] = 'U' AND s.[name] <> 'ADT'
    ORDER BY 
        s.name,
        o.name;

-- Open cursor    
OPEN MYTRG;

-- Set the cr/lf
SELECT @RET = CHAR(10) --+ CHAR(13);

-- Get the first row    
FETCH NEXT FROM MYTRG INTO @MYSCH, @MYOBJ;

-- While there is data
WHILE (@@FETCH_STATUS = 0) 
BEGIN   

    -- Set up variables
    SELECT @VARSCH = QuoteName(@MYSCH);
    SELECT @VAROBJ = QuoteName(@MYOBJ);
    SELECT @VARFULL = QuoteName(@MYSCH) + '.' + QuoteName(@MYOBJ);

    -- Show table name
    PRINT 'Making trigger for ' + @VARFULL;

    -- Dynamic SQL to drop object
    SELECT @MYSQL = 'IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N' + CHAR(39) + @VARSCH + '.' + QUOTENAME('TRG_TRACK_DML_CHGS_' + @MYOBJ) + CHAR(39) + ')) DROP TRIGGER ' + @VARSCH + '.' + QUOTENAME('TRG_TRACK_CHGS_' + @MYOBJ) + ';';
    EXEC (@MYSQL);
    --PRINT @MYSQL
    
    -- Dynamic SQL to create object
    SELECT @MYSQL = ''
    SELECT @MYSQL = @MYSQL + 'CREATE TRIGGER ' + @VARSCH + '.' + QUOTENAME('TRG_TRACK_DML_CHGS_' + @MYOBJ) + ' ON ' + @VARFULL + ' ' + @RET
    SELECT @MYSQL = @MYSQL + 'FOR INSERT, UPDATE, DELETE AS ' + @RET + @RET

    SELECT @MYSQL = @MYSQL + '  -- Author:  John Miner ' + @RET
    SELECT @MYSQL = @MYSQL + '  -- Date:  May 2012' + @RET
    SELECT @MYSQL = @MYSQL + '  -- Purpose:  Automated change detection trigger (ins, upd, del).' + @RET + @RET

    SELECT @MYSQL = @MYSQL + 'BEGIN ' + @RET + @RET

    SELECT @MYSQL = @MYSQL + '  -- Detect inserts' + @RET
    SELECT @MYSQL = @MYSQL + '  IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted) ' + @RET
    SELECT @MYSQL = @MYSQL + '    BEGIN ' + @RET
    SELECT @MYSQL = @MYSQL + '	  INSERT [ADT].[LOG_DML_CHANGES] ([ChangeType], [SchemaName], [ObjectName], [XmlRecSet]) ' + @RET
    SELECT @MYSQL = @MYSQL + '      SELECT ' + CHAR(39) + 'Insert'  + CHAR(39) + ', ' + CHAR(39) + @VARSCH  + CHAR(39) + ', ' + CHAR(39) + @VAROBJ + CHAR(39) + ', (SELECT * FROM inserted as Record FOR XML AUTO, elements , root(' + CHAR(39) + 'RecordSet' + CHAR(39) + '), type); ' + @RET
    SELECT @MYSQL = @MYSQL + '      RETURN; ' + @RET
    SELECT @MYSQL = @MYSQL + '    END; ' + @RET + @RET
    
    SELECT @MYSQL = @MYSQL + '  -- Detect deletes' + @RET
    SELECT @MYSQL = @MYSQL + '  IF EXISTS (SELECT * FROM deleted) AND NOT EXISTS(SELECT * FROM inserted) ' + @RET    
    SELECT @MYSQL = @MYSQL + '    BEGIN ' + @RET
    SELECT @MYSQL = @MYSQL + '      INSERT [ADT].[LOG_DML_CHANGES] ([ChangeType], [SchemaName], [ObjectName], [XmlRecSet]) ' + @RET
    SELECT @MYSQL = @MYSQL + '      SELECT ' + CHAR(39) + 'Delete'  + CHAR(39) + ', ' + CHAR(39) + @VARSCH  + CHAR(39) + ', ' + CHAR(39) + @VAROBJ + CHAR(39) + ', (SELECT * FROM deleted as Record FOR XML AUTO, elements , root(' + CHAR(39) + 'RecordSet' + CHAR(39) + '), type); ' + @RET
    SELECT @MYSQL = @MYSQL + '      RETURN; ' + @RET
    SELECT @MYSQL = @MYSQL + '    END; ' + @RET + @RET
        
    SELECT @MYSQL = @MYSQL + '  -- Update inserts' + @RET
    SELECT @MYSQL = @MYSQL + '  IF EXISTS (SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted) ' + @RET
    SELECT @MYSQL = @MYSQL + '    BEGIN ' + @RET
    SELECT @MYSQL = @MYSQL + '      INSERT [ADT].[LOG_DML_CHANGES] ([ChangeType], [SchemaName], [ObjectName], [XmlRecSet]) ' + @RET
    SELECT @MYSQL = @MYSQL + '      SELECT ' + CHAR(39) + 'Update'  + CHAR(39) + ', ' + CHAR(39) + @VARSCH  + CHAR(39) + ', ' + CHAR(39) + @VAROBJ + CHAR(39) + ', (SELECT * FROM deleted as Record FOR XML AUTO, elements , root(' + CHAR(39) + 'RecordSet' + CHAR(39) + '), type); ' + @RET
    SELECT @MYSQL = @MYSQL + '      RETURN; ' + @RET
    SELECT @MYSQL = @MYSQL + '    END; ' + @RET + @RET

    SELECT @MYSQL = @MYSQL + 'END;' 

    -- Execute the SQL stmt
    EXEC (@MYSQL);
    --PRINT @MYSQL
    
    -- Get the next row
    FETCH NEXT FROM MYTRG INTO @MYSCH, @MYOBJ;
    
END;

-- Close the cursor
CLOSE MYTRG; 

-- Release the cursor
DEALLOCATE MYTRG; 
GO



/*  
	Fictitious build of Lincoln Town Car.
*/

-- Load the brands table with data
INSERT INTO USA.BRANDS (MyId, MyValue) VALUES
(1, 'Continental'),
(2, 'Edsel'),
(3, 'Lincoln'),
(4, 'Mercury'),
(5, 'Ram')
GO


-- Load the status code table with data
INSERT INTO USA.STATUS_CODE (MyId, MyValue) VALUES
(1, 'Completed'),
(2, 'Scraped'),
(3, 'Rework Needed')
GO

-- http://www.ehow.com/how-does_4928886_automobile-assembly-process.html
-- Typical assembly process for Lincoln Town Car (UPSERT)

-- Step 1
EXEC USA.UPSERT '1G1FP22PXS2100001', 3, '2011-04-01 00:00:00', '2011-04-01 00:01:40', 'Start with Frame', 1;
GO

-- Step 2
EXEC USA.UPSERT '1G1FP22PXS2100001', 3, '2011-04-02 00:00:00', '2011-04-02 00:03:20', 'Install Parts on Frame', 1;
GO

-- Step 3
EXEC USA.UPSERT '1G1FP22PXS2100001', 3, '2011-04-03 00:00:00', '2011-04-03 00:05:00', 'Install Engine and Transmission', 1;
GO

-- Step 4
EXEC USA.UPSERT '1G1FP22PXS2100001', 3, '2011-04-04 00:00:00', '2011-04-04 00:06:40', 'Building the Shell', 1;
GO

-- Step 5
EXEC USA.UPSERT '1G1FP22PXS2100001', 3, '2011-04-05 00:00:00', '2011-04-05 00:08:20', 'Final Body Components', 1;
GO

-- Step 6
EXEC USA.UPSERT '1G1FP22PXS2100001', 3, '2011-04-06 00:00:00', '2011-04-06 00:10:00', 'Inspection and Washing', 1;
GO

-- Step 7
EXEC USA.UPSERT '1G1FP22PXS2100001', 3, '2011-04-07 00:00:00', '2011-04-07 00:11:40', 'Paint', 1;
GO

-- Step 8
EXEC USA.UPSERT '1G1FP22PXS2100001', 3, '2011-04-08 00:00:00', '2011-04-08 00:13:20', 'Add Inner Workings', 1;
GO

-- Step 9
EXEC USA.UPSERT '1G1FP22PXS2100001', 3, '2011-04-09 00:00:00', '2011-04-09 00:15:00', 'Join Shell and Frame', 1;
GO

-- Step 10
EXEC USA.UPSERT '1G1FP22PXS2100001', 3, '2011-04-10 00:00:00', '2011-04-10 00:16:40', 'Test the Car', 2;
GO

-- Step 11
EXEC USA.UPSERT '1G1FP22PXS2100001', 3, '2011-04-11 00:00:00', '2011-04-11 00:18:20', 'Test the Car', 1;
GO

SELECT * FROM ADT.LOG_DML_CHANGES