"Steven Markey" <s.markey_at_worldnet.att.net> wrote in message
news:Ost4b.121314$0v4.8730853_at_bgtnsc04-news.ops.worldnet.att.net...
> I have 2 tables:
>
> desc employee;
> Name Null? Type
> ----------------------------------------------------- -------- -----------
--
> -----------------------
> FNAME NOT NULL
VARCHAR2(15)
> MINIT NOT NULL CHAR(1)
> LNAME NOT NULL
VARCHAR2(15)
> SSN NOT NULL CHAR(9)
> BDATE DATE
> ADDRESS NOT NULL
VARCHAR2(30)
> SEX CHAR(1)
> SALARY NUMBER(5)
> SUPERSSN CHAR(9)
> DNO NUMBER(1)
>
> dept_stat described:
>
> desc dept_stat;
> Name Null? Type
> ----------------------------------------------------- -------- -----------
--
> -----------------------
> DNAME
VARCHAR2(15)
> TOTAL_EMPS NUMBER
> TOTAL_SAL NUMBER
>
> With a trigger that calculates the total employees and total salary per
> department (displaying the dept name) each time an action (specifically an
> insert) happens on the employee table and inserts that data in the
dept_stat
> table. Here's the code:
>
> CREATE OR REPLACE TRIGGER Update_Dept_State_AIUDR
> AFTER INSERT OR UPDATE OR DELETE ON Employee
> FOR EACH ROW
>
> /*Trigger captures the current count of employees and the total salary for
> each department after a change
> is made to the employees table, and stores this data in the Dept_Stat
> table.*/
>
>
> DECLARE
>
> --Declaring variables to hold data cursor
> v_TotalEmps Dept_Stat.Total_Emps%TYPE;
> v_TotalSal Dept_Stat.Total_Sal%TYPE;
> v_Dname Dept_Stat.Dname%TYPE;
>
> --Declaring types for holding cursor data and inserts
> TYPE t_TotalEmps IS TABLE of Dept_Stat.Total_Emps%TYPE INDEX BY
> BINARY_INTEGER;
>
> TYPE t_TotalSal IS TABLE of Dept_Stat.Total_Sal%TYPE INDEX BY
> BINARY_INTEGER;
>
> TYPE t_Dname IS TABLE of Dept_Stat.Dname%TYPE INDEX BY BINARY_INTEGER;
>
> --Declaring variables for types
> v_type_TotalEmps t_TotalEmps;
> v_type_TotalSal t_TotalSal;
> v_type_Dname t_Dname;
>
> --Declaring cursor to gather aggregate data
> CURSOR c_AggregateData IS
> SELECT dname, COUNT(ssn), SUM(salary)
> FROM employee, department
> WHERE employee.dno = department.dnumber
> GROUP BY dname;
>
> --Declaring variables for indices
> v_CursorIndex BINARY_INTEGER;
> v_InsertIndex BINARY_INTEGER;
> v_LoopIndex BINARY_INTEGER;
>
> BEGIN
>
> --Initializing cursor index
> v_CursorIndex := 1;
>
> --open the cursor
> OPEN c_AggregateData;
>
> --Initial Loop to populate variables
> LOOP
> FETCH c_AggregateData INTO v_Dname, v_TotalEmps,
v_TotalSal;
>
> EXIT WHEN c_AggregateData%NOTFOUND;
>
> --Initializing elements...walking through elements via loop
> above
> v_type_Dname(v_CursorIndex) := v_Dname;
> v_type_TotalEmps(v_CursorIndex) := v_TotalEmps;
> v_type_TotalSal(v_CursorIndex) := v_TotalSal;
>
> v_CursorIndex := v_CursorIndex + 1;
>
> END LOOP;
>
> --Grabbing upper limit for loop
> v_LoopIndex := v_type_Dname.COUNT;
>
> --Initializing index for element position for looping
> v_InsertIndex := 1;
>
> --Looping through inserts 1 record at a time by using the table
> types and indexes
> FOR FREE_VARIABLE IN 1..v_LoopIndex LOOP
>
> INSERT INTO Dept_Stat
> VALUES (v_type_Dname(v_InsertIndex),
> v_type_TotalEmps(v_InsertIndex), v_type_TotalSal(v_InsertIndex));
>
> v_InsertIndex := v_InsertIndex + 1;
>
> END LOOP;
>
> END Update_Dept_StateAIUDR;
>
> I receive the following error when inserting records into the table via a
> script (that uses a WORKING procedure). The script inserts approx. 10
> records. The error:
>
> "table SCM24.EMPLOYEE is mutating, trigger/function may not see ..."
>
> So, after reading some posts I created a package and 2 triggers (below)
that
> will do the following:
>
> 1. Package, hold a table full of data values tp access throughout the
> triggers
> 2. Trigger, before an insert is done on Employee I am querying the
aggregate
> values of the total employees per dept & total salary per dept and storing
> them in package tables.
> 3. Trigger, after insert on Employee find right dept and add to existing
> table these values. Then loop through to populate dept_stat table.
>
> 1st, I created this package to hold the variables for the 2 triggers I
use:
>
> CREATE OR REPLACE PACKAGE dept_stat_pack AS
>
>
> --Declaring types for holding cursor data and inserts
> TYPE t_TotalEmps IS TABLE of Dept_Stat.Total_Emps%TYPE INDEX BY
> BINARY_INTEGER;
>
> TYPE t_TotalSal IS TABLE of Dept_Stat.Total_Sal%TYPE INDEX BY
> BINARY_INTEGER;
>
> TYPE t_Dname IS TABLE of Dept_Stat.Dname%TYPE INDEX BY BINARY_INTEGER;
>
> --Declaring variables for types
> v_type_TotalEmps t_TotalEmps;
> v_type_TotalSal t_TotalSal;
> v_type_Dname t_Dname;
>
> --Declaring variable for index
> v_CursorIndex BINARY_INTEGER;
>
>
> END dept_stat_pack;
>
> 2nd, I created this trigger to query and aggregate the employee table
BEFORE
> the action is taken on the table:
>
> CREATE OR REPLACE TRIGGER employee_insert_BUIDR
> BEFORE INSERT OR UPDATE OR DELETE ON Employee
> FOR EACH ROW
>
> DECLARE
>
> --Declaring variables to hold data cursor
> v_TotalEmps Dept_Stat.Total_Emps%TYPE;
> v_TotalSal Dept_Stat.Total_Sal%TYPE;
> v_Dname Dept_Stat.Dname%TYPE;
>
> --Declaring cursor to gather aggregate data
> CURSOR c_AggregateData IS
> SELECT dname, COUNT(ssn), SUM(salary)
> FROM employee, department
> WHERE employee.dno = department.dnumber
> GROUP BY dname;
>
> BEGIN
>
> --Initializing cursor index
> dept_stat_pack.v_CursorIndex := 1;
>
> --open the cursor
> OPEN c_AggregateData;
>
> --Initial Loop to populate variables
> LOOP
> FETCH c_AggregateData INTO v_Dname, v_TotalEmps,
v_TotalSal;
>
> EXIT WHEN c_AggregateData%NOTFOUND;
>
> --Initializing elements...walking through elements via loop
> above
> dept_stat_pack.v_type_Dname(dept_stat_pack.v_CursorIndex)
:=
> v_Dname;
>
dept_stat_pack.v_type_TotalEmps(dept_stat_pack.v_CursorIndex)
> := v_TotalEmps;
>
dept_stat_pack.v_type_TotalSal(dept_stat_pack.v_CursorIndex)
> := v_TotalSal;
>
> dept_stat_pack.v_CursorIndex :=
dept_stat_pack.v_CursorIndex
> + 1;
>
> END LOOP;
>
> CLOSE c_AggregateData;
>
> END employee_insert_BUIDR;
>
> Last but not least after the insert I take the populated table and then
grab
> the newly inserted values and try to mesh them together for insertion in
my
> dept_stat table:
>
> CREATE OR REPLACE TRIGGER Update_Dept_State_AIUDR
> AFTER INSERT OR UPDATE OR DELETE ON Employee
> FOR EACH ROW
>
> DECLARE
>
> --Variable to hold dname of inserted row in employee
> v_hold_dname dept_stat.dname%TYPE;
>
> --Declaring variables for indices
> v_InsertIndex BINARY_INTEGER;
> v_LoopIndex BINARY_INTEGER;
>
> BEGIN
>
> --Grabbing upper limit for loop
> v_LoopIndex := dept_stat_pack.v_type_Dname.COUNT;
>
> --Initializing index for element position for looping
> v_InsertIndex := 1;
>
> --Checking to see if any records are in the table
> IF v_LoopIndex >= 1 THEN
>
> --Looping through inserts 1 record at a time by using the table
> types and indexes
> FOR FREE_VARIABLE IN 1..v_LoopIndex LOOP
>
> INSERT INTO dept_stat
> VALUES
(dept_stat_pack.v_type_Dname(v_InsertIndex),
> dept_stat_pack.v_type_TotalEmps(v_InsertIndex),
> dept_stat_pack.v_type_TotalSal(v_InsertIndex));
>
> v_InsertIndex := v_InsertIndex + 1;
>
> END LOOP;
>
> --Grabbing department name to constrain for updates
> SELECT dname
> INTO v_hold_dname
> FROM dept_stat
> WHERE dname = (select dname from department where dnumber =
> :new.dno);
>
> --Inserting record if dept name is not present
> IF SQL%NOTFOUND THEN
>
> --Grabbing department name for insertion.
> SELECT dname
> INTO v_hold_dname
> FROM department
> WHERE dname = (select dname from department where dnumber =
> :new.dno);
>
> --Insertion
> INSERT INTO dept_stat VALUES(v_hold_dname, 1, :new.salary);
>
> --Else update table dept_stat
> ELSE
> --Updating dept_stat w/ new values
> UPDATE dept_stat SET total_emps = total_emps + 1
> WHERE dname = v_hold_dname;
>
> --Updating dept_stat w/ new values
> UPDATE dept_stat SET total_sal = total_sal + :new.salary
> WHERE dname = v_hold_dname;
>
> END IF;
>
> --Inserting record if there are no records in the table
> ELSE
>
> --Grabbing department name for insertion.
> SELECT dname
> INTO v_hold_dname
> FROM department
> WHERE dname = (select dname from department where dnumber =
> :new.dno);
>
> --Insertion
> INSERT INTO dept_stat VALUES(v_hold_dname, 1, :new.salary);
>
> END IF;
>
> END Update_Dept_State_AIUDR;
>
>
> And so all DB objects compile correctly (for this effort), but when I run
> the script mentioned above I get:
>
> BEGIN AddEmp('Franklin', 'T', 'Wong', '333445555', '08-Dec-1955', '638
Voss,
> Houtston, TX', 'M', 40000, '888665555', 5); END;
>
> *
> ERROR at line 1:
> ORA-01422: exact fetch returns more than requested number of rows
> ORA-06512: at "SCM24.UPDATE_DEPT_STATE_AIUDR", line 32
> ORA-04088: error during execution of trigger
'SCM24.UPDATE_DEPT_STATE_AIUDR'
> ORA-06512: at "SCM24.ADDEMP", line 14
> ORA-06512: at line 1
>
>
> BEGIN AddEmp('Alicia', 'J', 'Zelaya', '999887777', '19-Jul-1968', '3321
> Castle, Spring, TX', 'F', 25000, '987654321', 4); END;
>
> *
> ERROR at line 1:
> ORA-01403: no data found
> ORA-01403: no data found
> ORA-06512: at "SCM24.UPDATE_DEPT_STATE_AIUDR", line 32
> ORA-04088: error during execution of trigger
'SCM24.UPDATE_DEPT_STATE_AIUDR'
> ORA-06512: at "SCM24.ADDEMP", line 14
> ORA-06512: at line 1
>
> and on..........................................
>
> With all that said, the operation did insert the following rows in the
> following tables:
>
> [CRC] SQL> select * from employee;
>
> FNAME M LNAME SSN BDATE ADDRESS
> S SALARY
> --------------- - --------------- --------- --------- --------------------
--
> -------- - ----------
> SUPERSSN DNO
> --------- ----------
> John B Smith 123456789 09-JAN-65 731 Fondren,
Houtston,
> TX M 30000
> 333445555 5
>
>
> [CRC] SQL> select * from dept_stat;
>
> DNAME TOTAL_EMPS TOTAL_SAL
> --------------- ---------- ----------
> Research 1 30000
>
>
> So, any thoughts on were I am going wrong? I double-checked the SQL in my
> queries and cursor and nothing jumps out at me.... Also, what's the
better
> approach?
>
>
>
>
>
Seems an awful lot of work, why no just use a View or a materialized view to
keep track of the totals.
Your errors stem from not handling any errors in your application or
checking conditions of too may or not enough rows.
Jim
Received on Sun Aug 31 2003 - 16:19:42 CDT
