Re: Trigger question..please help.

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Fri, 27 Mar 2009 20:27:39 +0100
Message-ID: <49cd28ac$0$193$e4fe514c_at_news.xs4all.nl>

ddf schreef:
> Comments embedded.
>
> 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

I don't think you need a procedure for this. You don't need to query the emp table. Just do the bookkeeping in the trigger: If an employee is inserted, add 1 to total employees, and add the salary   to the total;
If an employee is deleted, substract the salary and substract 1 from total; if an employee gets updated, substract the old salary and add the new salary.

Of course, this will fail when the table is truncated.....

Shakespeare Received on Fri Mar 27 2009 - 14:27:39 CDT