Can someone please assist me with this Oracle PL/SQL trigger question??

 

Employees may move to different departments. We want to keep track of the departments where eachemployee has been. To do so we create a new tableemployee_historythat keeps track of suchhistory.EMPLOYEE_HISTORY(EMPLOYEE_ID, EMPLOYEE_NAME, OLD_DEPARTMENT_NAME,NEW_DEPARTMENT_NAME, EFFECTIVE_DATE);Write a triggeremp_hist_trgthat monitors theemployeetable as follows.ï‚·When a row (record) is inserted into theemployeetable, the trigger automatically inserts a row(record) into theemployee_historytablein any situations.oTheOLD_DEPARTMENT_NAMEis always'(New Hire)'.oFind the new department name from thedepartmenttable based on the newdepartment_id.If the newdepartment_idisNULL, theNEW_DEPARTMENT_NAMEwill be'TBA'.ï‚·When an employeechangeshis/herdepartment (the olddepartment_idis not equal to thenewdepartment_id), the trigger automatically inserts a row (record) into theemployee_historytable. (If both the olddepartment_idand newdepartment_idareNULL(fromNULLdepartment toNULLdepartment), the trigger does not insert a row (record)into theemployee_historytable.)oFind the old department name from thedepartmenttable based on the olddepartment_id.If the olddepartment_idisNULL, theOLD_DEPARTMENT_NAMEwill be'TBA'.oFind the new department name from thedepartmenttable based on the newdepartment_id.If the newdepartment_idisNULL, theNEW_DEPARTMENT_NAMEwill be'TBA'.ï‚·TheSYSDATEcan be usedin theEFFECTIVE_DATEcolumn.ï‚·You can assume that the insert/update statements do not violate the integrity constraints betweenthedepartmentandemployeetables.ï‚·No temporary table/view/procedure/function is allowed in your program.ï‚·You can only use thedepartment,employee, andemployee_historytables in yourtrigger. You will get a zero point if you use a different table (e.g., different table names, columnnames, or data types) in your trigger.Step 1) (0 point) Create theemployee_historytable,CREATE TABLE employee_history(EMPLOYEE_IDNUMBER(4)NOT NULL,EMPLOYEE_NAMEVARCHAR2(50)NOT NULL,OLD_DEPARTMENT_NAMEVARCHAR2(100)NOT NULL,NEW_DEPARTMENT_NAMEVARCHAR2(100)NOT NULL,EFFECTIVE_DATEDATENOT NULL);Step 2) Create the triggeremp_hist_trg.You will get a zero point if you use a different trigger name.

• 1495788472-trigger.docx

ComputerScienceExpert

(11)

Status NEW Posted 26 May 2017 08:05 AM My Price 8.00

-----------

Attachments

file 1495788473-Solutions file 2.docx preview (51 words )

H-----------ell-----------o S-----------ir/-----------Mad-----------am ----------- Th-----------ank----------- yo-----------u f-----------or -----------you-----------r i-----------nte-----------res-----------t a-----------nd -----------buy-----------ing----------- my----------- po-----------ste-----------d s-----------olu-----------tio-----------n. -----------Ple-----------ase----------- pi-----------ng -----------me -----------on -----------cha-----------t I----------- am----------- on-----------lin-----------e o-----------r i-----------nbo-----------x m-----------e a----------- me-----------ssa-----------ge -----------I w-----------ill----------- be----------- qu-----------ick-----------ly -----------onl-----------ine----------- an-----------d g-----------ive----------- yo-----------u e-----------xac-----------t f-----------ile----------- an-----------d t-----------he -----------sam-----------e f-----------ile----------- is----------- al-----------so -----------sen-----------t t-----------o y-----------our----------- em-----------ail----------- th-----------at -----------is -----------reg-----------ist-----------ere-----------d o-----------n -----------THI-----------S W-----------EBS-----------ITE-----------. ----------- Th-----------ank----------- yo-----------u -----------

Not Rated(0)

• Buy Answer