CODEWITHSUNDEEP
sql-servert-sqltriggersbulkinsert
LeonidasFett
LeonidasFett

Reputation: 3132

How to insert rows into a table based on another table's rows using Triggers?

I have three tables:

Employee

Id
FirstName
LastName
EmployeeTasksId

EmployeeTasks

Id
EmployeeId
EmployeeTaskDefinitionId

EmployeeTaskDefinitions

Id
Description
etc.

What I am trying to do is once an Employee is inserted into the Employees table, I need to insert rows into the EmployeeTasks table based on the inserted Employee's Id and every EmployeeTaskDefinition's Id.

For example:

Now I have read numerous posts about cursors, with most people saying that they're a bad practice to use for this task. But how would you do something like this? Note: I don't want to use anything else other than triggers.

EDIT: This is the query I came up with. 

CREATE TRIGGER CreateEmployee 
    ON [dbo].[EmployeeSet]
    AFTER INSERT
AS
    DECLARE @LoopCounter int, @MaxSettingDefinitionId int, @Id int
    SELECT @LoopCounter = MIN(Id), @MaxSettingDefinitionId = MAX(Id)
    FROM SettingsDefinitionSet

    WHILE(@LoopCounter IS NOT NULL AND @LoopCounter <= @MaxSettingDefinitionId)
    BEGIN
        SELECT @Id = Id FROM SettingDefinitionSet
        WHERE Id = @LoopCounter
        INSERT INTO SettingSet(CompanyId, EmployeeId, SettingDefinitionId, SettingType, State, Value)
        VALUES((SELECT CompanyId FROM inserted), (SELECT Id FROM inserted), 
                @Id, 
                (SELECT SettingType FROM SettingSet WHERE EmployeeId IS NULL AND CompanyId = (SELECT CompanyId FROM inserted) AND SettingDefinitionId = @Id), 
                (SELECT State FROM SettingSet WHERE EmployeeId IS NULL AND CompanyId = (SELECT CompanyId FROM inserted) AND SettingDefinitionId = @Id), 
                (SELECT Value FROM SettingSet WHERE EmployeeId IS NULL AND CompanyId = (SELECT CompanyId FROM inserted) AND SettingDefinitionId = @Id))
        SELECT @LoopCounter = MIN(Id) FROM SettingDefinitionSet
        WHERE Id > @LoopCounter
    END
GO

Upvotes: 1

Views: 68

Answers (2)

Alex Kudryashev
Alex Kudryashev

Reputation: 9470

As I noted in comments, Employee table is bad normalized and shouldn't have EmployeeTasksId. If you want to add automatically some tasks to a new employee do it like this:

alter table EmployeeTaskDefinitions
add DefaultTask bit not null default 0
--update EmployeeTaskDefinitions set DefaultTask = 1 where...
--now create a trigger
create trigger trEmployee_i
on dbo.Employee after insert
as
begin
set nocount on
insert EmployeeTasks(EmployeeId,EmployeeTaskDefinitionId)
select i.id,td.id
from inserted i cross join EmployeeTaskDefinitions td
where td.DefaultTask = 1
end

P.S.: I hope EmployeeTasks.Id is identity column.

Upvotes: 1

Kevin Anderson
Kevin Anderson

Reputation: 4592

This should do the trick:

INSERT INTO EmployeeTasks(EmployeeId, EmployeeTaskDefinitionId)
SELECT inserted.id as EmployeeId, t.id as EmployeeTaskDefinitionId
    FROM inserted JOIN EmployeeTaskDefinitions

Upvotes: 1

Related Questions