Here's another question that I hope you can assist me with?

 

Create a trigger calledjob_min_sal_trgon theemployeetable. When anINSERTorUPDATEstatement is issued against theemployeetable, the trigger is fired to ensure that the value of theSALARYcolumn meetsthe criteria in thejob_minimum_salarytable.(For example, you can findthat the minimum salary for a programmer is 800 from thejob_minimum_salarytable. Your triggerensures that the salary for a programmer in theemployeetable is greater than or equal to 800.)Step 1) (0 point) Create a tablejob_minimum_salaryas follows.CREATE TABLE job_minimum_salary(JOBVARCHAR2(50) PRIMARY KEY,SALARYNUMBER(7, 2) NOT NULL);Step 2) (0 point) Populate thejob_minimum_salarytable as follows.INSERT INTO job_minimum_salary VALUES ('ANALYST', 2000);INSERT INTO job_minimum_salary VALUES ('DATABASE ADMINISTRATOR', 2500);INSERT INTO job_minimum_salary VALUES ('PRESIDENT', 4800);INSERT INTO job_minimum_salary VALUES ('PROGRAMMER', 800);INSERT INTO job_minimum_salary VALUES ('PUBLIC ACCOUNTANT', 2400);INSERT INTO job_minimum_salary VALUES ('SALESMAN', 1800);INSERT INTO job_minimum_salary VALUES ('VICE PRESIDENT', 3800);INSERT INTO job_minimum_salary VALUES ('TBA', 1800);COMMIT;Step 3) Create the triggerjob_min_sal_trg.Thejob_minimum_salarytable is read-only. Your triggercannotmodify any rows in thejob_minimum_salarytable.You must get the minimum salaries from thejob_minimum_salarytable in your program.Hard coding, except the string'TBA', is not allowed in your program (e.g.,IF job ='SALESMAN' THEN v_min_sal = 1800 …).If the job cannot be found from thejob_minimum_salarytable (e.g., Program Facilitator),the job is considered as “TBA”. (You need to check whether the salary is equal to or greater thanthe minimum salary forjob='TBA'.)If the salary is equal to or greater than the minimum salary of the corresponding job, the triggerdoes not change anything.If the salary is less than the minimum salary of the corresponding job, the trigger increases thesalary to the minimum salary of the corresponding job.No temporary table/view/procedure/function is allowed in your program.To avoid amutating table error, please take a look examples on page 6, class handout 8.(Hint:you cannot use someINSERT/UPDATEstatements to modify theemployeetable in yourtrigger.)You will get a zero point if you use a different trigger name.If you modified theemployeetable created in Assignment #1, please delete and re-populate it.Step 4) Test your trigger.

Attachments:

• 1495787508-trigger1.docx