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