locked
INSTEAD OF UPDATE Trigger on View of Pivoted Data RRS feed

  • Question

  • Hi All,

    Running SQL Server 2008 R2.  We have a requirement to pivot data contained within an EAV table.  

    Sample DDL and Insert Statement:

    CREATE TABLE [dbo].[Test]
        (
        [TestID]      [varchar](50) NOT NULL,
        [DocumentID]  [varchar](50) NULL,
        [Description] [varchar](50) NULL,
        [Value]       [varchar](50) NULL,
        CONSTRAINT [PK_Test_TestID] PRIMARY KEY CLUSTERED ([TestID] ASC)
        WITH
            (
    	PAD_INDEX = OFF,
    	STATISTICS_NORECOMPUTE = OFF,
    	IGNORE_DUP_KEY = OFF,
    	ALLOW_ROW_LOCKS = ON,
    	ALLOW_PAGE_LOCKS = ON
    	)
            ON [PRIMARY]
        )
        ON [PRIMARY]
    GO
    
    INSERT INTO [dbo].[Test]
        ([TestID], [DocumentID], [Description], [Value])
    VALUES
        (1, 1, 'Notes 1', 'ABC'),
        (2, 1, 'Notes 2', NULL),
        (3, 1, 'Notes 3', 'XYZ'),
        (4, 1, 'Notes 4', 'Flying Fish'),
        (5, 1, 'Notes 5', 'Grass'),
        (6, 2, 'Notes 1', 'Test'),
        (7, 2, 'Notes 2', 'DEF'),
        (8, 2, 'Notes 3', '678'),
        (9, 2, 'Notes 4', NULL),
        (10, 2, 'Notes 5', 'Blah'),
        (11, 3, 'Notes 1', NULL),
        (12, 3, 'Notes 2', 'Alphabet'),
        (13, 3, 'Notes 3', NULL),
        (14, 3, 'Notes 4', 'N/A'),
        (15, 3, 'Notes 5', NULL)

    We've constructed a view to display the data in a pivoted format, which gives an output similar to the layout below:

    [DocumentID] | [Notes 1] | [Notes 2] | [Notes 3] | [Notes 4] | [Notes 5]
    1  | ABC     |           | XYZ       | Flying    | Grass
    2  | Test    | DEF       | 678       |           | Blah
    3  |         | Alphabet  |           | N/A       |
    However our requirements have evolved and we're now required to make this view updateable.  Do we have enough data contained within the layout to allow an INSTEAD OF UPDATE trigger to accomplish this task?  A couple of things to point out:

    a) Data modified through the view would never be inserted or deleted, merely updated.
    b) The pivoted columns in the view (the [Notes x] columns) will always be unique, so we shouldn't have to worry about having duplicate descriptions for a single DocumentID in the base table.

    How would I go about designing the INSTEAD OF UPDATE trigger for this task?

    Any help is greatly appreciated!

    Best Regards
    Brad
    Monday, March 7, 2016 7:56 PM

Answers

  • Something like this - I present the idea only, don't have time to test:

    CREATE TRIGGER [viewTest_InsteadOf_Update] ON [dbo].[viewTest] INSTEAD OF UPDATE
    AS
    BEGIN
    	
        SET NOCOUNT ON;
    	
        DECLARE @newID [varchar](50);
    
        declare @ChangedRows table (DocumentId varchar(50), Description varchar(50), Value varchar(50))
    insert into @ChangedRows (DocumentId, Description, Value)
    SELECT
    			[DocumentID],
    			[Description],
    			NULLIF([Value], '') AS [Value]
    		FROM
    			(
    			SELECT
    				[DocumentID],
    				ISNULL([Notes 1], '') AS [Notes 1],
    				ISNULL([Notes 2], '') AS [Notes 2],
    				ISNULL([Notes 3], '') AS [Notes 3],
    				ISNULL([Notes 4], '') AS [Notes 4],
    				ISNULL([Notes 5], '') AS [Notes 5]
    			FROM
    				[Inserted]
    			) [src]
    		UNPIVOT
    			(
    			[Value] FOR [Description] IN ([Notes 1], [Notes 2], [Notes 3], [Notes 4], [Notes 5])
    			) [upvt]
    
     MERGE [dbo].[Test] WITH (HOLDLOCK) T
        USING @ChangedRows S
        ON
    		[T].[DocumentID] = [S].[DocumentID]
        AND
    		
    			[T].[Description] = [S].[Description]
    		
    		
        WHEN MATCHED THEN
    		UPDATE SET [T].[Value] = [S].[Value]
    
    select * into #RowsToInsert from @ChangedRows CR 
    where not exists (select 1 from dbo.Test T where
    T.DocumentId = CR.DocumentId and T.Description = CR.Description) -- we may also want to exclude empty value rows, probably
    
    -- Now process this #RowsToInsert table row by row using a CURSOR and calling your procedure to insert values 
    
    
        
    
        
    	
    END
    GO


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by 2012S4 Tuesday, March 15, 2016 5:17 PM
    Thursday, March 10, 2016 5:26 PM

