插入视图，代替触发器，身份，多个表？ [英] INSERT INTO View, INSTEAD OF Trigger, Identity, multiple tables?

问题描述

这里是我的表格（Entire = Integer //Caractère长变量= Varchar）：

Here as my tables (Entier = Integer // Caractère long variable = Varchar) :

http://i.stack.imgur.com/lNjyy.jpg

我创建了一个视图V_Enterprise（idContact， phoneNumber，电子邮件，姓名，城市，地址）

I created a view V_Enterprise(idContact, phoneNumber, email, name, city, adress)

我试图在该视图上创建触发器，以允许用户更新视图：

I tried to create a Trigger on that View to allow users to update the view :

CREATE TRIGGER test
ON V_Entreprise
INSTEAD OF INSERT
AS 
DECLARE @T_ContactId INT
BEGIN
    INSERT INTO T_Contact 
    SELECT i.phoneNumber, i.email
    FROM Inserted i 

    SELECT @T_ContactId = @@IDENTITY

    INSERT INTO T_Entreprise
    SELECT @T_ContactId, i.name, i.city, i.adress
    FROM Inserted i 
END ;

如我所料，它可以在简单的插入中工作，但是当我一次添加几行时，它失败，因为@T_ContactId仅包含第一个ID。有人可以帮我解决吗？我觉得我应该使用INNER JOIN插入内容，但我不知道如何处理它。

As I expected, it work on simple inserts, but when I add couples of rows at once, it fails because @T_ContactId only contains the first id. Can someone help me to fix it ? I feel like I should use INNER JOIN inserts but I can't figure out how to deal with it.

推荐答案

如果 phoneNumber 和 email 是 T_Contact 中的唯一键，那么您可以

If phoneNumber and email are a unique key in T_Contact then you could do this:

CREATE TRIGGER test
ON V_Entreprise
INSTEAD OF INSERT
AS 
DECLARE @T_ContactId INT
BEGIN
    INSERT INTO T_Contact 
    SELECT i.phoneNumber, i.email
    FROM Inserted i 

    SELECT @T_ContactId = @@IDENTITY

    INSERT INTO T_Entreprise
    SELECT
        (SELECT idContact FROM T_Contact
            WHERE phoneNumber = i.phoneNumber AND email = i.email),
        i.name, i.city, i.adress
    FROM Inserted i 
END ;

这篇关于插入视图，代替触发器，身份，多个表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！