{"id":1995,"date":"2012-05-07T20:00:27","date_gmt":"2012-05-07T20:00:27","guid":{"rendered":"http:\/\/craftydba.com\/?p=1995"},"modified":"2013-01-23T21:55:33","modified_gmt":"2013-01-23T21:55:33","slug":"various-triggers-part-4","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=1995","title":{"rendered":"Various Triggers – Part 4"},"content":{"rendered":"

\"\"<\/a>Another usage of Data Modification Language (DML) triggers is the replicating data for historical tracking or data aggregation. <\/p>\n

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. <\/p>\n

 <\/p>\n

To accomplish this goal, I am going to add the following objects to the AUTOS database.<\/p>\n

STATUS_CODE<\/span> – a coded version of the status of a given task.
\nMAKE_STATUS<\/span> – for a given vehicle (VIN), what is the current status.
\nMAKE_HISTORY<\/span> – all tasks and status for building a vehicle (VIN).
\nTRG_MAKE_STATUS_TO_HISTORY<\/span> – a trigger that copies the current insert\/update to the history table.<\/p>\n

It is assumed that the following UPSERT stored procedure will be used when populating vehicle information. <\/p>\n

-- Create the procedure\r\nCREATE PROCEDURE [USA].[UPSERT] (\r\n    @VehicleId [varchar](17),\r\n    @BrandId [Int],\r\n    @StartDate [datetime],\r\n    @FinishDate [datetime],\r\n    @MessageText [varchar](max),\r\n    @StatusCode [int])\r\nAS\r\nBEGIN\r\n\r\n    -- Worry about concurrency\r\n    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE \r\n\r\n    BEGIN TRAN\r\n    \r\n        -- Try the update first\r\n        UPDATE [USA].[MAKE_STATUS] \r\n        SET \r\n            [BrandId] = @BrandId,\r\n            [StartDate] = @StartDate,\r\n            [FinishDate] = @FinishDate,\r\n            [MessageText] = @MessageText,\r\n            [StatusCode] = @StatusCode\r\n        WHERE [VehicleId] = @VehicleId\r\n          \r\n        -- Did not update vehicle\r\n        IF @@ROWCOUNT = 0        \r\n        BEGIN\r\n        \r\n            -- Add new record\r\n            INSERT INTO [USA].[MAKE_STATUS] SELECT\r\n                @VehicleId,\r\n                @BrandId,\r\n                @StartDate,\r\n                @FinishDate,\r\n                @MessageText,\r\n                @StatusCode\r\n                \r\n            -- Detected error\r\n            IF @@ERROR <> 0\r\n                ROLLBACK\r\n               \r\n        END\r\n    \r\n    -- Save the transcation\r\n    COMMIT\r\n    \r\nEND\r\nGO\r\n<\/span><\/pre>\n
 <\/p>\n
I am going to use the macro tasks for automobile making described in this ehow<\/a> 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<\/a>.<\/p>\n
To illustrate the trigger working, I am going to show the history table, as defined below, full of data.<\/p>\n
-- Create MAKE HISTORY table (All Records)\r\nCREATE TABLE [USA].[MAKE_HISTORY]\r\n(\r\n    [VehicleId] [varchar](17) NOT NULL,\r\n    [BrandId] Int NOT NULL,\r\n    [StartDate] [datetime] NOT NULL,\r\n    [FinishDate] [datetime] NULL,\r\n    [MessageText] [varchar](max) NULL,\r\n    [StatusCode] [int] NULL,\r\n    [Duration] AS (datediff(second,[StartDate],[FinishDate])) PERSISTED,\r\n    CONSTRAINT [pk_Make_History] PRIMARY KEY CLUSTERED \r\n    ( \r\n        [VehicleId] ASC,  \r\n        [StartDate] ASC\r\n    )\r\n)\r\nGO\r\n<\/span><\/pre>\n
 <\/p>\n
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.<\/p>\n
\"\"<\/a><\/p>\n
 <\/p>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[396,398,317,395,80,12,15,28,390,29],"class_list":["post-1995","post","type-post","status-publish","format-standard","hentry","category-db-dev","tag-after","tag-alter-trigger","tag-create-trigger","tag-dml","tag-drop-trigger","tag-free-code","tag-john-f-miner-iii","tag-sql-server","tag-triggers","tag-tsql"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/1995","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1995"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/1995\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1995"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1995"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1995"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}