/* setup the page */
spool ora_ex2
set echo on
set pagesize 66
set linesize 80


/* this file demonstrates the use of a trigger */

/* Example of how to create tablespace if you need it. This *might* be a bad example:
create tablespace users datafile 'HOME.TH' size 600K reuse
default storage (initial 25K next 10K
minextents 1 maxextents 100
pctincrease 0);
*/ 

/* create an employee table to store each employee's information */
create table employee
(	emp_num	number,
	name		varchar2(25),
	address		varchar2(60),
	salary		number,
	status		char(1),
	hiredate		date,
	constraint pk_emp_num PRIMARY KEY(emp_num)
		using index TABLESPACE users
)
	STORAGE	( INITIAL 1K
				  NEXT     1K
				  PCTINCREASE 0)
	TABLESPACE USERS ;					 
				
comment on table employee is
' created 3/11/97 by NKT, Primary Key is emp_num,
max 10,000 records at 10K each. Storage space changed 3/18/97 as per BR.
Related to: Emp_Audit ' ;

comment on column employee.status is
'Status indicates if an employee is active or inactive.' ;

/* create an audit table to store salary changes on updates */
create table emp_audit
( 	emp_num 		number,
	change_date		date,
	old_sal			number,
	new_sal			number,
	constraint pk_emp_num_aud PRIMARY KEY (emp_num, change_date)
		using index TABLESPACE users
/* 	taken out 3/18/97 as per BR
	,
	constraint fk_change_date FOREIGN KEY (emp_num)
		REFERENCES employee(emp_num) on delete cascade
*/
)
	STORAGE (	INITIAL 1K
				NEXT    1K
				PCTINCREASE  0)
	TABLESPACE USERS ;
	
comment on table emp_audit is
'created 3/12/97 by NKT. Primary key is emp_num 
and change_date concatenated.
Foreign key to employee is emp_num. Taken out 3/18/97 as per BR.
Max 100,000 records at 5K per record. Storage space changed as per BR.
Related to employee table.' ;

/* insert records into the employee table */
INSERT INTO employee VALUES
	(1, 'Nannette Thacker', '4560 S. Hydraulic',
150000,'A','10-MAR-97');
INSERT INTO employee VALUES
	(2, 'Joe Namath', '760 S Main', 95000,'A','09-JAN-97');
INSERT INTO employee VALUES
	(3, 'Jim Kick', '420 S. Irving', 25000,'A','12-JAN-97');
INSERT INTO employee VALUES
	(4, 'Larry Zonka', '550 S. Green', 15000,'A','15-JAN-97');
INSERT INTO employee VALUES
	(5, 'Joe Montana', '470 E. Osage', 70000,'A','18-JAN-97');
INSERT INTO employee VALUES
	(6, 'Michael Jordan', '624 N. Maple', 45000,'A','12-FEB-97');
INSERT INTO employee VALUES
	(7, 'Larry Bird', '7250 E. Chestnut', 40000,'A','18-FEB-97');
INSERT INTO employee VALUES
	(8, 'Bart Simpson', '1521 E. Pine', 160000,'A','19-FEB-97');
INSERT INTO employee VALUES
	(9, 'Charlie Brown', '1730 E. Red Oak', 72000,'A','01-MAR-97');
INSERT INTO employee VALUES
	(10, 'Ronald Reagan', '1512 Broadway Avenue',
53000,'A','04-MAR-97');

/* INSERT INTO emp_audit values
(1,'11-MAR-97',125000,150000);
example: not needed */

/* 	create a trigger to insert a record in the emp_audit database if
the 
	salary has been changed  -- nkt
*/	
CREATE TRIGGER bef_upd_employee
	before update on employee
		for each row when (old.salary != new.salary)
begin
	insert into emp_audit
	values (:old.emp_num,
			sysdate,
			:old.salary,
			:new.salary);
end;
/
			
			
/* give everyone a 20 percent raise */
update employee
set salary = salary * 1.20 ;

/* view the table */
select * from emp_audit ;

/* cancel the changes made to the table, including the inserts */
rollback ;

/* view the table again */
select * from emp_audit ;

/*      
	create a trigger to check if the salary is ever changed to be
less than the
	 current salary  or if the hiredate is ever less than the
current date,
	 if so, pop an error. If updating and the salary is more than
the current salary,
	 then insert a record in the audit table with the current date
and the old and
	 new salary, as well as the employee's number   -- nkt
*/	 
create or replace trigger bef_upd_employee
before insert or update on employee
for each row
when (old.salary != new.salary or
		new.hiredate <= sysdate )
declare
	salary_error EXCEPTION ;
	hiredate_error EXCEPTION ;
begin
	IF UPDATING and :old.salary > :new.salary THEN
		RAISE salary_error ;
	ELSIF UPDATING and :old.salary < :new.salary THEN
		insert into emp_audit
			values (:old.emp_num,
					sysdate,
					:old.salary,
					:new.salary) ;
	ELSIF INSERTING and :new.hiredate <= sysdate THEN
		RAISE hiredate_error ;
	END  IF;
	EXCEPTION
		when salary_error THEN
			RAISE_APPLICATION_ERROR (-20001,
				'You just tried to lower the salary. Not
allowed. Transaction update aborted.');
		when hiredate_error THEN
			RAISE_APPLICATION_ERROR(-20002,
				'The hire date is less than the current
date. Not allowed. Transaction insert aborted.');
		when others THEN
			RAISE_APPLICATION_ERROR(-20003,
				'An unknown error occurred.');
end ;
/

/* add record with new hire date less than the current date to test the
error */
INSERT INTO employee VALUES
	(11, 'Merry Krismas', 'S. Broadway',33000,'N','04-MAR-97');
/* add a record with a valid hire date for a test */	
INSERT INTO employee VALUES
	(12, 'Joe Blow', 'S. Fordway',23000,'N','29-MAR-97');


/* update a salary to add 5, to test the trigger */
update employee
set salary = salary  + 5 where  emp_num = 12 ;
/* update a salary to deduct 5 to test the trigger */
update employee
set salary = salary  - 5 where  emp_num = 3 ;


/* view the table */
select * from emp_audit ;

/* get rid of all changes made */
rollback ;

/* view the table again */
select * from emp_audit ;

/* drop the tables, including any constraints */
drop table employee cascade constraints;
drop table emp_audit cascade constraints ;


/* comment out for now */
/* drop tablespace users including contents ; */

/* turn off spooling */
spool off