| Trigger [message #308853] |
Tue, 25 March 2008 13:14  |
maanasiiii
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.
Thanks,
Manasi
[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  |
 |
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
|
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).
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
