Creating an update trigger for a multi-table view

Creating an update trigger for a multi-table view

• ThomasBear

  Did you try applying the trigger without JOIN Just "from inserted".
  

• Ahmed Mahdy

  Thanks for you suggestion, this resolved my problem!

  
  

• trager

  What is the primary key for the table Can you post a simple repro I don't think this error message is from coming from the SQL Server. Note that you can potentially have multiple rows in the inserted table that are unique for a given table so you need to qualify the correct set of rows and update the relevant tables. In any case, it will be easier to help if you post a TSQL repro that demonstrates the problem and the actual error message you are getting (including error number, state etc).
  
  

• AussieRS

  Thank you all for your help.  Here's the additional information you requested:

  The error is definately coming from Visual Studio, but I assumed it reflects some sort of database error.  Anyway, here's the complete message:

  No row was updated.

  The data in row 1 was not committed.

  Error Source: Microsoft.VisualStudio.DataTools.

  Error Message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows (2 rows).

   

  Here are the table, view, and trigger definitions (note that I used VisualStudio to do the design, so I'm guessing at the SQL):

  CREATE TABLE MyTable (

  pkey int IDENTITY (1,1) PRIMARY KEY,

  info varchar(50)

  )

   

  CREATE TABLE MyTableExtra (

  pkey int FOREIGN KEY REFERENCES MyTable (pkey),

  pkeyTwo char(2),

  extraInfo varchar(50),

  PRIMARY KEY (pkey, pkeyTwo)

  )

   

  CREATE VIEW MyView AS

  SELECT MyTable.pkey, info, pkeyTwo, extraInfo

  FROM MyTable INNER JOIN MyTableExtra ON MyTable.pkey = MyTableExtra.pkey

   

  CREATE TRIGGER MyTrigger ON MyView

  INSTEAD OF UPDATE AS

  UPDATE MyTable

  SET info = inserted.info

  FROM inserted INNER JOIN MyTable ON inserted.pkey = MyTable.pkey

   

  This reproduces the error for me.  Note that VisualStudio sticks "dbo." all over the place, but I have ommitted those here.

   

  
  

• dogs_bollox

  Upon further investigation, I have determined that this is entirely a VisualStudio issue (the SQL I posted above works with sqlcmd). I'm still at a loss to explain it, however. Any thoughts Is there somewhere else I should post this
  

• richard.edwards

  I had this problem with a trigger today. "The row value(s) updated or deleted... (2 rows)"

  Try adding SET NOCOUNT ON to your Trigger. That fixed my problem.

  
  

• sakifcy

  1. You need to give us more information. 2. For an instead of trigger, the only thing your DML statement does is materialize an inserted and deleted table. You join to the real table to do the updated. Like Umachander says, that message sounds like something from VS, not from SQL

  Here is a view I have started for a blog on the subject. (haven't added any error handling to keep things neat.) It runs and doesn't have any errors in it, so maybe seeing it might help...

  --create two tables that comprise tableName "table"

  CREATE TABLE tableNameA
  (
  tableNameId int PRIMARY KEY,
  valueA varchar(10)
  )
  CREATE TABLE tableNameB
  (
  tableNameId int PRIMARY KEY,
  valueB varchar(10)
  )
  GO

  --this view pulls the two tables together (using FULL OUTER so either table could have a row, or not)
  CREATE VIEW tableName
  AS
  SELECT coalesce(tableNameA.tableNameId, tableNameB.tableNameId) AS tableNameId,
  tableNameA.valueA, tableNameB.valueB
  FROM tableNameA
  FULL OUTER JOIN tableNameB
  ON tableNameA.tableNameId = tableNameB.tableNameId
  GO

  --this trigger inserts rows into the base tables if values exist in inserted
  CREATE TRIGGER tableName_insteadOFInsert
  ON tableName
  INSTEAD OF INSERT
  AS
  BEGIN
  SET NOCOUNT ON

  INSERT INTO tableNameA(tableNameId,valueA)
  SELECT tableNameId, valueA
  FROM inserted
  WHERE valueA IS NOT null

  INSERT INTO tableNameB(tableNameId,valueB)
  SELECT tableNameId, valueB
  FROM inserted
  WHERE valueB IS NOT null
  END
  GO

  INSERT INTO tableName(tableNameId, valueA, valueB)
  VALUES (1, NULL, '10')
  INSERT INTO tableName(tableNameId, valueA, valueB)
  VALUES (2, '20', '20')
  INSERT INTO tableName(tableNameId, valueA, valueB)
  VALUES (3, '30', NULL)
  GO

  SELECT *
  FROM tableName
  GO

  --in the instead of update trigger, you join to the inserted table to do the UPDATE
  --I add an insert if the row doesn't yet exist for the subordinate table(s)

  CREATE TRIGGER tableName_insteadOFUpdate
  ON tableName
  INSTEAD OF UPDATE
  AS
  BEGIN
  SET NOCOUNT ON

  UPDATE tableNameA
  SET valueA = inserted.valueA
  FROM tableNameA
  JOIN inserted
  ON tableNameA.tableNameId = inserted.tableNameId
  
  IF @@rowcount = 0
  BEGIN
  INSERT INTO tableNameA(tableNameId,valueA)
  SELECT tableNameId, valueA
  FROM inserted
  WHERE valueA IS NOT NULL
  END

  UPDATE tableNameB
  SET valueB = inserted.valueB
  FROM tableNameB
  JOIN inserted
  ON tableNameB.tableNameId = inserted.tableNameId

  IF @@rowcount = 0
  BEGIN
  INSERT INTO tableNameB(tableNameId,valueB)
  SELECT tableNameId, valueB
  FROM inserted
  WHERE valueB IS NOT NULL
  END
  END
  GO

  UPDATE tableName
  SET valueA = 'U10'
  WHERE tableNameId = 1

  UPDATE tableName
  SET valueA = 'U20',
  valueB = 'U21'
  WHERE tableNameId = 2

  UPDATE tableName
  SET valueB = 'U30'
  WHERE tableNameId = 2
  GO

  SELECT *
  FROM tableName
  

  
  
  

• Ace73

  Yes, the Visual Studio forum. I will move the thread to that forum.
  
  

• Kamal Hathi

  Yes. In that case, I get the same error message, except that it says I might be altering 48 rows instead of 2 (every row in the table). In any case, inserted should contain only a single row, so that part of the message makes little sense.
  
• Creating an update trigger for a multi-table view