CODEWITHSUNDEEP
sql-server
Secret
Secret

Reputation: 337

MS SQL 2012 Trigger on Insert: Insert all rows from a table

I am sorry if this question is too standard but I could not figure a way to do this efficiently

I have 3 tables Customer, CustomerProject, and StandardProject

My logic is that when a new customer is inserted into Customer table, it will trigger and insert ALL projects from StandardProject into CustomerProject

Create Table [Customer]
(
   [CID] INTEGER PRIMARY KEY IDENTITY(1,1)
 , Customer   VARCHAR(50) NOT NULL UNIQUE 
)


Create Table [CustomerProject]
(
   ProjectID INTEGER PRIMARY KEY IDENTITY(1,1)
 , [CID]     INTEGER FOREIGN KEY REFERENCES [Customer](CID)
 , Project   VARCHAR(50) NOT NULL 
 , CONSTRAINT UN_Customer_Project UNIQUE([CID], Project)
)


Create Table [StandardProject]
(
    ProjectID INTEGER PRIMARY KEY IDENTITY(1,1)
 ,  Project   VARCHAR(50) NOT NULL UNIQUE
)

INSERT INTO [StandardProject](Project) VALUES('PROJECT 1')
INSERT INTO [StandardProject](Project) VALUES('PROJECT 2')
INSERT INTO [StandardProject](Project) VALUES('PROJECT 3')

Question: When I insert Customer = 'My Customer', is there be a possible trigger that can add 'Project 1', 'Project 2', 'Project 3' into CustomerProject table?

In reality, the StandardProject can have a quite a few projects and these projects can changed frequency. 

CREATE TRIGGER InsertStandardProjects ON [Customer]
FOR INSERT
AS

INSERT INTO [CustomerProject]([CID], [Project])...

Upvotes: 2

Views: 31

Answers (2)

anshev0
anshev0

Reputation: 136

Something like this.

CREATE TRIGGER InsertStandardProjects ON [Customer]
FOR INSERT
AS
INSERT INTO [CustomerProject]([CID], [Project])
SELECT i.CID, St.Project
    FROM inserted i,StandardProject St

-- Check

INSERT INTO Customer (Customer) VALUES ('MyNewCustomer')

SELECT * FROM [Customer]
SELECT * FROM [CustomerProject]

Output:

CID Customer
1   MyNewCustomer

ProjectID   CID Project
1   1   PROJECT 1
2   1   PROJECT 2
3   1   PROJECT 3

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521684

First of all, I suggest defining CustomerProject as:

CREATE TABLE [CustomerProject] (
    ProjectID INTEGER FOREIGN KEY REFERENCES [StandardProject](ProjectID),
    CID INTEGER FOREIGN KEY REFERENCES [Customer](CID),
    CONSTRAINT UN_Customer_Project UNIQUE(CID, ProjectID)
)

This junction table only exists to store relationships between customers and their projects, not the metadata associated with a customer or project. So, storing the Project name here is not appropriate, and is just a waste of space.

That being said, you may try using the following trigger:

CREATE TRIGGER InsertStandardProjects ON [Customer]
FOR INSERT
AS

INSERT INTO [CustomerProject] (ProjectID, CID)
SELECT ProjectID, INSERTED.CID
FROM StandardProject

GO

The insert query in the above trigger makes use of INSERTED, which holds the values from the row being inserted, which includes the CID of the customer.

Upvotes: 1

Related Questions