All replies

  • What exactly do you mean - do you need to update the underlying Test table?

    If yes, then it should be relatively easy:

    ;with cte as (select T.*, I.Note1, I.Note2, ..., ROW_NUMBER() over (partition by T.DocumentId order by T.TestId) as Rn

    from Test T inner join Inserted I on T.DocumentId = I.DocumentId)

    update cte set Description = case Rn when 1 then Note1 when 2 then Note2 when 3 then Note3 etc end


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, March 7, 2016 8:12 PM
  • Hi Naomi,

    Yes I would like to update the underlying value in the Test table.  However, is there a way to do this without the case statement?  I'd like the trigger to be dynamic, as the EAV attributes are subject to addition and change (we have a solution for that part of it).

    Almost something like UPDATE Test SET Value = <Column/Row Value in View> WHERE DocumentID = <DocumentID in View Row> AND Description = <Name of View Column>

    Thanks!

    • Edited by 2012S4 Monday, March 7, 2016 8:21 PM Add example update statement
    Monday, March 7, 2016 8:17 PM
  • You would need a dynamic statement for that then. The idea is similar to what I presented but you would generate that statement dynamically using system views to get the necessary columns.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, March 7, 2016 8:25 PM
  • Hi Naomi,

    Could you provide an example of what you mean/how I'd go about generating the dynamic statement?  I'm having trouble envisioning it.

    Thanks.

    Monday, March 7, 2016 8:36 PM
  • Search this forum for dynamic unpivot. I think I may have samples of that in my articles too, say, this latest article  T-SQL: Finding Difference in Columns in the Table may point in the right direction too.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, March 7, 2016 8:49 PM
  • Hi Naomi,

    I'm still not getting how to implement your suggested solution.  I can UNPIVOT the view easily enough, but I don't understand where specifically in the trigger to implement this, and how to ensure the column value in the view updates the correct row/column in the base table.

    This is my first foray with triggers, so apologies for my lack of understanding.

    Thanks!

    Monday, March 7, 2016 9:53 PM
  • Hi Naomi,

    Yes I would like to update the underlying value in the Test table.  However, is there a way to do this without the case statement?  I'd like the trigger to be dynamic, as the EAV attributes are subject to addition and change (we have a solution for that part of it).

    Almost something like UPDATE Test SET Value = <Column/Row Value in View> WHERE DocumentID = <DocumentID in View Row> AND Description = <Name of View Column>

    Thanks!

    No you don't.  Google around for EAV disasters. I have been repair them for decades; it is good money for a consultant, but you feel bad when the client fails. 

    https://www.simple-talk.com/sql/t-sql-programming/avoiding-the-eav-of-destruction/

    http://karwin.blogspot.com/2009/05/eav-fail.html

    Bill put it very nicely in a reply on his blog:

     I realize it's possible to make a functioning system with EAV.

    But that's like saying one can build a house of bricks without using mortar. You can make it stand if you're careful enough, but you better not lean against it! ;-)

     

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, March 7, 2016 9:55 PM
  • Try

    create trigger UpdateEAVTable on PivotedView INSTEAD OF UPDATE

    AS

    set nocount on;

    ;with cte as (select * from Inserted UNPIVOT (Value for Description IN ([Notes1],[Notes2], ..)) unpvt)

    merge dbo.Test as target

    using cte as source on target.DocumentId = source.DocumentId and target.Description = source.Description

    when matched then update

    set value = source.value;

    ------

    From the top of my head. 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Edited by Naomi N Monday, March 7, 2016 10:48 PM
    Monday, March 7, 2016 10:48 PM
  • Hi Naomi,

    Thanks for the help, this has definitely set me on the right track.  I can currently update values within the view, and clear values that are currently set.  My last problem now rests with MERGE.  When looking at a row/column with a NULL value I can't change it.

    I'm assuming this has to do with the WITH MATCHING statement, as NULL is evaluated differently.  How would I change this to accomodate for updating values from the view where the original value was NULL?

    Thanks!

    Tuesday, March 8, 2016 3:26 AM
  • You can use this 

    ;with cte as (select * from Inserted UNPIVOT (Value for Description IN ([Notes1],[Notes2], ..)) unpvt)

    merge dbo.Test as target

    using cte as source on target.DocumentId = source.DocumentId and (target.Description = source.Description OR (target.Description IS NULL and Source.Description IS NULL))


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, March 8, 2016 5:31 AM
  • Hi Naomi, One other thing I noticed: there may be cases where an attribute could be added to the EAV table and need to be updated on documents after the fact. I added where not matched by source and the appropriate insert statement in the trigger to achieve this but I get an error about a duplicate key insert. I'm obtaining an unused value for the pk so I think the trigger is firing more than once. How would I address this? Thanks!
    Tuesday, March 8, 2016 8:58 PM
  • I am not sure I understand the issue. Can you please provide the whole picture - the table, the view and your current trigger's code and some data and desired result?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, March 8, 2016 9:23 PM
  • Hi Naomi,

    As requested:

    CREATE TABLE [dbo].[Test]
        (
        [TestID]      [varchar](50) NOT NULL,
        [DocumentID]  [varchar](50) NULL,
        [Description] [varchar](50) NULL,
        [Value]       [varchar](50) NULL,
        CONSTRAINT [PK_Test_TestID] PRIMARY KEY CLUSTERED ([TestID] ASC)
        WITH
            (
    	PAD_INDEX = OFF,
    	STATISTICS_NORECOMPUTE = OFF,
    	IGNORE_DUP_KEY = OFF,
    	ALLOW_ROW_LOCKS = ON,
    	ALLOW_PAGE_LOCKS = ON
    	)
            ON [PRIMARY]
        )
        ON [PRIMARY]
    GO
    
    INSERT INTO [dbo].[Test]
        ([TestID], [DocumentID], [Description], [Value])
    VALUES
        (1, 1, 'Notes 1', 'ABC'),
        (2, 1, 'Notes 2', NULL),
        (3, 1, 'Notes 3', 'XYZ'),
        (4, 1, 'Notes 4', 'Flying Fish'),
        (5, 2, 'Notes 1', 'Test'),
        (6, 2, 'Notes 2', 'DEF'),
        (7, 2, 'Notes 3', '678'),
        (8, 2, 'Notes 4', NULL),
        (9, 2, 'Notes 5', 'Blah'),
        (10, 3, 'Notes 1', NULL),
        (11, 3, 'Notes 2', 'Alphabet'),
        (12, 3, 'Notes 3', NULL),
        (13, 3, 'Notes 4', 'N/A'),
        (14, 3, 'Notes 5', NULL)
    
    CREATE VIEW [dbo].[viewTest]
    AS
    SELECT
        [DocumentID],
        NULLIF([Notes 1], '') AS [Notes 1],
        NULLIF([Notes 2], '') AS [Notes 2],
        NULLIF([Notes 3], '') AS [Notes 3],
        NULLIF([Notes 4], '') AS [Notes 4],
        NULLIF([Notes 5], '') AS [Notes 5]
    FROM
        (
        SELECT
            [DocumentID],
            [Description],
            [Value]
        FROM
            [dbo].[Test]
        ) [src]
    PIVOT
        (
        MAX([Value]) FOR [Description] IN ([Notes 1], [Notes 2], [Notes 3], [Notes 4], [Notes 5])
        ) [pvt]
    
    CREATE TRIGGER [viewTest_InsteadOf_Update] ON [dbo].[viewTest]
    INSTEAD OF UPDATE
    AS
    BEGIN
        SET NOCOUNT ON;
        WITH [CTE] AS
            (
    	SELECT
    	    [DocumentID],
    	    [Description],
    	    NULLIF([Value], '') AS [Value]
            FROM
    	    (
    	    SELECT
    		[DocumentID],
    		[Notes 1],
    		[Notes 2],
    		[Notes 3],
    		[Notes 4],
    		[Notes 5]
    	    FROM
    		[Inserted]
    	    ) [src]
    	UNPIVOT
    	    (
    	    [Value] FOR [Description] IN ([Notes 1], [Notes 2], [Notes 3], [Notes 4], [Notes 5])
    	    ) [upvt]
    	)
        MERGE [dbo].[Test] WITH (HOLDLOCK) [T]
        USING [CTE]
    	[S]
        ON
    	[T].[DocumentID] = [S].[DocumentID]
        AND
            (
    	    [T].[Description] = [S].[Description]
    	OR
    	    (
    		[T].[Description] IS NULL
    	    AND
    		[S].[Description] IS NULL
    	    )
    	)
        WHEN MATCHED THEN
    	UPDATE SET [T].[Value] = [S].[Value]
        WHEN NOT MATCHED BY TARGET THEN
    	INSERT
    	    ([TestID], [DocumentID], [Description], [Value])
    	VALUES
    	    ('15', [S].[DocumentID], [S].[Description], [S].[Value])
        ;
    END

    As you can see by the sample data provided, there is no row in the Test table with a DocumentID of 1 and Description of Notes 5 (so for example 'Notes 5' was added to the EAV table after DocumentID 1 was created).  Updating the view works in all other cases, as the row for the pivoted column already exists in the base table.  However, when this isn't the case I get:

    No row was updated.

    Violation of PRIMARY KEY constraint 'PK_Test_TestID'.  Cannot insert duplicate key in object 'dbo.Test'.  The duplicate key value is (15).  The statement has been terminated.

    As I know that TestID 15 doesn't exist in the base table at the time of the view update, my only guess is that the trigger is firing multiple times, however it's almost like the entire trigger operation rolls back on failure, as I don't wind up with a row with a TestID of 15 in the base table after I receive the error above.

    Thanks!

    Tuesday, March 8, 2016 11:37 PM
  • Works fine for me using your sample:

    use TempDB;
    CREATE TABLE [dbo].[Test]
        (
        [TestID]      [varchar](50) NOT NULL,
        [DocumentID]  [varchar](50) NULL,
        [Description] [varchar](50) NULL,
        [Value]       [varchar](50) NULL,
        CONSTRAINT [PK_Test_TestID] PRIMARY KEY CLUSTERED ([TestID] ASC)
        WITH
            (
    	PAD_INDEX = OFF,
    	STATISTICS_NORECOMPUTE = OFF,
    	IGNORE_DUP_KEY = OFF,
    	ALLOW_ROW_LOCKS = ON,
    	ALLOW_PAGE_LOCKS = ON
    	)
            ON [PRIMARY]
        )
        ON [PRIMARY]
    GO
    
    INSERT INTO [dbo].[Test]
        ([TestID], [DocumentID], [Description], [Value])
    VALUES
        (1, 1, 'Notes 1', 'ABC'),
        (2, 1, 'Notes 2', NULL),
        (3, 1, 'Notes 3', 'XYZ'),
        (4, 1, 'Notes 4', 'Flying Fish'),
        (5, 2, 'Notes 1', 'Test'),
        (6, 2, 'Notes 2', 'DEF'),
        (7, 2, 'Notes 3', '678'),
        (8, 2, 'Notes 4', NULL),
        (9, 2, 'Notes 5', 'Blah'),
        (10, 3, 'Notes 1', NULL),
        (11, 3, 'Notes 2', 'Alphabet'),
        (12, 3, 'Notes 3', NULL),
        (13, 3, 'Notes 4', 'N/A'),
        (14, 3, 'Notes 5', NULL)
    go
    
    CREATE VIEW [dbo].[viewTest]
    AS
    SELECT
        [DocumentID],
        NULLIF([Notes 1], '') AS [Notes 1],
        NULLIF([Notes 2], '') AS [Notes 2],
        NULLIF([Notes 3], '') AS [Notes 3],
        NULLIF([Notes 4], '') AS [Notes 4],
        NULLIF([Notes 5], '') AS [Notes 5]
    FROM
        (
        SELECT
            [DocumentID],
            [Description],
            [Value]
        FROM
            [dbo].[Test]
        ) [src]
    PIVOT
        (
        MAX([Value]) FOR [Description] IN ([Notes 1], [Notes 2], [Notes 3], [Notes 4], [Notes 5])
        ) [pvt]
    
    go
    select * from dbo.viewTest
    go
    
    ALTER TRIGGER [viewTest_InsteadOf_Update] ON [dbo].[viewTest]
    INSTEAD OF UPDATE
    AS
    BEGIN
        SET NOCOUNT ON;
        WITH [CTE] AS
            (
    	SELECT
    	    [DocumentID],
    	    [Description],
    	    NULLIF([Value], '') AS [Value]
            FROM
    	    (
    	    SELECT
    		[DocumentID],
    		[Notes 1],
    		[Notes 2],
    		[Notes 3],
    		[Notes 4],
    		[Notes 5]
    	    FROM
    		[Inserted]
    	    ) [src]
    	UNPIVOT
    	    (
    	    [Value] FOR [Description] IN ([Notes 1], [Notes 2], [Notes 3], [Notes 4], [Notes 5])
    	    ) [upvt]
    	)
    
    	--select S.*, T.*
    	--from cte S left join dbo.Test T on [T].[DocumentID] = [S].[DocumentID]
     --  AND
            
    	--    [T].[Description] = [S].[Description];
    
        MERGE [dbo].[Test] WITH (HOLDLOCK) [T]
        USING [CTE]
    	[S]
        ON
    	[T].[DocumentID] = [S].[DocumentID]
        AND
            (
    	    [T].[Description] = [S].[Description]
    	
    	    )
    	
        WHEN MATCHED THEN
    	UPDATE SET [Value] = [S].[Value]
        WHEN NOT MATCHED BY TARGET THEN
    	INSERT
    	    ([TestID], [DocumentID], [Description], [Value])
    	VALUES
    	    ('15', [S].[DocumentID], [S].[Description], [S].[Value])
        ;
    END
    
    go
    
    update dbo.viewTest set  [Notes 5] = 'Test4' where DocumentID = 1
    
    select * from dbo.Test

    I ran the update command a few times, I did get the new row correctly inserted.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, March 9, 2016 4:12 AM
  • Hi Naomi,

    I've done some more testing and have found some odd behaviour.  I will attach the DDL and Sample Data again because I've done some modification to the trigger to output the data being worked with to assist in debugging:

    -- Object: Table [dbo].[Test]
    IF OBJECT_ID('dbo.Test', 'U') IS NOT NULL
    	DROP TABLE [dbo].[Test]
    GO
    CREATE TABLE [dbo].[Test]
        (
        [TestID]      [varchar](50) NOT NULL,
        [DocumentID]  [varchar](50) NULL,
        [Description] [varchar](50) NULL,
        [Value]       [varchar](50) NULL,
        CONSTRAINT [PK_Test_TestID] PRIMARY KEY CLUSTERED ([TestID] ASC)
        WITH
            (
    		PAD_INDEX = OFF,
    		STATISTICS_NORECOMPUTE = OFF,
    		IGNORE_DUP_KEY = OFF,
    		ALLOW_ROW_LOCKS = ON,
    		ALLOW_PAGE_LOCKS = ON
    		)
    		ON [PRIMARY]
        )
        ON [PRIMARY]
    GO
    
    -- Object: Table [dbo].[Test]
    INSERT INTO [dbo].[Test]
        ([TestID], [DocumentID], [Description], [Value])
    VALUES
        (1, 1, 'Notes 1', NULL),
        (2, 1, 'Notes 2', NULL),
        (3, 1, 'Notes 3', 'XYZ'),
        (4, 2, 'Notes 1', 'Test'),
        (5, 2, 'Notes 2', 'DEF'),
        (6, 2, 'Notes 3', '678'),
        (7, 2, 'Notes 4', NULL),
        (8, 3, 'Notes 1', NULL),
        (9, 3, 'Notes 2', 'Alphabet'),
        (10, 3, 'Notes 3', NULL),
        (11, 3, 'Notes 4', NULL),
        (12, 3, 'Notes 5', NULL)
    
    -- Object: View [dbo].[viewTest]
    IF OBJECT_ID('dbo.viewTest', 'V') IS NOT NULL
    	DROP VIEW [dbo].[viewTest]
    GO
    CREATE VIEW [dbo].[viewTest]
    AS
    SELECT
        [DocumentID],
        NULLIF([Notes 1], '') AS [Notes 1],
        NULLIF([Notes 2], '') AS [Notes 2],
        NULLIF([Notes 3], '') AS [Notes 3],
        NULLIF([Notes 4], '') AS [Notes 4],
        NULLIF([Notes 5], '') AS [Notes 5]
    FROM
        (
        SELECT
            [DocumentID],
            [Description],
            [Value]
        FROM
            [dbo].[Test]
        ) [src]
    PIVOT
        (
        MAX([Value]) FOR [Description] IN ([Notes 1], [Notes 2], [Notes 3], [Notes 4], [Notes 5])
        ) [pvt]
    GO
    
    -- Object: Trigger [dbo].[viewTest_InsteadOf_Update]
    CREATE TRIGGER [viewTest_InsteadOf_Update] ON [dbo].[viewTest] INSTEAD OF UPDATE
    AS
    BEGIN
    	
        SET NOCOUNT ON;
    	
    	IF OBJECT_ID('dbo.TriggerTest', 'U') IS NOT NULL
    		DROP TABLE [dbo].[TriggerTest];
    	
        WITH [CTE] AS
    		(
    		SELECT
    			[DocumentID],
    			[Description],
    			NULLIF([Value], '') AS [Value]
    		FROM
    			(
    			SELECT
    				[DocumentID],
    				ISNULL([Notes 1], '') AS [Notes 1],
    				ISNULL([Notes 2], '') AS [Notes 2],
    				ISNULL([Notes 3], '') AS [Notes 3],
    				ISNULL([Notes 4], '') AS [Notes 4],
    				ISNULL([Notes 5], '') AS [Notes 5]
    			FROM
    				[Inserted]
    			) [src]
    		UNPIVOT
    			(
    			[Value] FOR [Description] IN ([Notes 1], [Notes 2], [Notes 3], [Notes 4], [Notes 5])
    			) [upvt]
    		)
    	
    	SELECT
    		*
    	INTO
    		[dbo].[TriggerTest]
    	FROM
    		[CTE];
    	
    	WITH [CTE] AS
    		(
    		SELECT
    			[DocumentID],
    			[Description],
    			NULLIF([Value], '') AS [Value]
    		FROM
    			(
    			SELECT
    				[DocumentID],
    				ISNULL([Notes 1], '') AS [Notes 1],
    				ISNULL([Notes 2], '') AS [Notes 2],
    				ISNULL([Notes 3], '') AS [Notes 3],
    				ISNULL([Notes 4], '') AS [Notes 4],
    				ISNULL([Notes 5], '') AS [Notes 5]
    			FROM
    				[Inserted]
    			) [src]
    		UNPIVOT
    			(
    			[Value] FOR [Description] IN ([Notes 1], [Notes 2], [Notes 3], [Notes 4], [Notes 5])
    			) [upvt]
    		)
        
        MERGE [dbo].[Test] WITH (HOLDLOCK) [T]
        USING [CTE]
    		[S]
        ON
    		[T].[DocumentID] = [S].[DocumentID]
        AND
    		(
    			[T].[Description] = [S].[Description]
    		OR
    			(
    				[T].[Description] IS NULL
    			AND
    				[S].[Description] IS NULL
    			)
    		)
        WHEN MATCHED THEN
    		UPDATE SET [T].[Value] = [S].[Value]
        WHEN NOT MATCHED BY TARGET THEN
    		INSERT
    			([TestID], [DocumentID], [Description], [Value])
    		VALUES
    			(13, [S].[DocumentID], [S].[Description], [S].[Value])
        ;
    	
    END
    GO

    Now, here's the combinations I've tried and their results:

    DocumentID 1: if I try to assign a value for Notes 1, I get a PK violation.

    DocumentID 2: if I try to assign a value for Notes 4, it updates the existing row accordingly, but also inserts a row for Notes 5, using the value of 13 (I will manually delete this row to continue testing as my TestID is hardcoded as 13 in the trigger).

    DocumentID 1: if I try to assign a value to Notes 5, I get the same PK violation.

    DocumentID 3: I can assign any value to the columns in the view, no errors and the update happens as it should.

    It seems as though the PK violations are occurring as a result of there being more than one missing row in the base table for DocumentID 1.  The trigger seems to be trying to insert the number of missing rows.

    Hopefully this gives you enough information to reproduce on your end.  Thanks so much for your help thus far, it is greatly appreciated!

    Thanks!

    Wednesday, March 9, 2016 11:28 PM
  • Given the schema you have, you are going to have a very hard time doing this.  I think you may want to consider changing the schema.

    The first question I would have is why is TestID a varchar(50)?  It would seem to me you would want an int with a sequence or identity property.  If you don't have a way for SQL to automatically generate new values, you will have difficulty generating the new value for rows you need to insert into Test since a single row insert or update in viewTest can generate multiple new rows in Test.  And for sure, you cannot hard code it as 13.  But it is very likely the best choice may be to remove TestID altogether and make your primary key be the combination (DocumentID, Description).  Of course, then DocumentID and Description would have to be NOT NULL, but you probably want to do that anyway (see below).

    Also, you need to look at the nullability of your columns in Test.  You allow DocumentID to be null.  Why?  What does it mean if you have a null DocumentID?  If it is not allowed, then set this column to NOT NULL.  And if it is allowed, what should your view and trigger be doing with NULL DocumentID's?  I think you need to answer the same questions about the Description column.  I don't see much sense in either one of these columns ever being null, but perhaps you have a reason for it and a definition of how you want NULLs in those columns to be handled.

    I have a somewhat different question about you allowing Value to be NULL.  What (if any) is the difference in meaning when a particular Description for a particular DocumentID has the Value column set to NULL and when that Description doesn't exist for that DocumentID?  For example, for DocumentID 1, Description 'Notes 1', Value is null.  But no row exists for Description 'Notes 4'.  They will both show up in your view as NULL.  If a Value of NULL is different than the case when a row for that Description does not exist you are going to have problems because those two cases look exactly the same in your view.  If the meaning is the same, you may want to set Value to NOT NULL and delete the row (if it exists) when the user returns an update setting some Description's Value to NULL.

    Finally, you will have problems if multiple rows in Test have the same DocumentID and Description.  What would it mean if you have one row with DocumentID = 1, Description = 'Notes 1' and Value = 'ABC' and a different row with DocumentID = 1, Description = 'Notes 1' and Value = 'XYZ'?  Of course, if you make (DocumentID, Description) your primary key (see above) that can't ever happen.  But if you leave TestID as you primary key, you need to add a Unique constraint on (DocumentID, Description).

    Tom



    • Edited by Tom Cooper Thursday, March 10, 2016 6:28 AM
    • Proposed as answer by Naomi N Thursday, March 10, 2016 4:06 PM
    Thursday, March 10, 2016 6:25 AM
  • I agree with Tom. Your INSERT case scenario is not going to work. You only will be able to do UPDATE part of the query.

    The Insertions of the new values will have to be handles differently checking for not empty/null value, etc.

    So, it is a more complex problem.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, March 10, 2016 4:08 PM
  • Hi Naomi,

    I've figured out a solution to the problem I most recently described.  I've also come up with a Stored Procedure that gives me the next ID to be used in the table, which works fine as long as I'm not updating the row in such a manner where multiple inserts are required.  In a multiple insert scenario, I get the PK violation because the value returned by the SP has been used in the first insert.

    My question now is how would I be able to return and use the SP output as the PK for each INSERT performed by the trigger?

    Thanks!

    Thursday, March 10, 2016 4:17 PM
  • Hi Tom,

    Thanks for your feedback.  I understand this isn't the ideal scenario, however in this particular project we have no alternative but to offer somewhat of an 'extensible' schema.  As for your concerns regarding the schema design, the NULLable columns and the potential for duplicates, we have logic in a different layer that handles these concerns.

    I'd love to work in an industry where business rules are iron-clad, however that's not the case where I am.  Business rules are rules until someone needs them to not be - then they're options.

    However with that said, I do appreciate you taking the time and expressing your thoughts - always nice to know I'm not the only one that thought this wasn't the greatest idea.

    Thanks!

    Thursday, March 10, 2016 4:24 PM
  • For the rows you would need to insert you will have to run a loop (CURSOR) and call this SP for each row separately. Assuming you don't do many insertions, it is OK. If you need to insert many (more than 1K) rows at once, the CURSOR will become too slow.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, March 10, 2016 4:37 PM
  • Hi Naomi,

    -- Object: Trigger [dbo].[viewTest_InsteadOf_Update]
    CREATE TRIGGER [viewTest_InsteadOf_Update] ON [dbo].[viewTest] INSTEAD OF UPDATE
    AS
    BEGIN
    	
        SET NOCOUNT ON;
    	
        DECLARE @newID [varchar](50);
    
        EXEC [dbo].[GetNextID]
            @idType = 'Test',
            @newID = @newID OUTPUT;
    
        WITH [CTE] AS
    		(
    		SELECT
    			[DocumentID],
    			[Description],
    			NULLIF([Value], '') AS [Value]
    		FROM
    			(
    			SELECT
    				[DocumentID],
    				ISNULL([Notes 1], '') AS [Notes 1],
    				ISNULL([Notes 2], '') AS [Notes 2],
    				ISNULL([Notes 3], '') AS [Notes 3],
    				ISNULL([Notes 4], '') AS [Notes 4],
    				ISNULL([Notes 5], '') AS [Notes 5]
    			FROM
    				[Inserted]
    			) [src]
    		UNPIVOT
    			(
    			[Value] FOR [Description] IN ([Notes 1], [Notes 2], [Notes 3], [Notes 4], [Notes 5])
    			) [upvt]
    		)
    	
        MERGE [dbo].[Test] WITH (HOLDLOCK) [T]
        USING [CTE]
    		[S]
        ON
    		[T].[DocumentID] = [S].[DocumentID]
        AND
    		(
    			[T].[Description] = [S].[Description]
    		OR
    			(
    				[T].[Description] IS NULL
    			AND
    				[S].[Description] IS NULL
    			)
    		)
        WHEN MATCHED THEN
    		UPDATE SET [T].[Value] = [S].[Value]
        WHEN NOT MATCHED BY TARGET AND [S].[Value] IS NOT NULL THEN
    		INSERT
    			([TestID], [DocumentID], [Description], [Value])
    		VALUES
    			(@newID, [S].[DocumentID], [S].[Description], [S].[Value])
        ;
    	
    END
    GO
    As posted above, could you indicate where in the code I should be placing the loop?  I'm not sure whether it's a simple as placing after the WHERE NOT MATCHED BY TARGET statement, or if it's something more complex.  Please advise.

    Thanks!

    Thursday, March 10, 2016 5:10 PM
  • Something like this - I present the idea only, don't have time to test:

    CREATE TRIGGER [viewTest_InsteadOf_Update] ON [dbo].[viewTest] INSTEAD OF UPDATE
    AS
    BEGIN
    	
        SET NOCOUNT ON;
    	
        DECLARE @newID [varchar](50);
    
        declare @ChangedRows table (DocumentId varchar(50), Description varchar(50), Value varchar(50))
    insert into @ChangedRows (DocumentId, Description, Value)
    SELECT
    			[DocumentID],
    			[Description],
    			NULLIF([Value], '') AS [Value]
    		FROM
    			(
    			SELECT
    				[DocumentID],
    				ISNULL([Notes 1], '') AS [Notes 1],
    				ISNULL([Notes 2], '') AS [Notes 2],
    				ISNULL([Notes 3], '') AS [Notes 3],
    				ISNULL([Notes 4], '') AS [Notes 4],
    				ISNULL([Notes 5], '') AS [Notes 5]
    			FROM
    				[Inserted]
    			) [src]
    		UNPIVOT
    			(
    			[Value] FOR [Description] IN ([Notes 1], [Notes 2], [Notes 3], [Notes 4], [Notes 5])
    			) [upvt]
    
     MERGE [dbo].[Test] WITH (HOLDLOCK) T
        USING @ChangedRows S
        ON
    		[T].[DocumentID] = [S].[DocumentID]
        AND
    		
    			[T].[Description] = [S].[Description]
    		
    		
        WHEN MATCHED THEN
    		UPDATE SET [T].[Value] = [S].[Value]
    
    select * into #RowsToInsert from @ChangedRows CR 
    where not exists (select 1 from dbo.Test T where
    T.DocumentId = CR.DocumentId and T.Description = CR.Description) -- we may also want to exclude empty value rows, probably
    
    -- Now process this #RowsToInsert table row by row using a CURSOR and calling your procedure to insert values 
    
    
        
    
        
    	
    END
    GO


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by 2012S4 Tuesday, March 15, 2016 5:17 PM
    Thursday, March 10, 2016 5:26 PM
  • Hi Naomi,

    Your solution as suggested did the trick.  Thanks very much for your assistance with this!

    Best Regards

    Brad

    Tuesday, March 15, 2016 5:17 PM