Explicit error message, HELP please

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Explicit error message, HELP please

Author

Topic

dbserver2000
Starting Member

47 Posts

Posted - 2009-05-18 : 01:55:43

Here is the Statement

CREATE TRIGGER trgZipStateError
ON Zipstate
INSTEAD OF INSERT
AS
DECLARE @State char (2)
DECLARE @Count int
SET @State =(SELECT State FROM inserted)
SET @Count =(SELECT Count (StateID)FROM State WHERE STATEID = @State)
IF @Count = 0
--Insert the data into the error table
BEGIN
INSERT INTO ZipStateError
SELECT ZipCode, State FROM inserted
RAISERROR ('ZipstateError entry added' , 16, 10)WITH LOG
END
ELSE
--insert the date into the regular table
INSERT INTO ZipState SELECT ZipCode, State, GetDate ( ) FROM inserted

I get this error msg:
Msg 8101, Level 16, State 1, Procedure trgZipStateError, Line 18
An explicit value for the identity column in table 'ZipState' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Can not figure out what is wrong

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2009-05-18 : 02:27:02

It's telling you that you should be providing a list of columns in your insert statement:
e.g. INSERT INTO ZipState (ZipCode, State) SELECT ......

This is good practice anyway. You also need to turn IDENTITY_INSERT ON so you can insert a value into the identity column (although I don't know why a zip code would be an identity column unless you're running the database that allocates them....)

HTH,

Tim

dbserver2000
Starting Member

47 Posts

Posted - 2009-05-18 : 16:42:35

Tim,
Thank you, but I am lost.
Have no idea how to do what you are asking me to.

quote:
Originally posted by timmy

It's telling you that you should be providing a list of columns in your insert statement:
e.g. INSERT INTO ZipState (ZipCode, State) SELECT ......

This is good practice anyway. You also need to turn IDENTITY_INSERT ON so you can insert a value into the identity column (although I don't know why a zip code would be an identity column unless you're running the database that allocates them....)

HTH,

Tim

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2009-05-18 : 17:17:56

Focus on the first part of Tim's response...

What is the definition of the ZipState table? What field has the Identity property turned on?

--------------------------------------------
Brand yourself at EmeraldCityDomains.com

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-05-18 : 17:23:45

>>SET @State =(SELECT State FROM inserted)

In triggers you should never assume that only one row will be Inserted, Updated, or Deleted. If three different states is in inserted your @state variable will only hold one of them.

Why are you using a trigger anyway? This should be done with a Foreign Key constraint.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources