/******************************************************
 *
 * Name:         move-to-history.sql
 *     
 * Design Phase:
 *     Author:   John Miner
 *     Date:     05-07-2012
 *     Purpose:  An example of DML triggers used
 *               to move history records to seperate
 *		 table.
 * 
 ******************************************************/

/*  
	Which database to use.
*/


-- Select the correct database
USE [AUTOS]
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

-- Load the table with data
INSERT INTO USA.STATUS_CODE (MyId, MyValue) VALUES
(1, 'Completed'),
(2, 'Scraped'),
(3, 'Rework Needed')
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].[MAKE_STATUS]
GO

TRUNCATE TABLE [USA].[MAKE_HISTORY]
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


/*  
	Fictitious build of Lincoln Town Car.
*/

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