Trigger question..please help.
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