1. Before a trigger can be created, the user SYS must run a SQL script commonly called CATPROC.SQL (or DBMSSTDX.SQL). The exact name and location of this script depend on your operating system.

   	To create a trigger in your own schema on a table in your own schema or on your own schema (SCHEMA), you must have the CREATE TRIGGER privilege.
   	To create a trigger in any schema on a table in any schema, or on another user's schema (schema.SCHEMA), you must have the CREATE ANY TRIGGER privilege.
   	In addition to the preceding privileges, to create a trigger on DATABASE, you must have the ADMINISTER DATABASE TRIGGER system privilege.

    

2. Use the following SQL statement to create two tables call emp_tab and dept.

   CREATE TABLE DEPT_TAB (
   	DEPTNO NUMBER(2) NOT NULL, 
   	DNAME VARCHAR2(14 byte), 
   	LOC VARCHAR2(13 byte), 
   	CONSTRAINT PK_DEPT PRIMARY KEY(DEPTNO) );
   
   CREATE TABLE EMP_TAB (
   	EMPNO NUMBER(4) NOT NULL, 
   	ENAME VARCHAR2(10 byte), 
   	JOB VARCHAR2(9 byte), 
   	MGR NUMBER(4), 
   	HIREDATE DATE, 
   	SAL NUMBER(7, 2), 
   	COMM NUMBER(7, 2), 
   	DEPTNO NUMBER(2), 
   	CONSTRAINT FK_DEPTNO FOREIGN KEY(DEPTNO) REFERENCES DEPT_TAB(DEPTNO), 
   	CONSTRAINT PK_EMP PRIMARY KEY(EMPNO) );

3. Suppose that you want to audit the activities with the table, you can set up the following data structures before setting up a trigger:

   CREATE TABLE Audit_table (
      Seq      NUMBER,
      User_at  VARCHAR2(10),
      Time_now DATE, 
      Term     VARCHAR2(10),
      Job      VARCHAR2(10), 
      Proc     VARCHAR2(10), 
      enum     NUMBER);
   CREATE SEQUENCE Audit_seq;
   CREATE TABLE Audit_table_values (
      Seq      NUMBER, 
      Dept     NUMBER, 
      Dept1    NUMBER, 
      Dept2    NUMBER);

4. Now you're ready to set up the following trigger.

   CREATE OR REPLACE TRIGGER Audit_emp
      AFTER INSERT OR UPDATE OR DELETE ON Emp_tab
      FOR EACH ROW
      DECLARE
         Time_now DATE;
         Terminal CHAR(10);
      BEGIN
         -- get current time, and the terminal of the user:
         Time_now := SYSDATE;
         Terminal := USERENV('TERMINAL');
         -- record new employee primary key
         IF INSERTING THEN 
            INSERT INTO Audit_table
               VALUES (Audit_seq.NEXTVAL, User, Time_now,
                  Terminal, 'Emp_tab', 'INSERT', :new.Empno);
         -- record primary key of the deleted row:
         ELSIF DELETING THEN                           
            INSERT INTO Audit_table
               VALUES (Audit_seq.NEXTVAL, User, Time_now,
                  Terminal, 'Emp_tab', 'DELETE', :old.Empno);
         -- for updates, record the primary key
         -- of the row being updated:
         ELSE 
            INSERT INTO Audit_table
               VALUES (audit_seq.NEXTVAL, User, Time_now,
                  Terminal, 'Emp_tab', 'UPDATE', :old.Empno);
            -- and for SAL and DEPTNO, record old and new values:
            IF UPDATING ('SAL') THEN
               INSERT INTO Audit_table_values
                  VALUES (Audit_seq.CURRVAL, 'SAL',
                     :old.Sal, :new.Sal);
   
            ELSIF UPDATING ('DEPTNO') THEN
               INSERT INTO Audit_table_values
                  VALUES (Audit_seq.CURRVAL, 'DEPTNO',
                     :old.Deptno, :new.DEPTNO);
            END IF;
         END IF;
   END;

5. Alter the "HR"."JOBS" table by adding a field as the number of employees. Create a trigger in "HR"."EMPLOYEES" to ensure the accuracy of that field.
6. Create a trigger in "HR"."JOBS" to make sure no changes to the salary range will make some one's salary fall outside the range of salaries for that type of job.

Back to CS643 schedule