Home » Other » Training & Certification » Trigger
Trigger [message #308853] Tue, 25 March 2008 13:14 Go to next message
Messages: 1
Registered: March 2008
Junior Member
I have to create a trigger having the following database. I'm confused in using the trigger type(row-level,st-type). Please help me out.

Consider the relation schema of the COMPANY database given below

EMPLOYEE (fname, minit, lname, ssn, birthdate, address, sex, salary, superssn, dno) KEY: ssn

DEPARTMENT (dname, dnumber, mgrssn, mgrstartdate)KEY: dnumber.

PROJECT (pname, pnumber, plocation, dnum)KEY: pnumber.

WORKS_ON (essn, pno, hours)KEY: (essn, pno)

DEPENDENT (essn, dependent-name, sex, bdate, relationship)
KEY: (essn, dependent-name)

Write a PL/SQL TRIGGER(s) to enforce the following business rule

The assignments of an employee –removed from the company- must be partially absorbed by his/her co-workers. Under this temporary policy 1/10 of the hours dedicated to a project will be uniformly distributed among her current project co-workers.

Example. Consider employee 999222333 works in projects 10 and 20 for a total of 10 and 30 hours respectively. Her project 10 coworkers are {222333444, 444555666}. Similarly her project 20 coworkers are {222333444, 555666777, 777888999}. In order to enforce the new rule, employees {222333444, 444555666} will get an increment of ½ hour on their workload for project 10 [observe that 1/2 hour is computed as (10 hours * 1/10) / 2 emp)]. Likewise, employees {222333444, 555666777, 777888999} will see their weekly workload for project 20 increased by 1 hour [here 1 hour is computed as = (30 * 1/10)/3].

Print a listing of your well documented/organized code. Print images of the Works-On table before and after each test is made. Test the trigger as follows:

1. Remove employee 123456789 from the database.

Caution: Each time you delete/create a table, its associations to existing triggers are broken. After a table is created you need to re-compile the triggers protecting the table.


[Updated on: Tue, 25 March 2008 13:18]

Report message to a moderator

Re: Trigger [message #308856 is a reply to message #308853] Tue, 25 March 2008 13:16 Go to previous message
Michel Cadot
Messages: 67761
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is Homework and should be posted in Homework forum.

Post a test case: create table and insert statements.
Also post the result you want with these data.

Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Previous Topic: Error ORA-00947(not enough values) - FORALL
Next Topic: Regarding OCA- pl/sql
Goto Forum:

Current Time: Fri Mar 05 02:04:27 CST 2021