Sql-server – How to insert a value into table2 from a trigger on table1

sql serversql-server-2008

I have 2 tables with a column name WHO, and I'm using these 2 tables to create a view where FSwho.who = FStasks.who.

If I enter a new record into FStasks where FStasks.who = LIZ, but there's no record on my FSwho.who table, then the trigger should insert LIZ into my table.

With the code below, the error I get is

The multi-part identifier "fs_tasks.WHO" could not be bound. (I've also tried inserted.who)

I've scoured the interwebs and most of the solutions I've come across are about changing values on the same table. How do I go about inserting values to a completely different table?

ALTER TRIGGER [dbo].[FS_TASKS_TRIG]
   ON  [dbo].[FS_TASKS]
   AFTER INSERT,UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

DECLARE @Action as char(1);
    SET @Action = (CASE 
        WHEN EXISTS(SELECT * FROM INSERTED) AND EXISTS(SELECT * FROM DELETED) THEN 'U'  -- Set Action to Updated.
        WHEN EXISTS(SELECT * FROM INSERTED) THEN 'I'  -- Set Action to Insert.
        WHEN EXISTS(SELECT * FROM DELETED) THEN 'D'  -- Set Action to Deleted.
        ELSE NULL -- Skip. It may have been a "failed delete".   
    END) 

    if @Action in ('I','U') 
    begin


        IF NOT EXISTS(SELECT * FROM fs_who where who = WHO) BEGIN
            INSERT INTO FS_WHO (WHO)
            VALUES (fs_tasks.WHO)
        END

    end 

END

Best Answer

inserted is a table. So try:

INSERT INTO FS_WHO (WHO)
SELECT DISTINCT i.WHO
FROM inserted AS i
WHERE NOT EXISTS (SELECT *
    FROM FS_WHO f
    WHERE f.WHO = i.WHO);

And you don't need to consider @Action for this...