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

Question

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

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

enter image description here

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 ;

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.

Out of curiousity, what did you use to get that data model design image? — Jake H, Mar 01 '13 at 15:44
It is Sybase's PowerAMC, trial version. http://www.sybase.fr/products/modelingdevelopment/poweramc — FlorianC, Mar 01 '13 at 16:06

score 2 · Answer 1 · answered Mar 01 '13 at 15:41

2

OK you should never set scalar variables to a value in inserted or delted in a trigger.

Use the OUTPUT clause instead to get your id values back.

answered Mar 01 '13 at 15:41

HLGEM

94,695
15
113
186

score 1 · Answer 2 · answered Mar 01 '13 at 16:56

This trigger uses a loop over a cursor and won't require any particular uniqueness in the tables;

CREATE TRIGGER test
ON V_Enterprise
INSTEAD OF INSERT
AS 
BEGIN
   DECLARE @name    VARCHAR(32)
   DECLARE @city    VARCHAR(32)
   DECLARE @address VARCHAR(32)
   DECLARE @pn      VARCHAR(32)
   DECLARE @email   VARCHAR(32)

   DECLARE cursor1 CURSOR FOR
      SELECT name,city,address,phoneNumber,email FROM inserted;

    OPEN cursor1;
    FETCH NEXT FROM cursor1 INTO @name, @city, @address, @pn, @email;
    WHILE @@FETCH_STATUS = 0
    BEGIN
      INSERT INTO T_Contact (phoneNumber,email) VALUES (@pn, @email);
      INSERT INTO T_Enterprise (idcontact,name,city,address) VALUES
         (@@IDENTITY,@name,@city,@address);
      FETCH NEXT FROM cursor1 INTO @name, @city, @address, @pn, @email;
    END
    CLOSE cursor1;
    DEALLOCATE cursor1;
END
GO

score 1 · Answer 3 · answered Apr 02 '14 at 18:11

I don't know if this is a good way to do it, but you can do this without relying on unique columns or using a cursor using the OUTPUT clause for INSERT. This approach does make use of an in-memory temporary table that could get big with large inserts.

DECLARE @Table table( NewID BIGINT);


INSERT INTO T_Contact (PhoneNumber) 
    OUTPUT Inserted.ID
    INTO @Table
SELECT PhoneNumber FROM inserted WHERE 
;

INSERT INTO T_Enterprise (Contact_ID)
SELECT NewID FROM @Table;

score 0 · Accepted Answer · answered Mar 01 '13 at 15:42

0

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 ;

answered Mar 01 '13 at 15:42

Mike Perrenoud

66,820
29
157
232

Hmm, it should do the trick (I skipped some columns in my example. In my real database, I can put a dozen of parameters). But I expected something more... robust :-) – FlorianC Mar 01 '13 at 16:03
@FlorianC: `But I expected something more... robust`, what do you mean by that exactly? – Mike Perrenoud Mar 01 '13 at 16:07
First of all, sorry for my poor english. What I meant is : It's ok, it will work because users are not supposed to add twice the same contact. But users always act weird so I tried to think about a way to avoid failures in that particular case. I can obviously manage it "client-side" but I think there is a better way to deal with it in the database, and I really want to find it ! – FlorianC Mar 01 '13 at 16:20
@FlorianC, okay, now I understand. What you can do is add a unique constraint to that table on those two (or more if necessary) fields so that you will **know** that the records are unique. You should of course handle it client-side as well - but that's the concrete layer of safety. – Mike Perrenoud Mar 01 '13 at 17:48

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

4 Answers4

Linked