问题描述
嗨伙计们，
我正在尝试创建一个触发器[TrgDisAmount]来从PRODUCT表中读取PROD_NAME和PRICE，并为每个插入的新行计算销售额表的折扣和金额SERIAL和PCODE的值。
表1： PRODUCT
+ ---------- + ---- --------- + ---------- + ------- +
| PCODE | PROD_NAME | PROD_CAT |价格|
+ ---------- + ------------- + ---------- + ----- - +
| MB-101 | IPHONE 6+ | MB | 250 |
| CH-101 |玩具| CH | 95 |
| EL-101 | DEL LAPTOP | EL | 335 |
+ ---------- + ------------- + ---------- + ----- - +
表2：折扣
+ ---------- + --------------- +
| PROD_CAT | DISCOUNT_RATE |
+ ---------- + --------------- +
| MB | 10 |
| CH | 18 |
| EL | 15 |
+ ---------- + --------------- +
表3： SALES
+ -------- + ---------- + -------- ----- + ---------- + ---------- + ------- +
| SERIAL | PCODE | PROD_NAME |价格|折扣| AMOUNT |
+ -------- + ---------- + ------------- + ----- ----- + ---------- + ------- +
| | | | | | |
+ -------- + ---------- + ------------- + ------- --- + ---------- + ------- +
注意：
1）仅输入SERIAL和PCODE的值，其余列应仅由Trigger [TrgDisAmount]输入
2）使用函数[GetDiscount]获取DISCOUNT_RATE。 />
我已经创建了这个函数，它的工作原理是^^。
这是我的尝试，但是当我运行它时说：触发器用编译错误创建
当我显示错误时我发现： PLS-00049：错误的绑定变量'OLD.PRODUCT'
hi guys ,,
i'm trying to create a Trigger [TrgDisAmount] to read PROD_NAME and PRICE from PRODUCT table and to calculate the DISCOUNT and AMOUNT of SALES Table for every new row inserted with the values of SERIAL and PCODE.
Table 1 : PRODUCT
+----------+-------------+----------+-------+
| PCODE | PROD_NAME | PROD_CAT | PRICE |
+----------+-------------+----------+-------+
| MB-101 | IPHONE 6+ | MB | 250 |
| CH-101 | TOY | CH | 95 |
| EL-101 | DEL LAPTOP | EL | 335 |
+----------+-------------+----------+-------+
Table 2 : DISCOUNT
+----------+---------------+
| PROD_CAT | DISCOUNT_RATE |
+----------+---------------+
| MB | 10 |
| CH | 18 |
| EL | 15 |
+----------+---------------+
Table 3 : SALES
+--------+----------+-------------+----------+----------+-------+
| SERIAL | PCODE | PROD_NAME | PRICE | DISCOUNT | AMOUNT|
+--------+----------+-------------+----------+----------+-------+
| | | | | | |
+--------+----------+-------------+----------+----------+-------+
Note:
1) Enter the values for SERIAL and PCODE only, the remaining columns should entered by the Trigger [TrgDisAmount] only
2) Get the DISCOUNT_RATE using Function [GetDiscount].
I have already create the function and it works ^^ .
this is my try ,, but when i run it is says : trigger created with compilation error
when i show error it i found : PLS-00049: bad bind variable 'OLD.PRODUCT'
create or replace trigger TrgDisAmount
before insert on SALES
for each row
begin
if :new.PCODE = :old.PRODUCT.PCODE then
:new.PROD_NAME := :old.PRODUCT.PROD_NAME;
:new.PRICE := :old.PRODUCT.PRICE;
:new.DISCOUNT := :old.product.PRICE / (GetDiscount( :old.PRODUCT.PROD_CAT));
:new.AMOUNT := :new.PRICE - :new.DISCOUNT;
end if;
insert into SALES columns (PROD_NAME, PRICE, DISCOUNT, AMOUNT)
values (:new.PROD_NAME, :new.PRICE, :new.DISCOUNT, :new.AMOUNT );
end;
/
SQL>插入销售值（1，'MB-101'，null，null，null，null）;
结果应该是
+ -------- + ---------- + - ----------- + ---------- + ---------- + ------- +
| SERIAL | PCODE | PROD_NAME |价格|折扣| AMOUNT |
+ -------- + ---------- + ------------- + ----- ----- + ---------- + ------- +
| 1 | MB-101 | IPHONE 6+ | 250 | 25 | 225 |
+ -------- + ---------- + ------------- + ------- --- + ---------- + ------- +
请帮帮我，谢谢
SQL> insert into sales values (1,'MB-101',null, null, null, null);
RESULT SHOULD BE LIKE
+--------+----------+-------------+----------+----------+-------+
| SERIAL | PCODE | PROD_NAME | PRICE | DISCOUNT | AMOUNT|
+--------+----------+-------------+----------+----------+-------+
| 1 | MB-101 | IPHONE 6+ | 250 | 25 | 225 |
+--------+----------+-------------+----------+----------+-------+
please help me ,, thanks
推荐答案
这篇关于如何根据使用给定值从两个不同的表中插入表中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！