1) Start with CHECK_SALARY trigger explain below on triggers. Show that that trigger creates MUTTATING TABLE error. In order to circumvent that problem, write a combination of a BEFORE statement trigger and a modified BEFORE ROW trigger. Use the before statement trigger to record values of v_maxsalary and v_minsalary as packaged variables. In the row trigger perform the needed range check using those packaged variables. Add an AFTER statement trigger that would set those packaged variables to NULL, making it required for the BEFORE statement trigger to fire and act properly for the business rule to be enforced. Show that the above error is not raised. This is a standard way of eliminating mutating table errors.
MutatingTable:Example
SQL>CREATEOR REPLACETRIGGERcheck_salaryBEFOREINSERT OR UPDATEOFsal,job ONempFOREACH ROW
WHEN (new.job<> 'PRESIDENT') DECLARE
v_minsalaryemp.sal%TYPE;
vmaxsalaryemp.sal%TYPE;
BEGIN
SELECT MIN(sal),MAX(sal)
INTO v_minsalary,v_maxsalary
FROM emp
WHEREjob= :new.job;
IF:new.sal
:new.sal>v_maxsalaryTHEN RAISE_APPLICATION_ERROR(-20505,
'Out of range');
;
END IF;
END;
SQL>UPDATEemp
SETsal=1500
WHEREename= 'SMITH';
*
ERRORatline2
ORA_4091:TableEMPismutating,trigger/functionmaynotseeit
ORA_06512:atline4
ORA_04088:errorduringexecutionoftrigger'check_salary';
This trigger, CHECK_SALARY, tries to guarantee that whenever a new employee is added to the EMP table or an existing employee's salary or job title is changed, the employee's salary falls within the established salary range for the employee's job
2) Suppose Entity Title on slide below of Lecture note. Implement that entity as a database table with two subtypes, Games and Movie. Let both subtypes have their own unique ids, e.g. movie_id and game_id. Create another entity (table) Rented_Item that records rented items. Let that table reference movies and games from table (entity) Title. Implement the referential integrity using triggers. Populate both tables with various rows and shows that the referential integrity may be maintained.