Another usage of Data Modification Language (DML) triggers is the replicating data for historical tracking or data aggregation.
Today, I am going to expand on the AUTOS database that I created in prior articles to demonstrate such usage. The business unit has asked us to track each macro task in the making of an automobile.
To accomplish this goal, I am going to add the following objects to the AUTOS database.
STATUS_CODE – a coded version of the status of a given task.
MAKE_STATUS – for a given vehicle (VIN), what is the current status.
MAKE_HISTORY – all tasks and status for building a vehicle (VIN).
TRG_MAKE_STATUS_TO_HISTORY – a trigger that copies the current insert/update to the history table.
It is assumed that the following UPSERT stored procedure will be used when populating vehicle information.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
<span style="color: #008000; font-size:small;">-- 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 </span> |
I am going to use the macro tasks for automobile making described in this ehow article. There are really no new TSQL statements being introduced with this example. The typical CREATE, ALTER, and DROP statements are used in the sample code.
To illustrate the trigger working, I am going to show the history table, as defined below, full of data.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
<span style="color: #008000; font-size:small;">-- 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 </span> |
The resulting data rows after inserting 10 macro steps with one rework action is shown below. It is not a coincident that I used increments of 100 seconds multiplied by the task number as the duration.
In summary, after triggers can be used to duplicate data for historical record keeping. This history table might or might not be in the same database. Having the history data in a different database allows for applying techniques such as data compression and read only file groups to reduce administrative overhead.
