TRIGGER

Create a trigger that when a workon record is added (namely, an employee workon a new project) the trigger will increase the salary of THIS employee who work on THIS project by 1% if this employees' salary is less than 85000. (for example, when your add a row (1, 100, 20) into workon table (Using INSERT statement), assuming employee 100 exists, you need to find out (use Where clause) if the employee's salary is less than 85000, if so, increase his salary.( Hint: use After trigger, and use :new to refer the workon table after the change ) see the notes of samples in Canvas.

show :

(1).trigger code.

(2) the table contents of employee and workon before triggering event (insert).

(3) table contents after triggering event. This way I can see if the trigger works or not. Also show your INSERT statement.

SQL CODE

drop table workon; drop table employee; drop table project; drop table division;

create table division (did integer, dname varchar (25), managerID integer, constraint division_did_pk primary key (did) );

create table employee (empID integer, name varchar(30), salary float, did integer, constraint employee_empid_pk primary key (empid), constraint employee_did_fk foreign key (did) references division(did) );

create table project (pid integer, pname varchar(25), budget float, did integer, constraint project_pid_pk primary key (pid), constraint project_did_fk foreign key (did) references division(did) );

create table workon (pid integer references project(pid), empID integer references employee(empID), hours integer, constraint workon_pk primary key (pid, empID) );

/* loading the data into the database */

insert into division values (1,'engineering', 2); insert into division values (2,'marketing', 1); insert into division values (3,'human resource', 3); insert into division values (4,'Research and development', 5); insert into division values (5,'accounting', 4);

insert into project values (1, 'DB development', 8000, 2); insert into project values (2, 'network development', 6000, 2); insert into project values (3, 'Web development', 5000, 3); insert into project values (4, 'Wireless development', 5000, 1); insert into project values (5, 'security system', 6000, 4); insert into project values (6, 'system development', 7000, 1);

insert into employee values (1,'kevin', 32000,2); insert into employee values (2,'joan', 42000,1); insert into employee values (3,'brian', 37000,3); insert into employee values (4,'larry', 82000,5); insert into employee values (5,'harry', 92000,4); insert into employee values (6,'peter', 45000,2); insert into employee values (7,'peter', 68000,3); insert into employee values (8,'smith', 39000,4); insert into employee values (9,'chen', 71000,1); insert into employee values (10,'kim', 46000,5); insert into employee values (11,'smith', 46000,1);

insert into workon values (3,1,30); insert into workon values (2,3,40); insert into workon values (5,4,30); insert into workon values (6,6,60); insert into workon values (4,3,70); insert into workon values (2,4,45); insert into workon values (5,3,90); insert into workon values (3,3,100); insert into workon values (6,8,30); insert into workon values (4,4,30); insert into workon values (5,8,30); insert into workon values (6,7,30); insert into workon values (6,9,40); insert into workon values (5,9,50); insert into workon values (4,6,45); insert into workon values (2,7,30); insert into workon values (2,8,30); insert into workon values (2,9,30); insert into workon values (1,9,30); insert into workon values (1,8,30); insert into workon values (1,7,30); insert into workon values (1,5,30); insert into workon values (1,6,30); insert into workon values (2,6,30);