0

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?

closed as primarily opinion-based by Mr.Brownstone, Erik Darling, Philᵀᴹ, John aka hot2use, mustaccio Dec 15 '17 at 14:10

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

  • I would always use separate queries. The biggest problem with triggers is that they are silently changing things in the background. Occasionally that's what you need but mostly it just means that future work on the database causes a lot of unforseen/hidden consequences. Triggers should be used sparingly. – indiri Dec 15 '17 at 7:10
0

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...

  • 1
    I see what you mean with the modularizing of the queries, something similar to when i did general Game Development where I would make modular functions. But I see more where you start the update and delete comment "What happens if I only need to update the email of the person" or "Oh I suddenly want to delete that Phone Number" as well as if i have multiple different emails and phones per person which would it effect. Thanks for the feedback. – Daine Dec 15 '17 at 8:01

Not the answer you're looking for? Browse other questions tagged or ask your own question.