Error 207 Invalid Column Name

[mkrausnick]

I'm confused. The following code works:

CREATE TRIGGER DEMOG_UPD ON dbo.demog 
FOR UPDATE
AS
if Update( Fmly_Size ) 
update  [dbo].[demog] 
set FamSizeDate = getdate()
FROM  [dbo].[demog] , inserted
         WHERE  [dbo].[demog] .DemogID = inserted.DemogID

However when I add an additional test on the same field, it fails syntax check with error 207 - column not found fmly_size:

CREATE TRIGGER DEMOG_UPD ON dbo.demog 
FOR UPDATE
AS
if Update( Fmly_Size ) 
BEGIN
update  [dbo].[demog] 
set FamSizeDate = getdate()
FROM  [dbo].[demog] , inserted
         WHERE  [dbo].[demog] .DemogID = inserted.DemogID

if Fmly_Size not between 1 and 20
  update [dbo].[demog] 
    set Fmly_Size = 1
      FROM  [dbo].[demog] , inserted
         WHERE  [dbo].[demog] .DemogID = inserted.DemogID  

END

Why?

Mike Krausnick
Dublin, California

 

[SQLSister]

its the if statement, use a where clasue instead.

Questions about posting. See faq183-874

 

[mkrausnick]

Umm, ok, that worked. I'm sure there's a logical explanation, but it seems like voodoo to me!

Thanks for the guidance.

Mike Krausnick
Dublin, California

 

[ESquared]

I suspect you may not be getting the results you are expecting. Check things carefully. In the meantime, take a gander at the following:

UPDATE demog
   SET Fmly_Size = 1
   FROM demog INNER JOIN inserted ON demog.DemogId = inserted.DemogID
   WHERE FmlySize NOT BETWEEN 1 AND 20

And for what it's worth you might review the FAQ, Should I use JOIN or WHERE to join my tables?

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[mkrausnick]

Great information, thanks. I also prefer to use the ANSI Sql join syntax whenever possible, but being new to Transact-SQL and triggers, I didn't initially recognize the syntax in the UPDATE statement as a join.



Mike Krausnick
Dublin, California

 

[ESquared]

Not just the join... I couldn't help converting that, but also a method for doing your IF clause thingie.

-------------------------------------
A sacrifice is harder when no one knows you've made it.