建的表create table product
(
pCode number not null,
pType varchar2(20),
pName varchar2(20),
cost number(5,1),
price number(5,1),
primary key(pCode)
);
表的数据insert into product values('101', '足球类', '足球', '85.0', '110.0');
insert into product values('102', '足球类', '手套', '90.0', '122.0');
insert into product values('201', '羽毛球类', '羽毛球鞋', '28.0', '38.0');
insert into product values('202', '羽毛球类', '球拍', '200.0', '250.0');
insert into product values('301', '游泳类', '泳镜', '85.0', '102.0');
insert into product values('302', '游泳类', '泳帽', '50.0', '63.0');
insert into product values('401', '健美类', '拉力器', '40.5', '54.5');
insert into product values('402', '健美类', '十磅哑铃', '70.0', '92.0');
insert into product values('403', '健美类', '跳绳', '10.0', '20.0');下面是我建的update触发器create or replace trigger change_price
before update of price
on product
for each row
declare pragma autonomous_transaction;
begin
update product set price=cost
where price<cost;
commit;
end change_price;
/然后我update了数据update product set price='70' where pcode='101';
结果触发器没有触发price被修改为了70,我的本意是如果update之后,price<cost,就让price=cost
有谁知道为什么吗?谢谢!!
(
pCode number not null,
pType varchar2(20),
pName varchar2(20),
cost number(5,1),
price number(5,1),
primary key(pCode)
);
表的数据insert into product values('101', '足球类', '足球', '85.0', '110.0');
insert into product values('102', '足球类', '手套', '90.0', '122.0');
insert into product values('201', '羽毛球类', '羽毛球鞋', '28.0', '38.0');
insert into product values('202', '羽毛球类', '球拍', '200.0', '250.0');
insert into product values('301', '游泳类', '泳镜', '85.0', '102.0');
insert into product values('302', '游泳类', '泳帽', '50.0', '63.0');
insert into product values('401', '健美类', '拉力器', '40.5', '54.5');
insert into product values('402', '健美类', '十磅哑铃', '70.0', '92.0');
insert into product values('403', '健美类', '跳绳', '10.0', '20.0');下面是我建的update触发器create or replace trigger change_price
before update of price
on product
for each row
declare pragma autonomous_transaction;
begin
update product set price=cost
where price<cost;
commit;
end change_price;
/然后我update了数据update product set price='70' where pcode='101';
结果触发器没有触发price被修改为了70,我的本意是如果update之后,price<cost,就让price=cost
有谁知道为什么吗?谢谢!!
before update of price
on product
for each row
declare pragma autonomous_transaction;
begin
update product set price=cost
where :new.price<cost;
commit;
end change_price;
create or replace trigger change_price
before update of price on product
for each row
when (new.price < old.cost)
declare
pragma autonomous_transaction;
begin
update product set :new.price=:old.cost;
commit;
end change_price;
create or replace trigger change_price
before update of price on product
for each row
when (new.price < old.cost)
declare
begin
select cost into :new.price from product;
end change_price;
你把pragma autonomous_transaction;去掉了不行,要加上,
但是这样select出来的是多行数据,不能赋给一个变量,我在后面加了where :new.price<:old.cost;还是提示实际返回的行数超出请求的行数……
before update of price
on product
for each row
begin
if :new.price<:old.cost then
:new.price:=:new.cost;
end if;
end;
/
你开始写的触发器是 before 触发器,换句话说先执行触发器的语句,后执行你发出的update语句。由于你发出的update语句是最后执行的,所以没有出现你想要的结果。后来你的思路很好,既然是想改修改结果,直接在update生效前,将 :new.字段值 改了就ok了。