{"id":4157,"date":"2013-01-25T00:50:18","date_gmt":"2013-01-25T00:50:18","guid":{"rendered":"http:\/\/craftydba.com\/?p=4157"},"modified":"2017-10-08T16:48:18","modified_gmt":"2017-10-08T16:48:18","slug":"instead-of-delete","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=4157","title":{"rendered":"Instead of Delete"},"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 DELETE 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 delete 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 5E – defining the instead of delete trigger and 5F – 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 deleted from the 1 = MODELS, 2 = MAKES or 3 = BOTH tables.<\/p>\n

With the instead of insert trigger, we could use the physical data in the inserted table<\/a> to determine what action to perform. The instead of delete table has the deleted table<\/a> for reference; However it only shows potential records affected by the delete statement.<\/p>\n

How do we determine what table to delete from?<\/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 delete statement. Thus, finding what table to delete from. <\/p>\n

Here are some ground rules for our view users. One requirement of the user is to use the primary key (make or model id) or natural key (make or model name) in the WHERE clause. Deleting from the model table has no dependencies. Deleting from the make table can only occur if no model records exist. An exception to this rule is a delete from both tables when only one record exists in both. Any broken requirements will end up in a error. <\/p>\n

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

\r\n--\r\n-- Create a instead of trigger (delete)\r\n--\r\n\r\n-- Remove existing trigger\r\nIF OBJECT_ID('DBO.TRG_CORRECT_DEL_ACTION_4_MAKES_2_MODELS') IS NOT NULL \r\nDROP TRIGGER [DBO].[TRG_CORRECT_DEL_ACTION_4_MAKES_2_MODELS];\r\nGO\r\n\r\n-- Create instead of trigger\r\nCREATE TRIGGER [DBO].[TRG_CORRECT_DEL_ACTION_4_MAKES_2_MODELS] \r\n    ON [DBO].[VW_JOIN_MAKES_2_MODELS]\r\nINSTEAD OF DELETE\r\nAS  \r\n\r\n  -- Author:    John Miner \r\n  -- Date:      Jan 2013\r\n  -- Purpose:   Perform the correct delete 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\r\n    -- Action being performed\r\n    PRINT 'INSTEAD OF DELETE 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 deleting data.  Please try again.', 16, 1)\r\n    END;\r\n\r\n\r\n    -- Delete from models table\r\n    IF (@VAR_WHICH_TABLE = 1)\r\n    BEGIN\r\n        -- Action being performed\r\n        PRINT '  DELETE FROM MODELS. ';\r\n\r\n\t-- Perform the delete\r\n        DELETE FROM [DBO].[MODELS]\r\n        WHERE MODEL_ID IN\r\n        ( SELECT D.MODEL_ID FROM deleted AS D );\r\n    END;\r\n\r\n\r\n    -- Delete from makes table\r\n    IF (@VAR_WHICH_TABLE = 2)\r\n    BEGIN\r\n        -- Must be one record\r\n\tSELECT @VAR_VAL1 = COUNT(*) FROM deleted;\r\n\r\n\t-- Must not have any children\r\n\tSELECT @VAR_VAL2 = ISNULL(MODEL_ID, 0) FROM deleted;\r\n\r\n\t-- Make sure conditions are meet\r\n\tIF (@VAR_VAL1 = 1) AND (@VAR_VAL2 = 0)\r\n        BEGIN\r\n            -- Action being performed\r\n            PRINT '  DELETE FROM MAKES. ';\r\n\r\n            -- Perform the delete\r\n            DELETE FROM [DBO].[MAKES]\r\n            WHERE MAKER_ID IN\r\n            ( SELECT D.MAKER_ID FROM deleted AS D );\r\n        END\r\n\tELSE\r\n        BEGIN\r\n            -- Action being performed\r\n            PRINT '  TOO MANY CHILD RECORDS EXIST. ';\r\n\r\n            -- Raise an error on this action\r\n            RAISERROR('There must no model records left when deleting data from makes.  Please try again.', 16, 1)\r\n        END;\r\n\r\n    END;\r\n\r\n\r\n    -- Delete from both tables\r\n    IF (@VAR_WHICH_TABLE = 3)\r\n    BEGIN\r\n        -- Action being performed\r\n        PRINT '  DELETE FROM MODELS & MAKES. ';\r\n\r\n        -- Delete from child table first\r\n        DELETE FROM [DBO].[MODELS]\r\n        WHERE MODEL_ID IN\r\n        ( SELECT D.MODEL_ID FROM deleted AS D );\r\n\r\n        -- Delete from parent table second\r\n        DELETE FROM [DBO].[MAKES]\r\n        WHERE MAKER_ID IN\r\n        ( SELECT D.MAKER_ID FROM deleted AS D );\r\n                            \r\n    END;\r\n    \r\nEND \r\n\r\n<\/pre>\n
Before testing the new trigger, I want to add another model record (child) to the maker table (parent).  This means on data relationship with have an 1 to 2 cardinality.<\/p>\n
\r\n-- Add data to child table\r\nINSERT INTO DBO.VW_JOIN_MAKES_2_MODELS (MAKER_ID, MODEL_NM, MODEL_YR, MSRP)\r\n    VALUES (6, 'Model X', 2013, 104000);\r\n\r\n<\/pre>\n
Lets get the negative testing out of the way by trying to delete data from the view without a natural or primary key.<\/p>\n
\r\n-- Delete should fail\r\nDELETE FROM DBO.VW_JOIN_MAKES_2_MODELS;\r\n<\/pre>\n
\"\"<\/a><\/p>\n
The first positive test is to delete data from the MODELS table using either the natural or primary key.<\/p>\n
\r\n-- Delete from child table\r\nDELETE FROM DBO.VW_JOIN_MAKES_2_MODELS WHERE MODEL_NM = 'Model X';\r\n\r\n-- Delete from child table\r\nDELETE FROM DBO.VW_JOIN_MAKES_2_MODELS WHERE MODEL_ID = 6;\r\n<\/pre>\n
\"\"<\/a><\/p>\n
The second positive test is to delete from the MAKES table using the natural key.  We have to make sure no child record exists. <\/p>\n
\r\n-- Delete from parent table\r\nDELETE FROM DBO.VW_JOIN_MAKES_2_MODELS WHERE MAKER_NM = 'Tesla';\r\n<\/pre>\n
\"\"<\/a><\/p>\n
The second negative test is delete data from from the MAKES table using the primary key with one existing child record.  This is a no-no according to the rules.<\/p>\n
\r\n-- Delete from parent table (fails due to child record)\r\nDELETE FROM DBO.VW_JOIN_MAKES_2_MODELS WHERE MAKER_ID = 7;\r\n<\/pre>\n
\"\"<\/a><\/p>\n
The third positive test is to validate that deletion of 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-- Delete from both tables\r\nDELETE FROM DBO.VW_JOIN_MAKES_2_MODELS WHERE MAKER_ID = 7 AND MODEL_ID = 7;\r\n<\/pre>\n
\"\"<\/a><\/p>\n
In summary, we were able to add a INSTEAD OF DELETE trigger to our user defined view to make it function correctly with a DELETE statement. While the next article focus on making our view work correctly with an UPDATE statement, these trigger can be used for other purposes.  <\/p>\n
For instance, when designing a system that keeps track of large money transactions, we never want to actually delete a record.  We just want to mark it as deleted just incase the action taken by a high level user was fraudulent.  This would be another pefect use of this trigger.<\/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 DELETE 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 delete 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,33,433,80,563,12,432,736,330,15,28,614,29,613,708],"class_list":["post-4157","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-delete","tag-deleted","tag-drop-trigger","tag-drop-view","tag-free-code","tag-inserted","tag-instead-of-delete","tag-isnull","tag-john-f-miner-iii","tag-sql-server","tag-substring","tag-tsql","tag-upper","tag-user-defined-view"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/4157","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=4157"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/4157\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4157"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4157"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4157"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}