473,288 Members | 1,710 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,288 software developers and data experts.

Triggers 101 - Invalid Column Name

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.
Mar 6 '07 #1
2 6147
You can change your trigger like as

Expand|Select|Wrap|Line Numbers
  1.  
  2. CREATE TRIGGER trExample ON TABLE_1 
  3. FOR INSERT
  4. AS
  5.  
  6.        declare @Field_1 varchar(20) (What ever datatype)
  7.        declare @widget_id varchar(20)
  8.  
  9.        select @Field_1=Field_1 from inserted 
  10.  
  11.       if @Field_1 ='Some Value'
  12.        begin 
  13.          select @widget_id=WIDGET_ID from inserted 
  14.           exec MyDB.dbo.MyStoredProc null,null,@Widget_id,null,null,null
  15.        end
  16.  
  17.  
Hopefully it will work.
Mar 7 '07 #2
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.
Mar 8 '07 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

8
by: Shino | last post by:
Hi, Can anyone help with this error: "ORA-00904: invalid column name"? Thanks! SQL> create view PPFa as 2 SELECT L.UserID AS LecID, U.Name, U.Email, I.IntakeID, S.UserID AS StudID 3 FROM...
1
by: BUSHII | last post by:
I have little problem and I dont have any idea how to make my trigger. I have table MyTable where I have many column with almost same name and same type (Grp1,Grp2,Grp3,Grp4...Grp50 char(1))....
2
by: ecastillo | last post by:
i'm in a bit of a bind at work. if anyone could help, i'd greatly appreciate it. i have a web app connecting to a sql server using sql server authentication. let's say, for example, my...
1
by: Christoph Graf | last post by:
Hi everybody! As far as I have seen you can inherit from a table and get its columns. Is there a possibility to also inherit a tables triggers? When I simply derive a table from another I...
4
by: rama | last post by:
Hi, I am a bit troubled with the row-level triggers which PostgreSQL uses when using update table cpmmand. For instance, if the primary key column has values 1,2,3,... and i want to update the...
1
by: SVGK, Raju | last post by:
Hi, I have a table where in lot of triggers were included in that as shown below. How to view/access triggers and procedures from postgresql. I am using postgresql 7.4.1 on solaris. regds...
11
by: Anthony Paul | last post by:
Hello everyone, I am involved in a scenario where there is a huge (SQL Server 2005) production database containing tables that are updated multiple times per second. End-user reports need to be...
2
by: lakuma | last post by:
Hi, I have a table called A (say) with columns called name, place, animal and thing. I would want to write an on insert trigger on this table, which would create a table with the name of the...
9
by: mark4asp | last post by:
Why is this page invalid? Pasted into http://validator.w3.org/ I'm told that the page is invalid. Why? It flags problems with the <bodyand </htmltags but I can't see why. Is the W3C validator...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.