Is using a Trigger to Insert a new Record into a View seen as bad practice? So I was recently having a conversation with one of my new work colleagues and we got onto the topic of how best to INSERT data into multiple tables.

Their teaching taught them to not use a TRIGGER to split an INSERT onto a VIEW into multiple INSERTS into their respective tables, is this seen as bad practice?

Following is how I attempted to solve the INSERT issue without creation of duplicate values in the base tables.

VIEW

CREATE VIEW [Person].[vPeopleAllInformation] AS
SELECT pt.Type_Name AS Type,
p.Firstname,
p.Lastname,
e.Email AS Email,
ph.Number AS PhoneNumber
FROM [Person].[People] p
FULL JOIN [Person].[Person_Types] pt ON p.Person_Type = pt.Person_Type_ID
FULL JOIN [Person].[Person_Emails] e ON e.Person_ID = p.Person_ID
FULL JOIN [Person].[Person_Phones] ph ON ph.Person_ID = p.Person_ID


TRIGGER

CREATE TRIGGER [Person].[tPerson_Email_Phone] ON [Person].[vPeopleAllInformation]
INSTEAD OF INSERT
AS
BEGIN

INSERT INTO [Person].[People] (Person_Type, Firstname, Lastname)
SELECT p.Person_Type_ID, i.Firstname, i.Lastname FROM INSERTED i
INNER JOIN [Person].[Person_Types] p ON i.Type = p.Type_Name
WHERE NOT EXISTS (SELECT 1 FROM [Person].[People] WHERE Firstname = i.Firstname AND Lastname = i.Lastname);

INSERT INTO [Person].[Person_Emails] (Person_ID, Email)
SELECT p.Person_ID, i.Email FROM INSERTED i
INNER JOIN [Person].[People] p ON p.FullName = CONCAT(i.Lastname, ', ', i.Firstname)
WHERE NOT EXISTS (SELECT 1 FROM [Person].[Person_Emails] WHERE Email = i.Email);

INSERT INTO [Person].[Person_Phones] (Person_ID, Number)
SELECT p.Person_ID, i.PhoneNumber FROM INSERTED i
INNER JOIN [Person].[People] p ON p.FullName = CONCAT(i.Lastname, ', ', i.Firstname)
WHERE NOT EXISTS (SELECT 1 FROM [Person].[Person_Phones] WHERE Number = i.PhoneNumber);

END


Is this the right way of doing it or should I indeed split it into multiple queries when the user attempts to add another Record to the view?
Short answer


  No, don't do that.


Longer answer: 

What problem are you trying to solve? It seems like maybe it's "I would like to update multiple base tables with a single INSERT tsql command." If that's the case, then... Sure? I guess? Why not? As long as you accept that the scope of the implementation in question to do is pretty limited and clunky. You're really just obfuscating away the multiple steps you need to perform anyway. But what happens when you want to DELETE from that view? Or UPDATE from that view? Do you need other triggers to handle those cases?

At a minimum, if you're commited to trying it out for yourself and discovering the "why nots" on your own, I'd at least recommend modularizing out the separate INSERT statements in that trigger into their own procs. At least then you can reuse spInsertPerson 1, 'Bob', 'Smith' when all you really want to do is insert Bob Smith without mucking about with other side-effecting executions. 

Still... I probably wouldn't...