{"id":4202,"date":"2013-01-25T17:45:41","date_gmt":"2013-01-25T17:45:41","guid":{"rendered":"http:\/\/craftydba.com\/?p=4202"},"modified":"2017-10-08T16:43:18","modified_gmt":"2017-10-08T16:43:18","slug":"instead-of-update","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=4202","title":{"rendered":"Instead of Update"},"content":{"rendered":"

\"\"<\/a><\/p>\n

Today, I will be looking at how INSTEAD OF TRIGGERS<\/a> can be used to make our VIEW<\/a> correctly work with a UPDATE statement. <\/p>\n

I will be using the [AUTOS] sample database for this demonstration. The [VW_JOIN_MAKES_2_MODELS] view will be enhanced with a instead of trigger so that update action can be perform on two tables at the same time.<\/p>\n

The enclosed<\/a> script has code to perform the following actions in the AUTOS database. <\/p>\n

<\/p>\n

\n
5A – Remove aggregate view<\/dd>\n
5B – Reload tables with good data<\/dd>\n
5C – Create a instead of trigger (insert)<\/dd>\n
5D – Test insert actions of instead of trigger<\/dd>\n
5E – Create a instead of trigger (delete)<\/dd>\n
5F – Test delete actions of instead of trigger<\/dd>\n
5G – Create a instead of trigger (update)<\/dd>\n
5H – Test update actions of instead of trigger<\/dd>\n<\/dl>\n

 <\/p>\n

<\/span><\/p>\n

Our focus today will be on 5G – defining the instead of update trigger and 5H – performing unit testing on said trigger.<\/p>\n

Like most Data Definition Language (DDL) constructs, a user defined trigger has three operations associated with it: CREATE TRIGGER<\/a>, ALTER TRIGGER<\/a> and DROP TRIGGER<\/a>.<\/p>\n

Since our view joins data between two tables, we need to know if data is going to be updated from the 1 = MODELS, 2 = MAKES or 3 = BOTH tables.<\/p>\n

With the instead of update trigger, we could compare the new data in the inserted table<\/a> to the old data in the deleted table<\/a> to determine what action to perform. This would require a lot of code for a view with a bunch of fields. <\/p>\n

How do we determine what table to update using a limited amount of code?<\/p>\n

We are in luck since the DBCC INPUTBUFFER<\/a> command can help us. Calling this function with the current session id returns a table containing information about the event. I used a table variable below to hold the information. We can find the exact TSQL statement that was used when the trigger fired.<\/p>\n

By parsing this string, we can find the WHERE clause of the update statement. Thus, finding what table to update from. <\/p>\n

Here are some ground rules for our view users. One requirement of the user is to use the primary key (make id or model id) or natural key (make name or model name) in the WHERE <\/a>clause. Only fields other than the primary key will be updated. Any broken requirements will end up in a error. <\/p>\n

The code below defines the INSTEAD OF UPDATE trigger.<\/p>\n

\r\n--\r\n-- Create a instead of trigger (update)\r\n--\r\n\r\n-- Remove existing trigger\r\nIF OBJECT_ID('DBO.TRG_CORRECT_UPD_ACTION_4_MAKES_2_MODELS') IS NOT NULL \r\n    DROP TRIGGER [DBO].[TRG_CORRECT_UPD_ACTION_4_MAKES_2_MODELS];\r\nGO\r\n\r\n-- Create instead of trigger\r\nCREATE TRIGGER [DBO].[TRG_CORRECT_UPD_ACTION_4_MAKES_2_MODELS] \r\n    ON [DBO].[VW_JOIN_MAKES_2_MODELS]\r\nINSTEAD OF UPDATE\r\nAS  \r\n\r\n  -- Author:    John Miner \r\n  -- Date:      Jan 2013\r\n  -- Purpose:   Perform the correct update action for a view. \r\n\r\nBEGIN  \r\n\r\n    -- Declare local variables\r\n    DECLARE @VAR_WHICH_TABLE INT;\r\n    DECLARE @VAR_TSQL NVARCHAR(4000);\r\n    DECLARE @VAR_VAL1 INT;\r\n    DECLARE @VAR_VAL2 INT;\r\n\r\n    -- Declare input buffer variable\r\n    DECLARE @VAR_BUFFER TABLE \r\n    (\r\n        [EventType] NVARCHAR(30),\r\n        [Parameters] INT,\r\n        [EventInfo] NVARCHAR(4000)\r\n    );\r\n\r\n    -- Find the tsql that fired the trigger\r\n    INSERT INTO @VAR_BUFFER\r\n    EXEC sp_executesql N'DBCC INPUTBUFFER(@@spid) WITH NO_INFOMSGS';\r\n\r\n    -- Parse TSQL to get WHERE clause\r\n    SELECT @VAR_TSQL = UPPER(ISNULL(B.EventInfo, '')) FROM @VAR_BUFFER B;\r\n    SELECT @VAR_VAL1 = CHARINDEX('WHERE', @VAR_TSQL, 1);\r\n    IF (@VAR_VAL1 > 0)\r\n        SELECT @VAR_TSQL = SUBSTRING(@VAR_TSQL, @VAR_VAL1, LEN(@VAR_TSQL) - @VAR_VAL1);\r\n    ELSE\r\n        SELECT @VAR_TSQL = ' ';\r\n\r\n    -- Action being performed\r\n    PRINT 'INSTEAD OF UPDATE ACTION - ';\r\n\r\n    -- Find out what is being modified\r\n    SELECT @VAR_WHICH_TABLE = 0;\r\n\r\n    IF ( (CHARINDEX('MODEL_ID', @VAR_TSQL, 1) > 0) OR (CHARINDEX('MODEL_NM', @VAR_TSQL, 1) > 0) )\r\n        SELECT @VAR_WHICH_TABLE = @VAR_WHICH_TABLE + 1;\r\n\r\n    IF ( (CHARINDEX('MAKER_ID', @VAR_TSQL, 1) > 0) OR (CHARINDEX('MAKER_NM', @VAR_TSQL, 1) > 0) )\r\n        SELECT @VAR_WHICH_TABLE = @VAR_WHICH_TABLE + 2;\r\n\r\n\r\n    -- Key fields are null\r\n    IF (@VAR_WHICH_TABLE = 0)\r\n    BEGIN\r\n        -- Action being performed\r\n        PRINT '  NO VALID KEYS SUPPLIED. ';\r\n\r\n\t-- Raise an error on this action\r\n        RAISERROR('Either makers or models id or name must not be null when updating data.  Please try again.', 16, 1)\r\n    END;\r\n\r\n\r\n    -- Update data in models table\r\n    IF (@VAR_WHICH_TABLE = 1) OR (@VAR_WHICH_TABLE = 3)\r\n    BEGIN\r\n        -- Action being performed\r\n        PRINT '  UPDATE MODELS DATA. ';\r\n\r\n\t-- Perform the update\r\n        UPDATE [DBO].[MODELS]\r\n\tSET\r\n\t    MODEL_NM = I.MODEL_NM,\r\n\t    MODEL_YR = I.MODEL_YR,\r\n\t    MSRP = I.MSRP\r\n\tFROM \r\n\t    [DBO].[MODELS] M\r\n            INNER JOIN inserted I ON M.MODEL_ID = I.MODEL_ID;\r\n    END;\r\n\r\n\r\n    -- Update data in makes table\r\n    IF (@VAR_WHICH_TABLE = 2) OR (@VAR_WHICH_TABLE = 3)\r\n    BEGIN\r\n        -- Action being performed\r\n        PRINT '  UPDATE MAKES DATA. ';\r\n\r\n\t-- Perform the update\r\n        UPDATE [DBO].[MAKES]\r\n\tSET\r\n\t    MAKER_NM = I.MAKER_NM,\r\n\t    START_YR = I.START_YR,\r\n\t    END_YR = I.END_YR\r\n\tFROM \r\n\t    [DBO].[MAKES] M\r\n            INNER JOIN inserted I ON M.MAKER_ID = I.MAKER_ID;\r\n    END;\r\n    \r\nEND;\r\n<\/pre>\n
Lets get the negative testing out of the way by trying to update data from the view without a primary key.<\/p>\n
\r\n-- Update should fail\r\nUPDATE DBO.VW_JOIN_MAKES_2_MODELS\r\nSET MAKER_NM = 'Dodge Ram'\r\n<\/pre>\n
\"\"<\/a><\/p>\n
The first positive test is to update data in the MAKES table using a primary key.<\/p>\n
\r\n-- Update single field\r\nUPDATE DBO.VW_JOIN_MAKES_2_MODELS\r\nSET MAKER_NM = 'Dodge Ram'\r\nWHERE MAKER_ID = 2;\r\n<\/pre>\n
\"\"<\/a><\/p>\n
The second positive test is to update data in the MODELS table using the primary key.  <\/p>\n
\r\n-- Update single field\r\nUPDATE DBO.VW_JOIN_MAKES_2_MODELS\r\nSET MODEL_NM = '1500'\r\nWHERE MODEL_ID = 2;\r\n<\/pre>\n
\"\"<\/a><\/p>\n
The third positive test is to update data from both the MAKES and MODELS tables at the same time works. This is a new functionality that is not available in a normal view.<\/p>\n
\r\n-- Update two fields, restore original setting\r\nUPDATE DBO.VW_JOIN_MAKES_2_MODELS\r\nSET \r\n    MAKER_NM = 'Dodge'\r\n  , MODEL_NM = 'Ram 1500'\r\nWHERE MAKER_ID = 2 AND MODEL_ID = 2;\r\n<\/pre>\n
\"\"<\/a><\/p>\n
In summary, we were able to add a INSTEAD OF UPDATE trigger to our user defined view to make it function correctly with a UPDATE statement.  While I have been focusing on enabling a VIEW to be updatable, triggers can be used to implement many different business rules.  <\/p>\n
Just remember, a trigger gets fired like a Tommy Gun every time the associated DML<\/a> statement (INSERT, UPDATE, DELETE) is executed.  Therefore, limiting the amount of code in a trigger will make sure your SQL statements execute as quick as possible.<\/p>\n
Next time, I will be creating a test data warehouse from scratch with 20 million rows.  This in preparation for partitioned and materialized views.<\/p>\n","protected":false},"excerpt":{"rendered":"
Today, I will be looking at how INSTEAD OF TRIGGERS can be used to make our VIEW correctly work with a UPDATE statement. I will be using the [AUTOS] sample database for this demonstration. The [VW_JOIN_MAKES_2_MODELS] view will be enhanced with a instead of trigger so that update action can be perform on two tables at the same time. The enclosed script has code to perform the following actions in the AUTOS database. 5A – Remove aggregate view 5B – Reload tables with good data 5C – Create a instead…<\/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":[398,738,317,316,31,737,433,80,563,12,432,740,330,15,28,614,29,32,613],"class_list":["post-4202","post","type-post","status-publish","format-standard","hentry","category-db-dev","tag-alter-trigger","tag-charindex","tag-create-trigger","tag-create-view","tag-database-developer","tag-dbcc-inputbuffer","tag-deleted","tag-drop-trigger","tag-drop-view","tag-free-code","tag-inserted","tag-instead-of-update","tag-isnull","tag-john-f-miner-iii","tag-sql-server","tag-substring","tag-tsql","tag-update","tag-upper"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/4202","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=4202"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/4202\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4202"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4202"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4202"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}