/****************************************************** * * Name: exploring-views-part5.sql * * Design Phase: * Author: John Miner * Date: 01-06-2013 * Blog: www.craftydba.com * * Purpose: Create a series of articles on * database developer (70-433) topics. * * Topic: Talk about views - part 5. * ******************************************************/ -- -- 5A - Remove aggregate view -- -- Use the database USE [AUTOS]; GO -- Remove aggregated view IF OBJECT_ID('DBO.VW_MAKES_AVG_MODEL_COST') IS NOT NULL DROP VIEW [DBO].[VW_MAKES_AVG_MODEL_COST]; GO -- Update view IF OBJECT_ID('DBO.VW_JOIN_MAKES_2_MODELS') IS NOT NULL DROP VIEW [DBO].[VW_JOIN_MAKES_2_MODELS]; GO -- 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 -- -- 5B - Reload tables with good data -- -- Clear the table DELETE FROM [DBO].[MODELS] GO -- Clear the table DELETE FROM [DBO].[MAKES] GO -- Reseed the sequence DBCC CHECKIDENT ('DBO.MODELS', RESEED, 0); GO -- Reseed the sequence DBCC CHECKIDENT ('DBO.MAKES', RESEED, 0); GO -- Add data to makes INSERT INTO [DBO].[MAKES] (MAKER_NM, START_YR, END_YR) VALUES ('Chevrolet', 1912, Null), ('Dodge', 1915, Null), ('Ford', 1903, Null), ('Lincoln', 1917, Null), ('Mercury', 1938, 2011); GO -- Add data INSERT INTO MODELS (MODEL_NM, MODEL_YR, MSRP, MAKER_ID) VALUES ('Corvette', 2013, 49600.00, 1), ('Ram 1500', 2013, 22589.99, 2), ('Mustang Boss 302', 2013, 42200.00, 3), ('Navigator', 2013, 57775.00, 4), ('Mariner', 2011, 23082.00, 5) GO -- Review data (parent table) SELECT * FROM [DBO].[MAKES] GO -- Review data (child table) SELECT * FROM [DBO].[MODELS] GO -- Review view data SELECT * FROM [DBO].[VW_JOIN_MAKES_2_MODELS] GO -- -- 5C - Create a instead of trigger (insert) -- -- 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 -- 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; -- -- 5D - Test insert actions of instead of trigger -- -- Review view data SELECT * FROM [DBO].[VW_JOIN_MAKES_2_MODELS] GO -- Should generate an error INSERT INTO DBO.VW_JOIN_MAKES_2_MODELS (START_YR, END_YR) VALUES (2007, NULL); -- 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); -- 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); -- -- 5E - Create a instead of trigger (delete) -- -- Remove existing trigger IF OBJECT_ID('DBO.TRG_CORRECT_DEL_ACTION_4_MAKES_2_MODELS') IS NOT NULL DROP TRIGGER [DBO].[TRG_CORRECT_DEL_ACTION_4_MAKES_2_MODELS]; GO -- Create instead of trigger CREATE TRIGGER [DBO].[TRG_CORRECT_DEL_ACTION_4_MAKES_2_MODELS] ON [DBO].[VW_JOIN_MAKES_2_MODELS] INSTEAD OF DELETE AS -- Author: John Miner -- Date: Jan 2013 -- Purpose: Perform the correct delete action for a view. BEGIN -- Declare local variables DECLARE @VAR_WHICH_TABLE INT; DECLARE @VAR_TSQL NVARCHAR(4000); DECLARE @VAR_VAL1 INT; DECLARE @VAR_VAL2 INT; -- Declare input buffer variable DECLARE @VAR_BUFFER TABLE ( [EventType] NVARCHAR(30), [Parameters] INT, [EventInfo] NVARCHAR(4000) ); -- Find the tsql that fired the trigger INSERT INTO @VAR_BUFFER EXEC sp_executesql N'DBCC INPUTBUFFER(@@spid) WITH NO_INFOMSGS'; -- Parse TSQL to get WHERE clause SELECT @VAR_TSQL = UPPER(ISNULL(B.EventInfo, '')) FROM @VAR_BUFFER B; SELECT @VAR_VAL1 = CHARINDEX('WHERE', @VAR_TSQL, 1); IF (@VAR_VAL1 > 0) SELECT @VAR_TSQL = SUBSTRING(@VAR_TSQL, @VAR_VAL1, LEN(@VAR_TSQL) - @VAR_VAL1); -- Action being performed PRINT 'INSTEAD OF DELETE ACTION - '; -- Find out what is being modified SELECT @VAR_WHICH_TABLE = 0; IF ( (CHARINDEX('MODEL_ID', @VAR_TSQL, 1) > 0) OR (CHARINDEX('MODEL_NM', @VAR_TSQL, 1) > 0) ) SELECT @VAR_WHICH_TABLE = @VAR_WHICH_TABLE + 1; IF ( (CHARINDEX('MAKER_ID', @VAR_TSQL, 1) > 0) OR (CHARINDEX('MAKER_NM', @VAR_TSQL, 1) > 0) ) SELECT @VAR_WHICH_TABLE = @VAR_WHICH_TABLE + 2; -- Key fields are null IF (@VAR_WHICH_TABLE = 0) BEGIN -- Action being performed PRINT ' NO VALID KEYS SUPPLIED. '; -- Raise an error on this action RAISERROR('Either makers or models id or name must not be null when deleting data. Please try again.', 16, 1) END; -- Delete from models table IF (@VAR_WHICH_TABLE = 1) BEGIN -- Action being performed PRINT ' DELETE FROM MODELS. '; -- Perform the delete DELETE FROM [DBO].[MODELS] WHERE MODEL_ID IN ( SELECT D.MODEL_ID FROM deleted AS D ); END; -- Delete from makes table IF (@VAR_WHICH_TABLE = 2) BEGIN -- Must be one record SELECT @VAR_VAL1 = COUNT(*) FROM deleted; -- Must not have any children SELECT @VAR_VAL2 = ISNULL(MODEL_ID, 0) FROM deleted; -- Make sure conditions are meet IF (@VAR_VAL1 = 1) AND (@VAR_VAL2 = 0) BEGIN -- Action being performed PRINT ' DELETE FROM MAKES. '; -- Perform the delete DELETE FROM [DBO].[MAKES] WHERE MAKER_ID IN ( SELECT D.MAKER_ID FROM deleted AS D ); END ELSE BEGIN -- Action being performed PRINT ' TOO MANY CHILD RECORDS EXIST. '; -- Raise an error on this action RAISERROR('There must no model records left when deleting data from makes. Please try again.', 16, 1) END; END; -- Delete from both tables IF (@VAR_WHICH_TABLE = 3) BEGIN -- Action being performed PRINT ' DELETE FROM MODELS & MAKES. '; -- Delete from child table first DELETE FROM [DBO].[MODELS] WHERE MODEL_ID IN ( SELECT D.MODEL_ID FROM deleted AS D ); -- Delete from parent table second DELETE FROM [DBO].[MAKES] WHERE MAKER_ID IN ( SELECT D.MAKER_ID FROM deleted AS D ); END; END -- -- 5F - Test delete actions of instead of trigger -- -- Add data to child table INSERT INTO DBO.VW_JOIN_MAKES_2_MODELS (MAKER_ID, MODEL_NM, MODEL_YR, MSRP) VALUES (6, 'Model X', 2013, 104000); -- Review data SELECT * FROM [DBO].[VW_JOIN_MAKES_2_MODELS] GO -- Delete should fail DELETE FROM DBO.VW_JOIN_MAKES_2_MODELS; -- Delete from child table DELETE FROM DBO.VW_JOIN_MAKES_2_MODELS WHERE MODEL_NM = 'Model X'; -- Delete from child table DELETE FROM DBO.VW_JOIN_MAKES_2_MODELS WHERE MODEL_ID = 6; -- Delete from parent table DELETE FROM DBO.VW_JOIN_MAKES_2_MODELS WHERE MAKER_NM = 'Tesla'; -- Delete from child table (Fails) DELETE FROM DBO.VW_JOIN_MAKES_2_MODELS WHERE MAKER_ID = 7; -- Delete from both tables DELETE FROM DBO.VW_JOIN_MAKES_2_MODELS WHERE MAKER_ID = 7 AND MODEL_ID = 7; -- -- 5G - Create a instead of trigger (update) -- -- Remove existing trigger IF OBJECT_ID('DBO.TRG_CORRECT_UPD_ACTION_4_MAKES_2_MODELS') IS NOT NULL DROP TRIGGER [DBO].[TRG_CORRECT_UPD_ACTION_4_MAKES_2_MODELS]; GO -- Create instead of trigger CREATE TRIGGER [DBO].[TRG_CORRECT_UPD_ACTION_4_MAKES_2_MODELS] ON [DBO].[VW_JOIN_MAKES_2_MODELS] INSTEAD OF UPDATE AS -- Author: John Miner -- Date: Jan 2013 -- Purpose: Perform the correct update action for a view. BEGIN -- Declare local variables DECLARE @VAR_WHICH_TABLE INT; DECLARE @VAR_TSQL NVARCHAR(4000); DECLARE @VAR_VAL1 INT; DECLARE @VAR_VAL2 INT; -- Declare input buffer variable DECLARE @VAR_BUFFER TABLE ( [EventType] NVARCHAR(30), [Parameters] INT, [EventInfo] NVARCHAR(4000) ); -- Find the tsql that fired the trigger INSERT INTO @VAR_BUFFER EXEC sp_executesql N'DBCC INPUTBUFFER(@@spid) WITH NO_INFOMSGS'; -- Parse TSQL to get WHERE clause SELECT @VAR_TSQL = UPPER(ISNULL(B.EventInfo, '')) FROM @VAR_BUFFER B; SELECT @VAR_VAL1 = CHARINDEX('WHERE', @VAR_TSQL, 1); IF (@VAR_VAL1 > 0) SELECT @VAR_TSQL = SUBSTRING(@VAR_TSQL, @VAR_VAL1, LEN(@VAR_TSQL) - @VAR_VAL1); -- Action being performed PRINT 'INSTEAD OF UPDATE ACTION - '; -- Find out what is being modified SELECT @VAR_WHICH_TABLE = 0; IF ( (CHARINDEX('MODEL_ID', @VAR_TSQL, 1) > 0) OR (CHARINDEX('MODEL_NM', @VAR_TSQL, 1) > 0) ) SELECT @VAR_WHICH_TABLE = @VAR_WHICH_TABLE + 1; IF ( (CHARINDEX('MAKER_ID', @VAR_TSQL, 1) > 0) OR (CHARINDEX('MAKER_NM', @VAR_TSQL, 1) > 0) ) SELECT @VAR_WHICH_TABLE = @VAR_WHICH_TABLE + 2; -- Key fields are null IF (@VAR_WHICH_TABLE = 0) BEGIN -- Action being performed PRINT ' NO VALID KEYS SUPPLIED. '; -- Raise an error on this action RAISERROR('Either makers or models id or name must not be null when updating data. Please try again.', 16, 1) END; -- Update data in models table IF (@VAR_WHICH_TABLE = 1) OR (@VAR_WHICH_TABLE = 3) BEGIN -- Action being performed PRINT ' UPDATE MODELS DATA. '; -- Perform the update UPDATE [DBO].[MODELS] SET MODEL_NM = I.MODEL_NM, MODEL_YR = I.MODEL_YR, MSRP = I.MSRP FROM [DBO].[MODELS] M INNER JOIN inserted I ON M.MODEL_ID = I.MODEL_ID; END; -- Update data in makes table IF (@VAR_WHICH_TABLE = 2) OR (@VAR_WHICH_TABLE = 3) BEGIN -- Action being performed PRINT ' UPDATE MAKES DATA. '; -- Perform the update UPDATE [DBO].[MAKES] SET MAKER_NM = I.MAKER_NM, START_YR = I.START_YR, END_YR = I.END_YR FROM [DBO].[MAKES] M INNER JOIN inserted I ON M.MAKER_ID = I.MAKER_ID; END; END; -- -- 5G - Test update actions of instead of trigger -- -- Review data SELECT * FROM [DBO].[VW_JOIN_MAKES_2_MODELS] GO -- Update single field UPDATE DBO.VW_JOIN_MAKES_2_MODELS SET MAKER_NM = 'Dodge Ram' WHERE MAKER_ID = 2; -- Update single field UPDATE DBO.VW_JOIN_MAKES_2_MODELS SET MODEL_NM = '1500' WHERE MODEL_ID = 2; -- Update two fields, restore original setting UPDATE DBO.VW_JOIN_MAKES_2_MODELS SET MAKER_NM = 'Dodge' , MODEL_NM = 'Ram 1500' WHERE MAKER_ID = 2 AND MODEL_ID = 2;