CODEWITHSUNDEEP
sqlsql-servert-sqltriggerssql-insert
вʀaᴎᴅᴏƞ вєнᴎєƞ
вʀaᴎᴅᴏƞ вєнᴎєƞ

Reputation: 1678

Insert multiple rows into a table with a trigger on insert into another table

I am attempting to create a T-SQL trigger that will essentially insert x number of rows into a third table based upon the data being inserted into the original table and data contained in a second table; however, I'm getting all sorts of errors in the select portions of the insert statement.

If I comment out this portion [qmgmt].[dbo].[skillColumns].[columnID] in (select columnID from [qmgmt].[dbo].[skillColumns]), IntelliSense gets rid of all the red lines.

Table designs:

CREATE TRIGGER tr_Users_INSERT 
   ON [qmgmt].[dbo].[Users]
   AFTER INSERT
AS 
BEGIN
    INSERT into [qmgmt].[dbo].[Skills]([userID], [displayName], [columnID]) 
    Select [iTable].[userID], 
           [iTable].[displayName], 
           [cID] in (select [columnID] as [cID] from [qmgmt].[dbo].[skillColumns])
    From inserted as [iTable] inner join 
        [qmgmt].[dbo].[skillColumns] on 
        [iTable].[productGroupID] = [qmgmt].[dbo].[skillColumns].[groupID]
END
GO

Is what I'm looking to accomplish even possible with a trigger? Can multiple rows be inserted into a table with the in keyword?

UPDATE:

After using the answer provided by J0e3gan, I was able to create a trigger in the opposite direction:

CREATE TRIGGER tr_skillColumns_INSERT_Users
    ON [qmgmt].[dbo].[skillColumns]
    AFTER INSERT
AS
BEGIN
    INTO [qmgmt].[dbo].[Skills]([userID], [displayName], [columnID])
    Select [qmgmt].[dbo].[Users].[userID],
           [qmgmt].[dbo].[Users].[displayName],
           [iTable].[columnID]
    From inserted as [iTable] inner Join 
        [qmgmt].[dbo].[Users] on
        [iTable].[groupID] = [qmgmt].[dbo].[Users].[productGroupID]
    Where
        [qmgmt].[dbo].[Users].[userID] in (select [userID] from [qmgmt].[dbo].[Users])
END
GO

Upvotes: 1

Views: 1802

Answers (1)

J0e3gan
J0e3gan

Reputation: 8938

Yes, this can be done with an AFTER trigger.

The column list is not the correct place for the IN criterion that you are trying to use, which is why it is underlined in red.

Try adding the IN criterion to the JOIN criteria instead:

CREATE TRIGGER tr_Users_INSERT 
   ON [qmgmt].[dbo].[Users]
   AFTER INSERT
AS 
BEGIN
    INSERT into [qmgmt].[dbo].[Skills]([userID], [displayName], [columnID]) 
    Select [iTable].[userID], 
           [iTable].[displayName], 
           [qmgmt].[dbo].[skillColumns].[columnID]
    From inserted as [iTable] inner join 
        [qmgmt].[dbo].[skillColumns] on 
        [iTable].[productGroupID] = [qmgmt].[dbo].[skillColumns].[groupID] and
        [qmgmt].[dbo].[skillColumns].[columnID] in (select columnID from [qmgmt].[dbo].[skillColumns])
END
GO

Alternatively add it to a WHERE clause:

CREATE TRIGGER tr_Users_INSERT 
   ON [qmgmt].[dbo].[Users]
   AFTER INSERT
AS 
BEGIN
    INSERT into [qmgmt].[dbo].[Skills]([userID], [displayName], [columnID]) 
    Select [iTable].[userID], 
           [iTable].[displayName], 
           [qmgmt].[dbo].[skillColumns].[columnID]
    From inserted as [iTable] inner join 
        [qmgmt].[dbo].[skillColumns] on 
        [iTable].[productGroupID] = [qmgmt].[dbo].[skillColumns].[groupID]
    Where
        [qmgmt].[dbo].[skillColumns].[columnID] in (select columnID from [qmgmt].[dbo].[skillColumns])
END
GO

Upvotes: 3

Related Questions