1

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

1 Answer 1

5

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...

2
  • that did the trick, is there any reason why inserted.who as values didnt work when i tried that? is it the way my code was setup?
    – lizzy81
    Commented Mar 26, 2016 at 23:06
  • Because you weren't referencing inserted in your FROM clause.
    – Rob Farley
    Commented Mar 26, 2016 at 23:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.