In my project, it happened that I had to expand an existing table with several columns. Unfortunately, I cannot change or delete / recreate it, so I just decided to create another table with a 1: 1 ratio and merge them into a view using Insert / Update / Delete-Triggers.
So far, the insert and delete job works, but I have problems with Update-Trigger. The success message is even more confusing.
The view is as follows:
Create View [dbo].[JoinedGroups] as SELECT [dbo].[MaterialGroups].[GroupID] ,[Name] ,[SupGroup_ID] ,[ExtGroup_ID] FROM [dbo].[MaterialGroups] left outer join [dbo].[MaterialGroups_Extend] ON [dbo].[MaterialGroups].[GroupID]=[dbo].[MaterialGroups_Extend].[GroupID]
As said, a new additional table is not yet complete, which is the reason for the left outer join, but if you have a different idea, let me know.
Trigger:
CREATE TRIGGER [dbo].[UpdateTriggerJG] ON [dbo].[JoinedGroups] INSTEAD OF Update AS BEGIN IF EXISTS (SELECT * FROM [dbo].[MaterialGroups_Extend] E,inserted I WHERE E.[GroupID] = I.[GroupID]) BEGIN UPDATE [dbo].[MaterialGroups_Extend] SET [SupGroup_ID] = I.[SupGroup_ID] ,[ExtGroup_ID] = I.[ExtGroup_ID] FROM [Buran].[dbo].[MaterialGroups_Extend] E,inserted I WHERE E.[GroupID] = I.[GroupID] END ELSE BEGIN INSERT INTO [dbo].[MaterialGroups_Extend] ([GroupID] ,[SupGroup_ID] ,[ExtGroup_ID]) SELECT [GroupID] ,[SupGroup_ID] ,[ExtGroup_ID] FROM inserted END UPDATE [dbo].[MaterialGroups] SET [dbo].[MaterialGroups].[Name] = I.Name FROM [dbo].[MaterialGroups],inserted I WHERE [dbo].[MaterialGroups].[GroupID] = I.[GroupID]
An operator like:
UPDATE [dbo].[JoinedGroups] SET [SupGroup_ID] = 1 ,[ExtGroup_ID] = 1
Gives the following output:
(2 row(s) affected) (23 row(s) affected) (23 row(s) affected)
What's interesting: [MaterialGroups] contains 23 rows of data [MaterialGroups_Extend] contains 2 rows of data The view also has 23.
But why is nothing inserted? Am I doing something wrong or forgot something?