更新/插入视图wia触发器(Update/Insert into View wia Trigger)

它出现在我的项目中，我不得不扩展一个现有的表，其中包含更多列。不幸的是我不能改变或删除/重新创建它，所以我只选择创建另一个具有1：1-Relationship的表，并在具有插入/更新/删除触发器的视图中合并它们。

到目前为止，插入和删除工作，但我有更新触发器的问题。成功的信息更令人困惑。

View看起来像这样：

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]

如上所述，新的附加表尚未填充，这就是左外连接的原因，但如果你有不同的想法，请告诉我。

触发：

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]

声明如下：

UPDATE [dbo].[JoinedGroups] SET [SupGroup_ID] = 1 ,[ExtGroup_ID] = 1

给出以下输出：

(2 row(s) affected) (23 row(s) affected) (23 row(s) affected)

有点有趣：[MaterialGroups]包含23行数据[MaterialGroups_Extend]包含2行数据视图也有23行。

但为什么没有插入任何东西？我做错了什么或忘记了什么？

It occurred in my project that I had to extend an existing table with a few more columns. Unfortunately I can't alter or drop/recreate it, so I just chose to create another table with a 1:1-Relationship and merge them in a view with Insert/Update/Delete-Triggers.

So far, insert and Delete works, but I have my issues with the Update-Trigger. The success-message is even more confusing.

The View looks like this:

As said the new additional Table isn't filled yet, that's the reason for the left outer join, but if you got a different idea, let me know it.

The Trigger:

A statement 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)

Which is kinda interesting: [MaterialGroups] contains 23 rows of data [MaterialGroups_Extend] contains 2 rows of data The view has 23 as well.

But why isn't anything inserted? Am I doing something wrong or have I forgotten something?

最满意答案

尝试重写你的触发器，如：

CREATE TRIGGER [dbo].[UpdateTriggerJG] ON [dbo].[JoinedGroups] INSTEAD OF Update AS begin update dbo.MaterialGroups_Extend set SupGroup_ID = I.SupGroup_ID, ExtGroup_ID = I.ExtGroup_ID from dbo.MaterialGroups_Extend as E inner join inserted I on I.GroupID = E.GroupID insert into dbo.MaterialGroups_Extend ( GroupID, SupGroup_ID, ExtGroup_ID ) select GroupID, SupGroup_ID, ExtGroup_ID, from inserted as i where not exists ( select * from dbo.MaterialGroups_Extend as E where E.GroupID = I.GroupID ) update dbo.MaterialGroups set Name = I.Name from dbo.MaterialGroups as MG inner join inserted I on I.GroupID = MG.GroupID end

try to rewrite your trigger like:

更多推荐