Invalid columns on trigger
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;开发者_如何学Python
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;
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.
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
精彩评论