如何根据使用给定值从两个不同的表中插入表中？ [英] How Can I Insert Into Table From Two Different Table Based On Use Given Value?
问题描述
嗨伙计们，
我正在尝试创建一个触发器[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
推荐答案
据我所知，你不能引用像（：old.Product）那样的产品，因为old指的是更新中更新的值，删除触发器。
而不是声明变量并使用选择查询
DECLARE
V_CODE = VARCHAR2;
V_NAME VARCHAR2;
V_PRICE NUMERIC;
V_DISCOUNT NUMERIC;
SELECT P.PCODE ，P.PROD_NAME，P.PRICE，P.PRICE / NVL（D.DISCOUNT_RATE，1）折扣到V_CODE，V_NAME，V_PRICE，V_DISCOUNT来自产品P，折扣D其中P.PROD_CAT = PROD_CAT（+）AND P.PCODE = ：new.PCODE
插入SALES栏目（SERIAL，PCODE，PROD_NAME，PRICE，DISCOUNT，AMOUNT）
值（：new.SERIAL，：new.PCODE，V_NAME，V_PRICE，V_DISCOUNT，V_PRICE-V_DISCOUNT）;
As I understand you can not refer to product like (:old.Product) since old refers to the values being updated in the update, delete trigger.
Instead of this declare variables and use select query
DECLARE
V_CODE = VARCHAR2;
V_NAME VARCHAR2;
V_PRICE NUMERIC;
V_DISCOUNT NUMERIC;
SELECT P.PCODE, P.PROD_NAME, P.PRICE, P.PRICE/ NVL(D.DISCOUNT_RATE,1) DISCOUNT INTO V_CODE,V_NAME,V_PRICE,V_DISCOUNT FROM PRODUCT P, DISCOUNT D where P.PROD_CAT = PROD_CAT (+) AND P.PCODE = :new.PCODE
insert into SALES columns (SERIAL, PCODE, PROD_NAME, PRICE, DISCOUNT, AMOUNT)
values (:new.SERIAL, :new.PCODE,V_NAME , V_PRICE, V_DISCOUNT, V_PRICE-V_DISCOUNT );
这篇关于如何根据使用给定值从两个不同的表中插入表中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
