Invalid columns on trigger

Question

I get the error invalid column media.user and tuid. Before running this i see that media.user does exist and i would think that the coalesce() as tuid would solve the tuid problem.

Why are these columns invalid?

CREATE TRIGGER  media_comment_trig_0  ON  media_comment   AFTER INSERT  AS  
INSERT INTO user_incoming_media_comments(recipient, comment) 
        SELECT coalesce(p.author, [media.user]) as tuid, INSERTED.id 
        FROM media
        JOIN INSERTED ON media.id = INSERTED.media
        LEFT JOIN media_comment p on p.id=INSERTED.parent
        WHERE tuid <> INSERTED.author; 

Answer 1

USER is a reserved SQL keyword, so you'll need to write it as [media].[user] instead.

Also, you can't use an alias in the WHERE clause. You'll have to put the full expression back in there:

 WHERE coalesce(p.author, [media].[user]) <> INSERTED.author; 

Answer 2

Does the column exist in the table? COALESCE will substitute a NULL value not a missing column.

Also this [media.user] should be media.[user]

you also need to repeat it in your WHERE clause

Answer 3

change [media.user] to [media].[user], the [ and ] cause SQL Server to think [media.user] is the column name, and not the table.column.

Also, you need to change WHERE tuid <> INSERTED.author; to WHERE coalesce(p.author, [media].[user]) <> INSERTED.author;. You must repeat the COALESCE and can't use the column alias in the WHERE.