T-SQL Error: The multi-part identifier could not be bound

Sometimes when I work with different databases I forget the rules that are exclusive to each one. For example, today I wrote a trigger in T-SQL (SQL Server) that would have probably worked in PL/PGSQL (PostgreSQL). When it bombed with a syntax error, I had to scratch my head. I read most of the manual page from Technet but had no real luck finding the answer. I've been trying to use my own mental knowledge-base to figure out the answers to a lot of questions lately, but this time I was under an approaching deadline so I just Googled it.

Here's the problem:

 
CREATE TRIGGER 
 dbo.tr_FOO
ON  
 dbo.FOO 
AFTER INSERT, UPDATE AS 
BEGIN

 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    -- Insert statements for trigger here
 IF( UPDATE( REF ) AND ISNUMERIC( inserted.REF ) = 1 )
 BEGIN

  UPDATE
   dbo.FOO
  SET
   REF = 'Y'
  WHERE
   ID = inserted.ID;  

 END

END
GO

Why didn't this work in T-SQL? Because inserted is a table and I'm trying to reference a column. The error message I received was The multi-part identifier "inserted.REF" could not be bound.

So it turns out that there's no way to directly reference a table in T-SQL without using a SELECT statement. Unfortunately, I write triggers so rarely that I forget this because it seems somewhat illogical and annoying to me. In PL/PGSQL that's not the case. In that language, the inserted and updated sets is a record variable that can be referenced directly, so referencing something like inserted.REF is perfectly legitimate. That's one of the really annoying "missing features" of T-SQL. So the solution is simple but long-winded.

CREATE TRIGGER 
 dbo.tr_FOO
ON  
 dbo.FOO
AFTER INSERT, UPDATE AS 
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

 -- In T-SQL, 
 DECLARE @ID char(10);
 DECLARE @REF char(6);

 SELECT
  @ID = NID,
  @REF = REF,
 FROM
  inserted;

        -- Insert statements for trigger here
 IF( UPDATE( REF ) AND ISNUMERIC( @REF ) = 1 )
 BEGIN

  UPDATE
   dbo.FOO
  SET
   REF = 'Y'
  WHERE
   ID = @ID;

 END

END
GO

On another note, SQL Server has an AFTER and INSTEAD OF triggers but not the extremely useful BEFORE triggers that are in PostgreSQL. Dear Microsoft: Fix these two annoyances and I will love you more!