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)
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.
So, Example I have made for this businees rule is something like this. 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].
I am going to test the code which I'm trying on the employee 123456789 from the employee table which I have already created.
Thanks,
Manasi