有两个表
product: productID productName number price confirmTime expriationDate res
和
consumrecord: consumID clientID productID number price knockoff total buyDate res
创建如下触发器:
create trigger tbuy
before insert on consumrecord
declare @num int
select @num=number from product A,new B where A.productID=new.productID
if(@num>0)
begin
print 'num 大于零'
update product set number=number-1
from product A inner JOIN NEW B
where A.productID=B.productID
end
select number from product A,new B where A.productID=B.productID
if(@num<=0)
begin
rollback transaction
print '无库存了,不能购买!'
end;
在执行如下:
错误码: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'declare @num int
select @num=number from product A,new B where A.productID=new.p' at line 3Execution Time : 00:00:00:000
Transfer Time : 00:00:00:000
Total Time : 00:00:00:000这是怎么回事呀,各位大侠帮小弟解决下。
product: productID productName number price confirmTime expriationDate res
和
consumrecord: consumID clientID productID number price knockoff total buyDate res
创建如下触发器:
create trigger tbuy
before insert on consumrecord
declare @num int
select @num=number from product A,new B where A.productID=new.productID
if(@num>0)
begin
print 'num 大于零'
update product set number=number-1
from product A inner JOIN NEW B
where A.productID=B.productID
end
select number from product A,new B where A.productID=B.productID
if(@num<=0)
begin
rollback transaction
print '无库存了,不能购买!'
end;
在执行如下:
错误码: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'declare @num int
select @num=number from product A,new B where A.productID=new.p' at line 3Execution Time : 00:00:00:000
Transfer Time : 00:00:00:000
Total Time : 00:00:00:000这是怎么回事呀,各位大侠帮小弟解决下。
create trigger tbuy
before insert on consumrecord
declare num int
select num from product A, new B where A.productID=B.productID
if(num>0)
begin
print 'num 大于零'
update product set number=number-1
from product A inner JOIN NEW B
where A.productID=B.productID
end
select num=number from product A,new B where A.productID=B.productID
if(num<=0)
begin
rollback transaction
print '无库存了,不能购买!'
end;结果如下:
错误码: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'declare num int
select num from product A, new B where A.productID=B.productID
' at line 3Execution Time : 00:00:00:000
Transfer Time : 00:00:00:000
Total Time : 00:00:00:000
ON tbl_name FOR EACH ROW
trigger_stmt
create trigger tbuy
before insert on consumrecord
declare num int
select number into num from product A, new B where A.productID=B.productID
if(num>0)
begin
print 'num 大于零'
update product set number=number-1
from product A inner JOIN NEW B
where A.productID=B.productID
end
select num=number from product A,new B where A.productID=B.productID
if(num<=0)
begin
rollback transaction
print '无库存了,不能购买!'
end;错误码: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'declare num int
select number into num from product A, new B where A.productID=B' at line 3Execution Time : 00:00:00:000
Transfer Time : 00:00:00:000
Total Time : 00:00:00:000
for each row
begin
select number from product A, new B where A.productID=B.productID
if(number>0)
begin
print 'num 大于零'
update product set number=number-1
from product A inner JOIN NEW B
where A.productID=B.productID
end
select number from product A,new B where A.productID=B.productID
if(number<=0)
begin
rollback transaction
print '无库存了,不能购买!'
end
end;
错误码: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'if(number>0)
begin
print 'num 大于零'
update product set number=number' at line 5Execution Time : 00:00:00:000
Transfer Time : 00:00:00:000
Total Time : 00:00:00:000