SQL QUeries 3

Solve the assignment

(A) Open and read file  

Notes and examples on oracle trigger x

Actions

  (This file is used in the lecture video)

1) explain the ROLE of the following trigger, and 

2) run the following code and test the trigger . Namely,  insert a row in related table (show the INSERT statement) and show the content of the table before and after triggering event.

create or replace trigger workon_ai_trigger
after insert on workon
for each row
begin
update employee

   set totalprojects = totalprojects + 1
   where employee.empid = :new.empid;
end ;

(B) Watch the lecture video about “Trigger” and do the following tasks:

Create a trigger that when a workon record is added (namely, an employee works on a new project so an new row is INSERTED) 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, your trigger code needs 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 examples in “notes of trigger and its samples” posted at Canvas.

show me:

(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 (insert a row into workon table to test your trigger).

Read the SQL note in for examples of triggers (link above)
. Write your trigger code in notepad and paste into SQL window. If you see “trigger created ” then you can test the trigger by inserting a row into workon table. Then use select * from employee to see if or not the corresponding salary of qualified employee is changed). Make sure that your trigger does not have error.

Note, if you have tried very hard and your code is still not working, do not worry, just submit your code. You may still get most of the credits for this work if the logic is correct.

· Notes and examples on oracle trigger

Create trigger
A trigger is a stored program that is attached to a table or view. It is used to maintain the data integrity in database system. The trigger code is invoked by DBMS when an insert, update or delete request is issued on the table to which a trigger is defined.

A row level trigger is in the format
1)
Create trigger trigger_name
Before/after triggering_event on table_name
For each row
Begin
Any SQL or PL/SQL statements
End
A triggers is stored and called/fired implicitly when a triggering event (insert, update or delete) occurs. A trigger can be designed to fire before or after the triggering event.
e.g. (when delete a course, a trigger will delete all its enrollment records)
2)

create or replace trigger employee_bd_trigger
before delete on employee
for each row
begin
delete workon where empid = :old.empid;
end;
Type in ‘show error’ to display the complilation error. Pay attention to the condition in delete statement. In each triggering event, Oracle create two virtual tables to keep the ‘before’ and ’a fter’ image of the table being changed. :old refers to the before image, :new refers to the after image. You can refer both in a trigger.
Using above code as an example, you can do the assignment.

 

More examples:

/create a trigger that increments the project count for an employee where time the employee has a new workon record added.

3)

create or replace trigger workon_ai_trigger
after insert on workon
for each row
begin
update employee

set totalprojects = totalprojects + 1
where employee.empid = :new.empid;
end ;

4)
/* for delete , …*/
create or replace trigger workon_ad_trigger
after delete on workon
for each row
begin
update employee

set totalprojects = totalprojects – 1
where employee.empid = :old.empid;
end

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,
empID integer,
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 employee
values (12,’joan’, 48000,1);
insert into employee
values (13,’kim’, 49000,2);
insert into employee
values (14,’austin’, 46000,1);
insert into employee
values (15,’sam’, 52000,3);
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);
insert into workon
values (2,12,30);
insert into workon
values (3,13,30);
insert into workon
values (4,14,20);
insert into workon
values (4,15,40);