Trigger Trouble - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Trigger Trouble

Author

Topic

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-10-28 : 12:18:39

I am Attempting to use a trigger to sequence the field entrynum.
For each order
So that I can output the record to a canned program.

CCCorderID comes from the main table (this is a sub)
And The Orderdetid is a key auto number field.

The Trigger should give me the entrynum.
example:

Cccorderid OrdDetid entrynum
S121 109 1
S121 110 2
S121 111 3
S122 112 1
S123 113 1
S123 114 2

ALTER TRIGGER StockOrderDet_Trigger1
ON dbo.StockOrderDet
FOR INSERT
AS
Declare @OrdDetID Int
Declare @OrderID Varchar
Declare @entrynum Int
Select @OrdDetID = OrdDetID , @OrderId = CCCOrderID
From Inserted

select @Entrynum = Isnull(MAX(entrynum),0) + 1
From Stockorderdet
where cccorderid = @orderid

Update dbo.Stockorderdet
Set entrynum = @entrynum
Where OrdDetID = @OrdDetID

Instead on entry I get This error.

Syntax error converting Converting the varchar value ‘*’ to a column data type Int

I am stumped What the heck have I done.

Jim
Users <> Logic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-28 : 12:22:10

What are the data types of the columns in Stockorderdet? You are receiving an asterisk and trying to put that into an int column, which you can't do even with an explicit conversion.

BTW, this will only work if inserted table has one row:

Select @OrdDetID = OrdDetID , @OrderId = CCCOrderID
From Inserted

You have to keep in mind that inserted table could have more than one row. If that is the case, you can't use variables unless you loop through the inserted table to process them. Of course looping would not be recommended though.

Tara

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-10-28 : 12:28:31

cccorderID is Varchar
OrddetId is of course Int
Entrynum is Int

This trigger is for a entry level not a table import.

I am just trying to update an extra field "entrynum"
so that the other program can recive it.

Jim
Users <> Logic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-28 : 12:33:34

If you could provide us with sample data that would generate this error, then we could come up with a solution. Your current sample data looks like it would work with this trigger. Also, please provide the sample data in the form of INSERT INTO statements. Along with the sample data, please provide DDL for your table.

You might consider troubleshooting this by putting the inserted rows into a table that only has varchar columns in it. Then have a look at your varchar table after the trigger fires. You should be able to tell which data value is giving you the problem.

Tara

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-10-28 : 12:42:54

The error generates on any entry.
and I am only entering one record at atime.
I have no clue where it is getting varchar value ‘*’

Thats what has me stumped.
This dang thing should work.

Here is the table Script.
CREATE TABLE [dbo].[StockOrderDet] (
[OrdDetID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[CCCOrderID] [int] NULL ,
[Item_Number] [int] NULL ,
[ItemQuan] [int] NULL ,
[BasePrice] [money] NULL ,
[VolPrice] [money] NULL ,
[CustPrice] [money] NULL ,
[ORPrice] [money] NULL ,
[LinePrice] [money] NULL ,
[QuanShipped] [int] NULL ,
[QuanBackordered] [int] NULL ,
[FixedPriceCode] [int] NULL ,
[EntryNum] [smallint] NULL

Jim
Users <> Logic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-28 : 12:45:10

Sample data needs to be provided using INSERT INTO statements. Once I have that, I should be able to help you out.

Tara

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-10-28 : 13:03:07

Found it !!!!!!!

cccorderid is int not varchar

That '*' error realy threw me

Thanks Tara

Jim
Users <> Logic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-28 : 13:08:21

Jim,

Will the inserted table EVER have more than one row in it? If so, your trigger isn't going to work except for the last row in the inserted table.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-28 : 13:10:26

Jim,

I'd be curious if you have another trigger on the table...

Since it could be a bunch of things, let's prove it is the trigger (which I doubt).

DROP TRIGGER StockOrderDet_Trigger1

Then run your code/Insert and see if it happens or not. If it does, you know you need to look elsewhere.

As an aside, how do the records get inserted, and why doesn't the process that does that create your derived column?

I don't believe I would do this in a trigger.

MOO

Brett

8-)

EDIT: Jim, glad you found the datatype incosistency...but are you stuck with using a trigger?

And if you do want to do what you're doing, you'd need a (Dare I use the word) CURSOR....can you even use a cursor in a trigger?

Never tried...don't think I would....

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-10-28 : 13:11:26

no the user can only enter one record at a time.

I do see what you mean though.

Thanks Again!!

Jim
Users <> Logic

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-10-28 : 13:17:28

Normaly I would not do this at all.

I prefer not to use multiple key fields in my DBs.

It is harder to have clean relationships.

That is what this dang "entrynum" field is for.

Unfortunatly I cannot change their design so I had to dummy up their field on my table so I can Output my sales transaction to their accounting package.

Jim
Users <> Logic

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-28 : 13:35:52

quote:
Originally posted by JimL

no the user can only enter one record at a time.

I do see what you mean though.

Thanks Again!!

Jim
Users <> Logic

And if that's the Case then adding

IF (SELECT COUNT(*) FROM inserted) <> 1
Return

Shouldn't be a problem...

One question though you gotta ask yourself....

Do you feel lucky? Well do you?*

* Eastwood reference

btw, go see mystic river

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources