Re: Trigger question..please help.
Date: Fri, 27 Mar 2009 07:59:45 -0700 (PDT)
On Mar 27, 2:28 am, pereges <Brol..._at_gmail.com> wrote:
> Hello, I've an employee table which I created as follows:
> CREATE TABLE employee
> (fname VARCHAR2(15) NOT NULL,
> minit VARCHAR2(1) NULL,
> lname VARCHAR2(15) NOT NULL,
> ssn VARCHAR2(9) NOT NULL,
> bdate DATE NULL,
> address VARCHAR2(30) NULL,
> city VARCHAR2(15) NULL,
> state VARCHAR2(2) NULL,
> zipcode VARCHAR2(10) NULL,
> sex VARCHAR2(1) NULL CHECK (sex IN
> salary NUMBER(10,2) NULL,
> superssn VARCHAR2(9) NULL,
> dno NUMBER NOT NULL,
> CONSTRAINT employee_pk PRIMARY KEY(ssn));
No foreign key to DEPARTMENT to ensure the DNO value exists?
> I also have a department table:
> CREATE TABLE department
> (dname VARCHAR2(15) NOT NULL,
> dnumber NUMBER NOT NULL,
> mgrssn VARCHAR2(9) NOT NULL,
> mgrstartdate DATE NULL,
> CONSTRAINT department_pk PRIMARY KEY (dnumber),
> CONSTRAINT department_uq1 UNIQUE (dname));
> then employee was populated with 8 rows and department with 3.
> Now the first part of this question was to 1)Backup the EMPLOYEE
> table, then truncate all instances of the EMPLOYEE TABLE. I did this
> as follows:
> Creating backup table for employee table.
> SQL> create table employee_bk AS
> 2 select *
> 3 from employee;
That's a copy of the table, but, yes, it can serve as a 'backup'.
> Table created.
> Truncating all instances of employee table:
> SQL> delete from employee;
> 8 rows deleted.
No, that's not truncating the table, which your instructions clearly stated. This truncates the table:
SQL> truncate table employee;
which does much more than delete the data.
> Second part of the question was to 2)Create a table called DEPT_STAT1
> with the following attributes, dname[varchar2(15)], total_emps
> [number], total_salary[number].
I don't see that you've created that table from your post.
> This table will basically store the stats for every department (name o
> f department, total employees in it and the total salary of all
> The third part of the question was
> 3)Create a STATEMENT_LEVEL_LOG with type and time (to the second) of
> SQL> create table statement_level_log (
> 2 log_date DATE,
> 3 log_user VARCHAR2 (20),
> 4 log_desc VARCHAR2 (20));
> Table created.
> This table will basically log all the changes made to employee table.
> I''m stuck with the fourth part which is:
> 4)Define a statement-level trigger called UpdateDeptStatSL that keeps
> the DEPT_STAT1 table and the log table up-to-date with changes made to
> the EMPLOYEE table. Consider all 3 DML types. Include an Exception
> This is my attempt at trying to change the log table
> (STATEMENT_LEVEL_LOG) with the trigger but I cannot figure out a way
> to change the DEPT_STAT1 table:
> CREATE OR REPLACE TRIGGER UpdateDeptStatSL
> AFTER INSERT OR UPDATE OR DELETE ON employee
> v_action varchar2 (20);
> IF INSERTING THEN
> v_action := 'Added employee(s)';
> ELSIF UPDATING THEN
> v_action := 'Updated employee(s)';
> ELSIF DELETING THEN
> v_action := 'Deleted employee(s)';
> END IF;
> INSERT INTO statement_level_log VALUES (SYSDATE, USER,
> How can I update the DEPT_STAT1 table ?
Look back at your instructions for the DEPT_STAT1 table and see what your instructor says should be stored there:
Number of employees for that department
Total salary for the department employees
Without using a procedure your trigger won't execute since you need to query the EMPLOYEE table to get the data for DEPT_STAT1, so you need to write a stored procedure to maintain the DEPT_STAT1 table to:
- update existing statistics
- delete department data for departments which no longer exist
This isn't difficult, but it is necessary to get this to work properly. Once you get the procedure written and working you'd then call it after the INSERT statement in your existing trigger. We won't write this for you (although I do have a working example) but we are here to assist you with your efforts.
David Fitzjarrell Received on Fri Mar 27 2009 - 09:59:45 CDT