Trigger question..please help.

From: pereges <Broli00_at_gmail.com>
Date: Fri, 27 Mar 2009 00:28:44 -0700 (PDT)
Message-ID: <0d4e0340-7a9c-4e35-a4da-ffac53a89d8e_at_c11g2000yqj.googlegroups.com>



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 ? Received on Fri Mar 27 2009 - 02:28:44 CDT

Original text of this message