现在在做一个拍卖系统,必须保证 对于同一个拍卖品,后来插进来的出价比前面的出价要高,想在数据库做一个限制。
不知道应该用Check还是Trigger来做,具体如何实现,求高手指点,数据库为SqlServer2005表结构如下gid int 对应的拍卖品的号码,Foreign Key
uid char(20) 出价用户id Foreign Key
bidprice float 出价
bidtime datetime 出价时间
不知道应该用Check还是Trigger来做,具体如何实现,求高手指点,数据库为SqlServer2005表结构如下gid int 对应的拍卖品的号码,Foreign Key
uid char(20) 出价用户id Foreign Key
bidprice float 出价
bidtime datetime 出价时间
for insert
as
if exists(
select 1
from inserted a,
(
select max(bidprice)bidprice,
gid
from tb group by gid)b
where a.gid=b.gid and a.bidprice<b.bidprice
)
rollback
请帮忙调试一下。另外我想抛出一个异常,这样应用程序可以catch到并通知用户出价无效,请问如何实现,附上数据库脚本。CREATE TABLE bidprice (
gid int,
uid char(20),
bidprice float,
bidtime datetime,
PRIMARY KEY (gid,uid,bidprice)
);INSERT INTO bidprice VALUES (1,'user02',60.0,'2010-5-10');
INSERT INTO bidprice VALUES (1,'user03',65.0,'2010-5-11');
INSERT INTO bidprice VALUES (2,'user02',100.0,'2010-5-10');
INSERT INTO bidprice VALUES (2,'user03',105.0,'2010-5-11');
INSERT INTO bidprice VALUES (3,'user02',100.0,'2010-4-28');
CREATE TABLE bidprice (
gid int,
uid char(20),
bidprice float,
bidtime datetime,
PRIMARY KEY (gid,uid,bidprice)
);
INSERT INTO bidprice VALUES (1,'user02',60.0,'2010-5-10');
INSERT INTO bidprice VALUES (1,'user03',65.0,'2010-5-11');
INSERT INTO bidprice VALUES (2,'user02',100.0,'2010-5-10');
INSERT INTO bidprice VALUES (2,'user03',105.0,'2010-5-11');
INSERT INTO bidprice VALUES (3,'user02',100.0,'2010-4-28');
go
create trigger tri_name on bidprice
for insert
as
if exists(
select 1
from inserted a,
(
select max(bidprice)bidprice,
gid
from bidprice group by gid)b
where a.gid=b.gid and a.bidprice<=b.bidprice
)
rollback
go
INSERT INTO bidprice VALUES (1,'user04',65.0,'2010-5-11')
select * from bidprice
drop table bidprice
/*
訊息 3609,層級 16,狀態 1,行 1
交易在觸發程序中結束。已中止批次。*/
create trigger tri_name on bidprice
for insert
as
declare @gid int,@bidprice float
select @gid=gid,@bidprice=bidprice from inserted
if @bidprice<(select max(bidprice)from bidprice where gid=@gid )
rollback
go
把比较运算符改为<=之后又不行了,插入理论上可以插入的数据后总是报错,费解
if object_id('bidprice') is not null drop TABLE bidprice
go
CREATE TABLE bidprice (
gid int,
uid char(20),
bidprice float,
bidtime datetime,
PRIMARY KEY (gid,uid,bidprice)
);
---------------------------------测试开始----------------
if object_id('tri') is not null drop trigger tri
go
create trigger tri on bidprice
instead of insert
as
begin
declare @new_price money,@old_price money,@gid int
select @gid=gid,@new_price=bidprice from inserted
select @old_price=isnull(max(bidprice),0) from bidprice where gid=@gid
if @new_price>=@old_price
insert into bidprice select * from inserted
else
print'次竞价太低'
endINSERT INTO bidprice VALUES (1,'user02',60.0,'2010-5-10');
INSERT INTO bidprice VALUES (1,'user03',65.0,'2010-5-11');
INSERT INTO bidprice VALUES (2,'user02',100.0,'2010-5-10');
INSERT INTO bidprice VALUES (2,'user03',105.0,'2010-5-11');
INSERT INTO bidprice VALUES (1,'user05',30.0,'2010-4-28');
go