ORDER by desc problem in Trigger

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 2005 Forums

Transact-SQL (2005)

ORDER by desc problem in Trigger

Author

Topic

tramvai
Starting Member

7 Posts

Posted - 2009-04-25 : 07:58:32

Hello.
I have a problem i can't figure out what's going wrong.

I have a table catalog wich contains the following columns :
codp( int, the id of the product),
data(smalldatetime, wich is the date where the codp receive a price),
price(wich is the price of the product).
I have do to a trigger to stop me adding to the catalog a a new price which is the same with the last price of the product in the catalog.

I have the following code :

IF OBJECT_ID ('trigger2', 'TR')
is not null
drop trigger trigger2
Go
create trigger trigger2 on catalog
AFTER INSERT
as

declare @codp int, @data smalldatetime,@price int,@lastPrice int

select @codp=i.codp, @data=i.data, @price=i.price from inserted i

set @lastPrice=(select top 1 c.pret from catalog c where c.codp=@codp order by data desc)

print @lastPrice -- i did this print only to verify if it's wrong or not
print @price -- the same with this print
if @lastPrice=@price
begin
print 'The price is the same. can't be included'
rollback
end

The problem is that the select statement ("select top 1 c.pret from catalog c where c.codp=@codp order by data desc") is going perfectly outside the trigger.
I am sure it's not a big deal, but i can't figure out.

Give me an A ... ARSENAL

nr
SQLTeam MVY

12543 Posts

Posted - 2009-04-25 : 08:13:31

Is the insert inserting more than one row?
Are there two rows wit the same dae?
Doubt whether it would make a difference but try
select top 1 @lastPrice = c.pret from catalog c where c.codp=@codp order by data desc
Also see what it is getting
select top 1 @lastPrice = c.pret, @data = data from catalog c where c.codp=@codp order by data desc
select @data, @lastPrice
select top 10 data from catalog c where c.codp=@codp order by data desc
also try with
select @lastPrice = c.pret, @data = data from catalog c where c.codp=@codp and data = select max(c2.data) from catalog c2 where c.codp=c2.copep)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tramvai
Starting Member

7 Posts

Posted - 2009-04-25 : 08:42:04

pret = price in english i fortog to tell you that.
the problem very strange is that lastPrice get always the value of the price i want to insert.

for instance:
insert into catalog values ('1','11/25/2020','86')

i have no price 86 for product in the table but i get printet
@lastPrice = 86, which
@price = 86 , which is correct

I don't know why, cause i told you outside the trigger this select its's giving me right results .

Is the insert inserting more than one row ? -- i insert only 1 row

Give me an A ... ARSENAL

tramvai
Starting Member

7 Posts

Posted - 2009-04-25 : 09:09:01

so the problem is that all time @lastPrice receive the value of @price, which in my case is 86

Give me an A ... ARSENAL

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-25 : 09:10:26

A...what's that spell!?

Your query to get @lastPrice is including the data you just inserted (the 86 in this case). It's reading the uncommitted transaction because the query is executing from within the context of the transaction.

I think it is a bad idea to assume that the inserts will always only be 1 row at a time. A trigger should take multi-row inserts into account.

Be One with the Optimizer
TG

tramvai
Starting Member

7 Posts

Posted - 2009-04-25 : 09:24:32

thank you i understood now :D
i added the @data<>c.data condition and now it's ok

Give me an A ... ARSENAL

nr
SQLTeam MVY

12543 Posts

Posted - 2009-04-25 : 10:09:20

oops - di8dn't notice the type of trigger.
You could use an instead of trigger instead.

Even better would to do the insert via an sp and put the logic there.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources