Triggers 101 - Invalid Column Name

[mrkinsopo]

Here's my simple Trigger to lauch a sp in SQL2000:

CREATE TRIGGER trExample ON TABLE_1
FOR INSERT
AS

if TABLE_1.FIELD_1 ='Some Value'
begin
declare @widget_id varchar(20)
select @widget_id=TABLE_1.WIDGET_ID from inserted TABLE_1
exec MyDB.dbo.MyStoredProc null,null,@Widget_id,null,null,null
end


I'm getting this error when running in QA:

"The column prefix 'TABLE_1' does not match with a table name or alias name used in the query."


TABLE_1.FIELD_1 is a valid table.column name in the DB in which I am running the Trigger. Obviously my syntax is wrong somewhere around the IF.

Any guidance appreciated.

[mabubakarpk]

You can change your trigger like as

 
CREATE TRIGGER trExample ON TABLE_1 
FOR INSERT
AS
 
       declare @Field_1 varchar(20) (What ever datatype)
       declare @widget_id varchar(20)
 
       select @Field_1=Field_1 from inserted 
 
      if @Field_1 ='Some Value'
       begin 
         select @widget_id=WIDGET_ID from inserted 
          exec MyDB.dbo.MyStoredProc null,null,@Widget_id,null,null,null
       end
 
 

Hopefully it will work.

[mrkinsopo]

Well thank you mabubakarpk , fellow newbie. That did the trick. Would you comment on WHY?

Evidently it has to do with declaring the criteria (For Table_1.Field_1) as a variable...

Again, much appreciated.