| PL/SQL Problem!!! :( [message #39926] |
Sat, 24 August 2002 03:25  |
RAID
Messages: 2
Registered: August 2002
|
Junior Member |
|
|
Hi, i was wondering if anyone out there can help me? I'm making a trigger for the demobld database that came with Oracle (i'm testing and making sure it works on this so i can move it to my one without having to rollback every five seconds).
What i want the trigger to do is give the employees a 10% commission increase when someone places an order) However, the trigger code (below) is only for general updates on the order table... The problem i have is that there's no direct reference from the order table to the employee who is getting the raise. I have to take the customer number of the customer who placed the order, then get the rep_id from that so i know the employee number.... Which allows me to update the employee who got another orders commission.
CREATE OR REPLACE TRIGGER comm
AFTER INSERT OR UPDATE ON ORD
FOR EACH ROW
DECLARE
v_TOTAL ORD.TOTAL%TYPE := 9000;
v_COMMINC EMP.COMM%TYPE := 100.00;
v_NEWCOMM EMP.COMM%TYPE;
BEGIN
v_NEWCOMM := (v_COMMINC / 100) * 10;
UPDATE EMP
SET COMM = COMM + v_NEWCOMM
WHERE EMPNO = v_TOTAL;
END;
/
If you know how to reference the other tables data (i know it will have to be stored in a var) please help me :(
RAID
|
|
|
|
| Re: PL/SQL Problem!!! :( [message #39942 is a reply to message #39926] |
Mon, 26 August 2002 20:28  |
Anupam
Messages: 62
Registered: July 2001
|
Member |
|
|
I beleive the employee who logs in the application to enter the order gets the comission. If this is this case, you can store the login of the employee in a global variable and access it in the trigger code.
Hope this solves your problem.
|
|
|
|
| Re: PL/SQL Problem!!! :( [message #39946 is a reply to message #39926] |
Tue, 27 August 2002 01:40 |
RAID
Messages: 2
Registered: August 2002
|
Junior Member |
|
|
Thanks that would work that way but i think i didn't explain very well. The employees are in a seperate table, so once a new customer order is recieved (by an admin ir phone person) the order is placed in the customer table. I need the id in that table to look up the employee and give them the commission (the actual employee wont be logged in).
Sorry, and thanks,
RAID
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
