Re: Trigger question..please help.

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Fri, 27 Mar 2009 06:44:10 -0700 (PDT)
Message-ID: <0b9c4fad-f2fe-44ab-960b-8af6e4b0b5c9_at_l3g2000vba.googlegroups.com>

On Mar 27, 3: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));
>
> 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;
>
> Table created.
>
> Truncating all instances of employee table:
>
> SQL> delete from employee;
>
> 8 rows deleted.
>
> 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].
>
> 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 ?

What? Wouldn't you just perform an insert followed by a commit into the table?
Then run an update statement, commit followed by a delete, commit. Now query your log table.

HTH -- Mark D Powell -- Received on Fri Mar 27 2009 - 08:44:10 CDT