4776

How to write an INSTEAD OF INSERT trigger on a multi table view that works with identities?

I have two tables that have a 1:1 relationship and use IDENTITY columns for their primary keys.

A view is joining both tables together to give the impression that all columns are really stored in one table.

For viewing purposes this is fine, but the view needs an INSTEAD OF INSERT trigger as well.

How do you write such a trigger that splits the column values across both tables?

<strong>Note</strong>: There is no candidate key other than the identity column in the parent table. Otherwise this question may help:

INSERT INTO View, INSTEAD OF Trigger, Identity, multiple tables?

(There is also a possible solution that declares a variable for every field in an answer of that question.)

Simplified schema (I left out PKEYs, FKEYs, COLLATIONS, etc.):

CREATE TABLE [dbo].[parent] ( [parent_id] INT IDENTITY(1,1) , [name] NVARCHAR(100) ) CREATE TABLE [dbo].[child] ( [parent_id] INT NOT NULL , [child_id] INT IDENTITY(1,1) , [name] NVARCHAR(100) ) GO CREATE VIEW [dbo].[parent_child] AS SELECT par.[parent_id] , par.[name] AS "parent_name" , chi.[child_id] , chi.[name] AS "child_name" FROM [dbo].[parent] par LEFT OUTER JOIN [dbo].[child] chi ON chi.[parent_id] = par.[parent_id] GO

The trigger template:

CREATE TRIGGER [dbo].[parent_child_instead_of_insert] ON [dbo].[parent_child] INSTEAD OF INSERT AS BEGIN -- Implementation here END GO

Example data:

INSERT INTO [dbo].[parent_child] ( [parent_name] , [child_name] ) SELECT 'parent1' , 'child1' UNION SELECT 'parent2' , 'child2'

Answer1:

Inspired by the question Future-proofing an INSTEAD OF INSERT trigger I came up with a solution which uses a temporary table #inserted that is a complete copy of the inserted table.

The trigger adds a temporary identity column on that table to make it possible to iterate over the inserted rows using a unique value.

The rest is then a simple loop using a cursor that inserts each row into the parent table first and uses the SCOPE_IDENTITY() to insert the child row.

This solution has the advantage over the "declare a var for every column" solution that you have to fetch only the temporary identity value instead of all columns.

Performance wise it is probably not very good, because all data in the inserted table must be copied.

SELECT * INTO [dbo].[#inserted] FROM [inserted] ALTER TABLE [dbo].[#inserted] ADD [temp_id] INT IDENTITY(1,1) DECLARE @temp_id int DECLARE cur CURSOR FOR SELECT [temp_id] FROM [dbo].[#inserted] OPEN cur FETCH cur INTO @temp_id WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO [dbo].[parent] ( [name] ) SELECT [parent_name] FROM [#inserted] WHERE [temp_id] = @temp_id INSERT INTO child ( [parent_id] , [name] ) SELECT SCOPE_IDENTITY() , [child_name] FROM [dbo].[#inserted] WHERE [temp_id] = @temp_id FETCH cur INTO @temp_id END CLOSE cur DEALLOCATE cur

Answer2:

You can iterate over the inserted table by declaring a variable for each column.

DECLARE @parent_name NVARCHAR(100) DECLARE @child_name NVARCHAR(100) DECLARE cur CURSOR FOR SELECT [parent_name] , [child_name] FROM [inserted] OPEN cur FETCH cur INTO @parent_name, @child_name WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO [dbo].[parent] ( [name] ) SELECT @parent_name INSERT INTO child ( [parent_id] , [name] ) SELECT SCOPE_IDENTITY() , @child_name FETCH cur INTO @parent_name, @child_name END CLOSE cur DEALLOCATE cur

I got the idea from this answer: https://stackoverflow.com/a/15162860/426242

Recommend

  • INNER JOIN on VARCHAR
  • Sorting words according to letters of an old Semitic language
  • Error Code: 1267 Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,NUMERIC
  • cakephp model association/join with same table [closed]
  • override relationship behaviour in sqlalchemy
  • PostgreSQL: update with left outer self join ignored
  • Wiring top-level DAGs together
  • Can I use SQL to split contents of a table column stored as CSV (comma separated values) into indivi
  • Passing variable into xp_cmdshell
  • How to select all column in XML
  • Conversion failed when converting the nvarchar value 'SELECT * FROM
  • Need faster concat of columns in sql server table
  • How do you SELECT several columns with one distinct column
  • How to retrieve multiple columns from non-entity type sql query?
  • Android device acting as an accessory
  • Possible to “watch” both HAML and SASS at the same time?
  • Implicit joins and Where in Doctrine - how?
  • I18n locale disregarding fallbacks
  • converting text file into xml using php?
  • Linq Objects Group By & Sum
  • Retrieving value from sql ExecuteScalar()
  • Submit form in a displaytag pagination
  • Alternatives to the OPTIONAL fallback SPARQL pattern?
  • Jquery - Jquery Wysiwyg return html as a string
  • Delete MySQLi record without showing the id in the URL
  • Unanticipated behavior
  • Arrays break string types in Julia
  • Comma separated Values
  • KeystoneJS: Relationships in Admin UI not updating
  • WPF Applying a trigger on binding failure
  • Error creating VM instance in Google Compute Engine
  • Hits per day in Google Big Query
  • How get height of the a view with gone visibility and height defined as wrap_content in xml?
  • Trying to get generic when generic is not available
  • how does django model after text[] in postgresql [duplicate]
  • FormattedException instead of throw new Exception(string.Format(…)) in .NET
  • Java static initializers and reflection
  • Checking variable from a different class in C#
  • Sorting a 2D array using the second column C++
  • java string with new operator and a literal