Home » Other » Client Tools » trigger, update mire
| trigger, update mire [message #38666] |
Sun, 05 May 2002 03:21  |
Sid
Messages: 38
Registered: May 1999
|
Member |
|
|
Here is my table relation for this program:
CREATE TABLE CUSTOMER (
CUSTID NUMBER (6) NOT NULL,
NAME CHAR (45),
...
REPID NUMBER (4) NOT NULL,
...
CONSTRAINT CUSTOMER_PRIMARY_KEY PRIMARY KEY (CUSTID),
CONSTRAINT CUSTID_ZERO CHECK (CUSTID > 0));
CUSTID NAME ... REPID
--------- -------------- ... ---------
100 JOCKSPORTS ... 7521
101 TKB SPORT SHOP ... 7654
102 VOLLYRITE ... 7521
... ... ...
CREATE TABLE ORD (
ORDID NUMBER (4) NOT NULL,
ORDERDATE DATE,
COMMPLAN CHAR (1),
CUSTID NUMBER (6) NOT NULL,
SHIPDATE DATE,
TOTAL NUMBER (8,2) CONSTRAINT TOTAL_ZERO CHECK (TOTAL >= 0),
CONSTRAINT ORD_FOREIGN_KEY FOREIGN KEY (CUSTID) REFERENCES CUSTOMER (CUSTID),
CONSTRAINT ORD_PRIMARY_KEY PRIMARY KEY (ORDID));
ORDID ... CUSTID TOTAL
--------- ... --------- -------
610 ... 101 101.4
611 ... 102 45
612 ... 104 5860
601 ... 106 2.4
--NOTE EMP TABLE HAS NO FOREIGN KEY RELATION TO THE CUSTOMER TABLE.
--HOWEVER EMPLOYERS WHO ARE SALESPERSON ARE SHOWN IN THE CUSTOMER TABLE AS REPID.
CREATE TABLE EMP (
EMPNO NUMBER(4) NOT NULL,
ENAME CHAR(10),
...
...
DEPTNO NUMBER(2) NOT NULL,
CONSTRAINT EMP_FOREIGN_KEY FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO),
CONSTRAINT EMP_PRIMARY_KEY PRIMARY KEY (EMPNO));
EMPNO ENAME JOB COMM DEPTNO
--------- ---------- ---------- --------- ---------
7369 SMITH CLERK 20
7499 ALLEN SALESPERSON 300 30
7521 WARD SALESPERSON 500 30
7566 JONES MANAGER 20
7654 MARTIN SALESPERSON 1400 30
... ... ...
7844 TURNER SALESPERSON 0 30
I have a package called ITEM_PACK – NOTE only upd_item() function being called:
CREATE OR REPLACE PACKAGE item_pack
IS
PROCEDURE add_item() -- NOT REQUIRED FOR MY CURRENT PROBLEM
PROCEDURE upd_item(v_ordid IN NUMBER, v_itemid IN NUMBER, v_qty IN NUMBER DEFAULT 1);
END item_pack;
/
CREATE OR REPLACE PACKAGE BODY item_pack
IS
--add_item()
PROCEDURE add_item() – CAN IGNORE HERE.
--upd_item()
PROCEDURE upd_item(v_ordid IN NUMBER, v_itemid IN NUMBER, v_qty IN NUMBER DEFAULT 1)
IS
v_item_total NUMBER;
v_add_total NUMBER;
v_curr_qty NUMBER;
BEGIN
SELECT item.itemtot + v_qty * NVL(item.actualprice, 0), v_qty * NVL(item.actualprice, 0),
item.qty + v_qty
INTO v_item_total, v_add_total, v_curr_qty FROM item
WHERE ordid = v_ordid
AND itemid = v_itemid;
IF (v_curr_qty <= 0 ) THEN
RAISE_APPLICATION_ERROR(-20250, 'Unable to set qty to ' || v_curr_qty);
END IF;
UPDATE item
SET item.qty = v_curr_qty,
item.itemtot = v_item_total
WHERE item.ordid = v_ordid
AND ITEM.ITEMID = v_itemid;
UPDATE ord
SET total = total + v_add_total
WHERE ordid = v_ordid;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20251, 'Invalid Order ID/ Item ID');
END upd_item;
END item_pack;
/
SQL> EXECUTE ITEM_PACK.UPD_ITEM(601,1,125);
begin ITEM_PACK.UPD_ITEM(601,1,125); end;
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 2
ORA-04088: error during execution of trigger 'SCOTT.VERIFY_WARDS_COMM'
ORA-06512: at "SCOTT.UPDATE_SALES_COMM", line 5
ORA-06512: at line 3
ORA-04088: error during execution of trigger 'SCOTT.ORDER_TRIGGER'
ORA-06512: at "SCOTT.ITEM_PACK", line 41
ORA-06512: at line 1
|
|
|
|
| Re: trigger, update mire [message #38667 is a reply to message #38666] |
Sun, 05 May 2002 03:34  |
Sid
Messages: 38
Registered: May 1999
|
Member |
|
|
Sorry need more information. Here is the trigger on ord table. This trigger has been thoroughly tested and works.
create or replace trigger order_trigger
after update or insert or delete on ord
for each row
begin
if inserting then update_sales_comm(:new.custid, :new.total);
elsif updating then update_sales_comm(:old.custid, (:new.total - :old.total));
elsif deleting then update_sales_comm(:old.custid, -:old.total);
end if;
end;
/
create or replace procedure update_sales_comm
(cust_id in customer.custid%type, order_adj in ord.total%type)
is
begin
update empy
set comm = comm + (order_adj * .05)
where empno = (select repid
from customer
where custid = cust_id);
end;
/
I have created a new trigger:
SET SERVEROUTPUT ON
CREATE OR REPLACE TRIGGER verify_wards_comm
AFTER UPDATE OF comm ON empy
FOR EACH ROW
WHEN (old.ename = 'WARD' )
BEGIN
DBMS_OUTPUT.PUT_LINE('WARD commission has increased by' + ( (:old.comm - :new.comm)/ :old.comm ) * 100 );
END;
/
This trigger will verify that after the execution of the ITEM_PACK.UPD_ITEM() function call, WARD’s commission has increased.
Note that also that package ITEM_PACK given in the first message of this forum has been thoroug
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 04:46:29 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
