Most information technology professionals do not know that data can be modified via a VIEW. The INSERT, UPDATE, and DELETE statements of the Data Manipulation Language (DML) can be executed on a view as long as the following set of rules are adhered to.
- Modifications must reference columns from only one base table.
- The columns must directly reference the underlying data table.
I am going to continue my exploration of updatable views. Last time, we were using the [AUTOS] sample database and discovered that DELETE statements were not working the way we wanted to. Duplicate information such as [MAKER_ID2] had to be supplied in the view to allow DML statements to execute correctly. Last but not least, we could not perform an action on two tables at the same time.
Today, I will be looking at how INSTEAD OF TRIGGERS can be used to make the view work the way we want with DML statements.
Before we get into the nitty-gritty trigger details, we need to reload our test environment. The main point is that we wanted remove the old views and redefine a new view as below to removed duplicate information. 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 of trigger (insert)
- 5D – Test insert actions of instead of trigger
- 5E – Create a instead of trigger (delete)
- 5F – Test delete actions of instead of trigger
- 5G – Create a instead of trigger (update)
- 5H – Test update actions of instead of trigger
|
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 |
-- -- Updated view that has 1 x N join -- CREATE VIEW [DBO].[VW_JOIN_MAKES_2_MODELS] AS SELECT MA.[MAKER_ID] , MA.[MAKER_NM] , MA.[START_YR] , MA.[END_YR] , MO.[MODEL_ID] , MO.[MODEL_NM] , MO.[MODEL_YR] , MO.[MSRP] FROM [dbo].[MAKES] MA LEFT JOIN [dbo].[MODELS] MO ON MA.MAKER_ID = MO.MAKER_ID GO -- Review view data SELECT * FROM [DBO].[VW_JOIN_MAKES_2_MODELS] GO |
Now that we have the view the way we want, we can add the INSTEAD OF trigger. For each action, we are going to define a trigger on the [VW_JOIN_MAKES_2_MODELS] view. Today, we are going to concentrate on the INSERT action today.
Like most Data Definition Language (DDL) constructs, a user defined trigger has three operations associated with it: CREATE TRIGGER, ALTER TRIGGER and DROP TRIGGER.
Since the view is a join between two tables, we need to know if data is going to be inserted into the 1 = MAKES, 2 = MODELS or 3 = BOTH tables.
Triggers in general have inserted and deleted tables to determine the state of the data. The common table expression in the trigger checks the fields in the inserted table to determine the target table to work with.
Since the MAKER_ID and and MODEL_ID are auto increment fields, the end user updating the view has to supply the corresponding natural keys, MAKER_NM and and MODEL_NM. The trigger below accounts for the rare case in which the user does not supply any key value during a INSERT and a user defined error is raised when this situation happens.
|
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 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 |
-- -- Remove existing trigger -- IF OBJECT_ID('DBO.TRG_CORRECT_INS_ACTION_4_MAKES_2_MODELS') IS NOT NULL DROP TRIGGER [DBO].[TRG_CORRECT_INS_ACTION_4_MAKES_2_MODELS]; GO -- -- Create instead of trigger -- CREATE TRIGGER [DBO].[TRG_CORRECT_INS_ACTION_4_MAKES_2_MODELS] ON [DBO].[VW_JOIN_MAKES_2_MODELS] INSTEAD OF INSERT AS RAISERROR ('This is a stub for insert trigger.', 16, 10); GO -- -- Alter instead of trigger -- ALTER TRIGGER [DBO].[TRG_CORRECT_INS_ACTION_4_MAKES_2_MODELS] ON [DBO].[VW_JOIN_MAKES_2_MODELS] INSTEAD OF INSERT AS -- Author: John Miner -- Date: Jan 2013 -- Purpose: Perform the correct insert action for a view. BEGIN -- Declare local variables DECLARE @VAR_WHICH_TABLE INT; -- Action being performed PRINT 'INSTEAD OF INSERT ACTION - '; -- Find out what is being modified WITH CTE_INS_MODIFICATION_FLGS AS ( SELECT -- Makes table change CASE WHEN I.MAKER_NM IS NULL THEN 0 ELSE 1 END AS UNQ_FLD_TBL1, -- Models table change CASE WHEN I.MODEL_NM IS NULL THEN 0 ELSE 2 END AS UNQ_FLD_TBL2 FROM inserted AS I ) SELECT @VAR_WHICH_TABLE = AVG(UNQ_FLD_TBL1 + UNQ_FLD_TBL2) FROM CTE_INS_MODIFICATION_FLGS; -- Key fields are null IF (@VAR_WHICH_TABLE = 0) BEGIN RAISERROR('Either makers or models name must not be null when inserting data. Please try again.', 16, 1) END; -- Add to makes table IF (@VAR_WHICH_TABLE = 1) BEGIN -- Make sure name is unique INSERT INTO [DBO].[MAKES] ([MAKER_NM],[START_YR],[END_YR]) SELECT INS.MAKER_NM, INS.START_YR, INS.END_YR FROM inserted AS INS WHERE INS.MAKER_NM IS NOT NULL; END; -- Add to models table IF (@VAR_WHICH_TABLE = 2) BEGIN -- Up to user to supply valid maker id INSERT INTO [DBO].[MODELS] ([MODEL_NM],[MODEL_YR],[MSRP],[MAKER_ID]) SELECT INS.MODEL_NM, INS.MODEL_YR, INS.MSRP, INS.MAKER_ID FROM inserted AS INS WHERE INS.MODEL_NM IS NOT NULL; END; -- Add to both tables IF (@VAR_WHICH_TABLE = 3) BEGIN -- Add to parent table first INSERT INTO [DBO].[MAKES] ([MAKER_NM],[START_YR],[END_YR]) SELECT INS.MAKER_NM, INS.START_YR, INS.END_YR FROM inserted AS INS; -- Add to child table second INSERT INTO [DBO].[MODELS] ([MODEL_NM],[MODEL_YR],[MSRP],[MAKER_ID]) SELECT INS.MODEL_NM, INS.MODEL_YR, INS.MSRP, MAK.MAKER_ID FROM inserted AS INS INNER JOIN [DBO].[MAKES] AS MAK ON INS.MAKER_NM = MAK.MAKER_NM; END; END; |
Our first action should be a query of the view to see what information is stored in the tables.
|
1 2 3 |
-- Review view data SELECT * FROM [DBO].[VW_JOIN_MAKES_2_MODELS] GO |
Lets get the negative testing out of the way by trying to insert data into the view without a natural key.
|
1 2 3 |
-- Should generate an error INSERT INTO DBO.VW_JOIN_MAKES_2_MODELS (START_YR, END_YR) VALUES (2007, NULL); |
The first positive test is to insert data into both MAKES or MODELS table separately.
|
1 2 3 4 5 6 7 8 |
-- Add data to parent table INSERT INTO DBO.VW_JOIN_MAKES_2_MODELS (MAKER_NM, START_YR, END_YR) VALUES ('Tesla', 2007, NULL); -- Add data to child table INSERT INTO DBO.VW_JOIN_MAKES_2_MODELS (MAKER_ID, MODEL_NM, MODEL_YR, MSRP) VALUES (6, 'Model S', 2013, 87400); |
The second positive test is to validate that inserting data into both the MAKES and MODELS tables at the same time works. This is a new functionality that is not available in a normal view.
|
1 2 3 |
-- Add data to two tables INSERT INTO DBO.VW_JOIN_MAKES_2_MODELS (MAKER_NM, START_YR, END_YR, MODEL_NM, MODEL_YR, MSRP) VALUES ('Cadillac', 1903, NULL, 'Escalade', 2013, 63170); |
Our last action should be a query of the view to see the final state of the information in the tables.
|
1 2 3 |
-- Review view data SELECT * FROM [DBO].[VW_JOIN_MAKES_2_MODELS] GO |
In summary, we were able to add a INSTEAD OF INSERT trigger to our user defined view to make it function correctly with a INSERT statement. While the next two articles focus on making our view updatable, these trigger can be used for other purposes.
Views can be used to partition data.
Vertical partitioning can be used to divide a table with a large number of columns into two or more tables. The same techniques used in these articles can be applied to a view that joins the tables in a logical one.
Horizontal partitioning is when data is split apart at some type of boundary. Typical boundaries are event dates or key values from a dimension table. This is a quick glance at a topic I will be covering in the future.
In a nutshell, the INSTEAD OF TRIGGER is a powerful construct that should be used when to creating an updatable view.
