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.

我试图创建一个T-SQL触发器,它基本上将x行数插入到第三个表中,基于插入原始表的数据和第二个表中包含的数据;但是,我在insert语句的选择部分中遇到了各种错误。

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.

如果我注释掉这部分[qmgmt]。[dbo]。[skillColumns]。[columnID](从[qmgmt]中选择columnID。[dbo]。[skillColumns]),IntelliSense将删除所有红线。

Table designs:

Users table - contains user info

用户表 - 包含用户信息

skillColumns table - list of all columns possible, filtered on the productGroupID of the user

skillColumns表 - 可能的所有列的列表,在用户的productGroupID上过滤

Skills table - contains the data per user, one row for every columnID in skillColumns

技能表 - 包含每个用户的数据,skillColumns中每个columnID包含一行

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?

我想要用触发器实现什么?可以使用in关键字将多行插入到表中吗?

UPDATE:

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

使用J0e3gan提供的答案后,我能够在相反的方向创建一个触发器:

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

1 个解决方案

#1

Yes, this can be done with an AFTER trigger.

是的,这可以通过AFTER触发器完成。

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.

列列表不是您尝试使用的IN标准的正确位置,这就是为红色下划线的原因。

Try adding the IN criterion to the JOIN criteria instead:

请尝试将IN标准添加到JOIN条件:

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:

或者将其添加到WHERE子句:

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

#1