Tigger Problem - 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

General SQL Server Forums

New to SQL Server Programming

Tigger Problem

Author

Topic

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-09-23 : 07:20:59

I have created a FOR INSERT trigger that will update a column row and trigger. see below

create table priceing(
invNo int identity (1,1) not null,
gprice numeric (28, 4) null,
Euro_GPRICE numeric (28, 4) null)

Trigger is as below

create Trigger EuroUpdate
on dbo.priceing
for insert

as

declare @NewInv int

set @NewInv = Max(invNo)

begin
update priceing
set Euro_GPRICE = (gprice * 1.25)

where invNo = @NewInv
end

but i receive the following when executing for creation

Msg 207, Level 16, State 1, Procedure EuroUpdate, Line 9
Invalid column name 'invNo'.

invNo is correct as a column name, i dont understand.

MCTS / MCITP certified

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-09-23 : 07:27:24

You can't insert a value into an identity column in this way. You can only do it using identity_insert, but I am guess you have more fundemental issues. If you really want to update the invNo in a trigger, why have you set it to an identity in the first place?

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-09-23 : 08:59:33

i have dropped and recreated the table without identity.

and i try to create the trigger again (see below)

create Trigger EuroUpdate
on dbo.priceing
for insert

as

declare @NewInv int

set @NewInv = Max(invNo)

begin
update priceing
set Euro_GPRICE = (gprice * 1.25)

where invNo = @NewInv
end

i still recieve the following error
Msg 207, Level 16, State 1, Procedure EuroUpdate, Line 9
Invalid column name 'invNo'.

i dont understand why it is doing this. THe column name is correct.

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-09-23 : 09:10:12

It ok people. worked it out.

Thank you for your help everyone.

everyone is so helpful on here. I love this site

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-23 : 12:21:11

quote:
Originally posted by masterdineen

It ok people. worked it out.

Thank you for your help everyone.

everyone is so helpful on here. I love this site

can you post finally worked code for others reference?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-09-24 : 04:17:14

Well, for everyone else, I am guessing it is changing:


set @NewInv = Max(invNo)


set @NewInv = SELECT Max(invNo) FROM priceing

Kristen
Test

22859 Posts

Posted - 2010-09-24 : 04:56:09

Note that your trigger will fire once - even if multiple rows are updated. Therefore your code

set @NewInv = SELECT Max(invNo) FROM priceing

is not safe.

You could probably do:


create Trigger EuroUpdate
    on dbo.priceing
for insert
as
update U
set Euro_GPRICE = (U.gprice * 1.25)
FROM dbo.priceing AS U
    JOIN inserted AS I
        ON I.invNo = U.invNo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources