Re: Trigger question..please help.

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
> ('M','F')),
>       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;

Table truncated.

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
> employees).
>
> The third part of the question was
> 3)Create a STATEMENT_LEVEL_LOG with type and time (to the second) of
> modification.
>
> 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
> Section.
>
> 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
> DECLARE
>         v_action varchar2 (20);
> BEGIN
>     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,
>         v_action);
> END;
> /
>
> 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:

Department number
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:

1. update existing statistics
2. 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