create or replace trigger sales_products
after insert on sales
begin
update products set products.quantity=products.quantity-sales.quantity
where sales.pid=products.pid;
end;
这条语句应该如何修改?使得当有数据插入sales表的时候,自动修改products表中的quantity值SQL> desc sales
名称 是否为空? 类型
----------------------------------------- -------- ---------------
SID NOT NULL NUMBER(4)
PID VARCHAR2(4)
QUANTITY NUMBER(4)
SDATE DATESQL> desc products;
名称 是否为空? 类型
----------------------------------------- -------- --------------
PID NOT NULL VARCHAR2(5)
PNAME VARCHAR2(20)
PTYPE VARCHAR2(10)
PLACE VARCHAR2(30)
PDATE DATE
VALIDITY NUMBER(4)
INPRICE NUMBER(7,2)
SALPRICE NUMBER(7,2)
BARGAINPRICE NUMBER(7,2)
QUANTITY NUMBER(7)
CID VARCHAR2(5)
after insert on sales
begin
update products set products.quantity=products.quantity-sales.quantity
where sales.pid=products.pid;
end;
这条语句应该如何修改?使得当有数据插入sales表的时候,自动修改products表中的quantity值SQL> desc sales
名称 是否为空? 类型
----------------------------------------- -------- ---------------
SID NOT NULL NUMBER(4)
PID VARCHAR2(4)
QUANTITY NUMBER(4)
SDATE DATESQL> desc products;
名称 是否为空? 类型
----------------------------------------- -------- --------------
PID NOT NULL VARCHAR2(5)
PNAME VARCHAR2(20)
PTYPE VARCHAR2(10)
PLACE VARCHAR2(30)
PDATE DATE
VALIDITY NUMBER(4)
INPRICE NUMBER(7,2)
SALPRICE NUMBER(7,2)
BARGAINPRICE NUMBER(7,2)
QUANTITY NUMBER(7)
CID VARCHAR2(5)
create or replace trigger sales_products
after insert on sales
begin
update products set quantity=quantity-:new.quantity where pid=:new.pid;
end;
2 after insert on sales
3 begin
4 update products set quantity=quantity-:new.quantity where pid=:new.pid;
5* end;
SQL> /
create or replace trigger sales_products
*
第 1 行出现错误:
ORA-04082: NEW 或 OLD 引用不允许在表级触发器中
create or replace trigger sales_products
before insert on sales
for each row --少了这个
begin
update products set quantity=quantity-:new.quantity where pid=:new.pid;
end;