Home » SQL & PL/SQL » SQL & PL/SQL » Expecting trigger to be called explicitly
| Expecting trigger to be called explicitly [message #38564] |
Fri, 26 April 2002 18:04  |
Sid
Messages: 38
Registered: May 1999
|
Member |
|
|
I want to increase/decrease a sales person's commission by a trigger.
The trigger is on the order table(ord).
When a DML like update or insert is called a procedure is invoked, the trigger passes the required parameters. The parameters that are passed are cusomer_id, old order total and new order total. I have used OLD and NEW qualifers that are avaiable in ROW TRIGGERS.
The procedure "proc_update_sales_comm" finds an appropriate employee from the CUSTOMER table and updates the sales person's record (commission column is updated) in the EMP table.
Below is the procedure:
CREATE OR REPLACE PROCEDURE update_sales_comm
(cust_id customer.custid%TYPE, order_total_old ord.total%TYPE, order_total_new ORD.total%TYPE)
IS
sales_rep_id customer.repid%TYPE;
sales_comm emp.comm%TYPE;
new_com_temp1 NUMBER(7,2) := 0;
new_com_temp2 NUMBER(7,2) := 0;
new_com_final NUMBER(7,2) := 0;
BEGIN
SELECT repid
INTO sales_rep_id
FROM customer
WHERE custid = cust_id;
SELECT comm
INTO sales_comm
FROM empy
WHERE empno = sales_rep_id;
--CODE TO WORK OUT COMMISSION
new_com_temp1 := order_total_new * .05;
new_com_temp2 := order_total_old * .05;
new_com_final := new_com_temp1 - new_com_temp2;
UPDATE empy
SET comm = comm + new_com_final
WHERE empno = sales_rep_id;
END;
/
--I HAVE TESTED THIS PROCEDURE WITHOUT THE TRIGGER CODE AND IT WORKED AS I HAD HOPED. BELOW ARE TWO TESTS:
SQL> EXECUTE update_sales_comm(100, 3.4, 100);
SQL> EXECUTE update_sales_comm(100, 4.83, 2);
-- HERE IS MY TRIGGER CODE
CREATE OR REPLACE TRIGGER order_trigger
AFTER UPDATE OR INSERT OR DELETE ON ord
FOR EACH ROW
BEGIN
IF inserting THEN update_sales_comm(:old.custid, :old.total, :new.total);
ELSIF updating THEN update_sales_comm(:old.custid, :old.total, :new.total);
ELSIF deleting THEN update_sales_comm(:old.custid, :old.total, :new.total);
ELSE update_sales_comm(:old.custid, :old.total, :new.total);
END IF;
END;
/
Trigger created.
--NOW WHEN I TEST THE TRIGGER IT DOES NOT WORK. THAT IS THE IMIPLICIT CALL DOES NOT TAKE PLACE SO EMP COMMISSION IS NOT UPDATED. NOTE I WAS EXPECTING AN IMLICIT CALL TO PASS THE CUSTID, :NEW.TOTAL AND OLD.TOTAL.
SQL> UPDATE ord
2 SET total = 102.40
3 WHERE ordid = 610;
1 row updated.
--DOES NOT WORK SINCE THE EMP TABLE COMMISSION IS NOT UPDATED
SQL> SELECT * FROM EMPY WHERE EMPNO = 7844;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------- ---------- ---------- --------- --------- --------- --------- ---------
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
PLEASE HELP
|
|
|
|
|
|
| Re: Expecting trigger to be called explicitly [message #38573 is a reply to message #38564] |
Sat, 27 April 2002 04:09  |
Sid
Messages: 38
Registered: May 1999
|
Member |
|
|
Sorry I have not included the table relations.
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 can't see what I am doing wrong. As stated the procedure testing worked OK when I tested with salesperson TURNER. I put in old and new values into the UPDATE_SALES_COMM procedure. Since this is a trigger I should be able to use the OLD and NEW qualifiers that are available in ROW TRIGGERS, note the old and new values are used on the column TOTAL from ORD table????
I know the :new value and :old value looks strange perhaps but the books I have read is similar to the syntax used, however I may have the syntax wrong.
|
|
|
|
| Re: Expecting trigger to be called explicitly [message #38576 is a reply to message #38564] |
Sat, 27 April 2002 23:32 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
How about a slightly different approach to your procedure? Instead of passing in the old and new order amounts from the trigger, let's just pass in the order "adjusted" value.
Insert: order total
Update: new order total - old order total
Delete: -order_total
As Saga mentioned, you cannot refer to :old values in an insert trigger and :new values in a delete trigger. Actually, you can refer to them, but those values will always be null. That is why your calculations are not working at all when called from the trigger.
So, here is what the revised trigger and proc (which becomes _much_ smaller) would look like:
create or replace procedure update_sales_comm
(cust_id in customer.custid%type,
order_adj in ord.total%type)
is
begin
update emp
set comm = comm + (order_adj * .05)
where empno = (select repid
from customer
where custid = cust_id);
end;
/
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(:new.custid, (:new.total - :old.total));
elsif deleting then
update_sales_comm(:old.custid, -:old.total);
end if;
end;
/ |
|
|
|
| Re: Expecting trigger to be called explicitly [message #38641 is a reply to message #38564] |
Thu, 02 May 2002 16:17 |
Sid
Messages: 38
Registered: May 1999
|
Member |
|
|
I have used the procedure and Trigger as Todd Barry has coded, this is much cleaner and concise then mine.
I have needed to create two more procedures that I have packaged in a package. Here is the code.
CREATE OR REPLACE PACKAGE item_pack
IS
PROCEDURE add_item(v_ordid IN NUMBER, v_itemid IN NUMBER, v_prodid IN number,
v_actualprice IN NUMBER, v_qty IN NUMBER DEFAULT 1);
PROCEDURE upd_item(v_ordid IN NUMBER, v_itemid IN NUMBER, v_qty IN NUMBER DEFAULT 1);
END item_pack;
/
Package created.
--body of package
CREATE OR REPLACE PACKAGE BODY item_pack
IS
--add_item()
PROCEDURE add_item(v_ordid IN NUMBER, v_itemid IN NUMBER, v_prodid IN number,
v_actualprice IN NUMBER, v_qty IN NUMBER DEFAULT 1)
IS
v_add_total NUMBER(8,2) := 0;
e_invalid_ordid_prodid EXCEPTION;
PRAGMA EXCEPTION_INIT( e_invalid_ordid_prodid, -2291);
BEGIN
v_add_total := v_qty * NVL(v_actualprice, 0);
insert into ITEM (ordid, itemid, prodid, actualprice, qty, itemtot)
values(v_ordid, v_itemid, v_prodid, v_actualprice, v_qty, v_add_total);
UPDATE ord
SET total = total + v_add_total
WHERE ORDID = v_ordid;
EXCEPTION
WHEN e_invalid_ordid_prodid THEN
RAISE_APPLICATION_ERROR(-20399, 'Invalid order id and/or product id supplied.');
END add_item;
--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;
/
Package body created.
Since both procedures ADD_ITEM and UPD_ITEM affect the order table this will make a call to the trigger.
I have tested this procedure and it works. As mentioned since Order table can be joined with customer table and customer table with the EMP table, I want to be able to inform to the user that employer named "ward" commission has increased/decreased.
For example after entering this line for order 601, itemid 1 and quantity of 125 employer "Ward" commission has increased by 3%
SQL>Execute item_pack.upd_item(601,1,125);
The test was done simply by examining the original commission with the old commission.
E.g.
(old commission - new commission) /old commission * 100 = 0.03
I could have the message printed using dbms_output.put_line()
on to the screen saying something like
"Employer Ward commission has increased by 3%"
My approach so far is something like this:
create or replace procedure verify_employers_commision(ord_id ord.ordid%TYPE, old_comm
emp.comm%TYPE, new_comm emp.comm%TYPE)
IS
BEGIN
IF ord_id = 601 THEN
DBMS_OUTPUT.PUT_LINE('WARD commission has increased by' + ( (old_comm - new_comm)/
old_comm ) * 100 );
END IF;
END;
/
This will obviously be called from the trigger since they only except old and new values, but I have a feeling I am doing something wrong????
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);
verify_employers_commision(:OLD.ordid,:OLD.COMM, :NEW.COMM);
elsif updating then update_sales_comm(:old.custid, (:new.total - :old.total));
verify_employers_commision(:OLD.ordid,:OLD.COMM, :NEW.COMM);
elsif deleting then update_sales_comm(:old.custid, -:old.total);
verify_employers_commision(:OLD.ordid,:OLD.COMM, :NEW.COMM);
end if;
end;
/
|
|
|
|
| Re: Expecting trigger to be called explicitly [message #39546 is a reply to message #38564] |
Tue, 23 July 2002 00:57 |
Kapoor Pramodh
Messages: 2
Registered: July 2002
|
Junior Member |
|
|
How do we call a trigger explicity(say within a stored procedure) in DB2version 6.
Can we write a generic trigger within a stored procedure which accepts the tablename as argument, and the trigger should run on the passed table.
Thanks in advance.
Pramodh Kumar Kapoor.
|
|
|
|
Goto Forum:
Current Time: Wed Apr 24 04:06:28 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
