hey everyone, I am new to this forum and am looking for an answer to what i believe is a very simple problem. I recently starting coding in SQL and am trying to use a trigger on a table i created (code below) and my problem is that it does compile now that i am selecting the column values and assigning them to those variables, however, when i run a query to insert only city or only state, the trigger does not go off like it should. Please let me know why the trigger is not detecting that
Here is code on a posted site and not sure bout rule here but i will post short code here as well to get a nice view of whole thing:

Link to code:
----------------------------
"http://sourcepost.sytes.net/sourcepost/sourceview.aspx?source_id=30047"

Actual Code:
----------------------------
USE Auction;

CREATE TABLE zip_code (
id int ,
zip_code varchar(5) ,
city varchar(50) ,
county varchar(50) default NULL,
state_name varchar(50) ,
state_prefix varchar(2) ,
area_code varchar(3) default NULL,
time_zone varchar(50) default NULL,
lat float NOT NULL,
lon float NOT NULL,
PRIMARY KEY (id)
)

--MY ADDITION: To check if state name and/or city is missing so require zipcode

--SET ANSI_NULLS ON /* added for error checking */
GO
CREATE TRIGGER tr_Valid_Zip_Entry
ON zip_code
FOR INSERT
AS
--not quite sure why i had so many problems not declaring these and just using the column
--names as tehy were. Why do i have to declare and pull the value from the inserted table?

declare @city varchar(50)
declare @state_prefix varchar(2)
declare @state_name varchar(50)
declare @zip_code varchar(5)


SELECT @city = city from inserted zip_code
SELECT @state_prefix = state_prefix from inserted zip_code
SELECT @state_name = state_name from inserted zip_code
SELECT @zip_code = zip_code from inserted zip_code

--PRINT @city /* debug added to see if execution was even coming into trigger */

IF(@city = 'NULL' OR @state_name = 'NULL')
BEGIN
IF (@state_prefix = 'NULL' OR @zip_code = 'NULL')
BEGIN
PRINT 'You must provide zip code if city or state are missing'
END
END
GO

--remember this is being run on MS SQL Server Express
-- and i m not sure if i m notified of replies to threw so i will try to check back but please email me if you do have response/post (if you can)

Did you get that trigger to fire?

I have MSSQL Express too, and that trigger you provided will not fire for me either. Maybe some other setting or mode might be needed? I'm still investigating...

EDIT:
1. How are you running that script with the 'GO' keywords? I keep getting errors with 'GO', but I created the trigger manually using a SQL command. (the table too)

2. What tool [VC++ express, etc.] do you use with the SQL? VC++ Express has some interesting information in the help, as related to triggers. Go to the help index, and just lookup 'triggers'. There are several sub-categories with examples and how-to's.

__________________
Use the force...read the source!!
WYCIWYG -- what you code is what you get!

hey turbo
i am using MS SQL Server Management Studio Express to work with the MS SQL Server (if this is redundant it may be the prob) but as far as i have seen the GO statements are needed and they execute all the code preceding until it finds another GO (if there is any). i am trying to work with/around this trigger problem while at the same time trying to create some stored procedures that will do the same thing... lemme know what you find... and thanks for the help.

note:
I am not sure why yo uare getting errors with the GO commands. Are you sure your using MS SQL and not mySQL? (I know, dumb question but had to ask. i dont wanna have an error on your part or [probably] mine be because of something stupid). Lets keep it in mind that i am kinda new to SQL, at least MS SQL (not mySQL)

I may have set the environment up wrong but i installed the SQL Server 2005 Express (SQLEXPR.exe) and the MS SQL Management Studio 2005 Express as well. But i am pretty sure it is correct.

You can view the entire problem for this class i am taking and understand what i m trying to solve by going here (all code included is there):
"http://www2.newpaltz.edu/~plotkinm/Course/DataBaseFinal.php"

You can see the whole problem yourself there with ALL the code i am using for MS SQL (although i believe my prof only gave the mySQL code for one of the files so i just copied and pasted the whole code (is MS WOrd) and used the find and replace feature to change the syntactical errors to fit into MS SQL. (i will give you/post the file if you wish))

Yes, I'm using MS SQL, it's the SQL Server 2005 Express, mine was installed as part of VC++ 2005 Express. Just to make sure I have the same application, I attempted another install, but got an "already installed" message. I do not, however, have the "Management Studio", but not sure if that matters.

I do see where triggers are claimed to be supported, as shown here, [on page 2], but have yet to make one work.

__________________
Use the force...read the source!!
WYCIWYG -- what you code is what you get!