Tema nr. 8 Observație! Scrieți rezolvarea direct în acest document! 1. A business rule states that each time one or more employees are added to the employees
table, an audit record must also be created. This rule could be enforced using application code, but we have decided to enforce it using a DML statement trigger. A. Create an audit table by executing the following SQL statement: CREATE TABLE audit_table (action VARCHAR2(20), user_name VARCHAR2(30) DEFAULT USER, last_change_date TIMESTAMP DEFAULT SYSTIMESTAMP); B. Create a statement level trigger that inserts a row into the audit table immediately after one or more rows are added to the employees_dup table that you created in a previous lesson. The audit table row should contain value “Inserting” in the action column. The other two columns should have their default values. Save your trigger code for later. CREATE TABLE employees_dup AS (SELECT * FROM employees) CREATE OR REPLACE TRIGGER audit_trigger AFTER INSERT ON employees_dup BEGIN INSERT INTO audit_table(ACTION) VALUES('Inserting'); END; C. Test your trigger by inserting a row into employees, then querying the audit table to see that it contains a row. ACTION
USER_NAME
Inserting APEX_PUBLIC_USER
LAST_CHANGE_DATE 14-DEC-14 12.12.01.701007 PM
D. Make sure the trigger does not fire with a DELETE by deleting the employee you just entered. Recheck the audit_table table to make sure that there is not another new row. ACTION
USER_NAME
Inserting APEX_PUBLIC_USER
LAST_CHANGE_DATE 14-DEC-14 12.12.01.701007 PM
2. Modify Triggers: a. Modify your audit table trigger from question 1B so that it inserts a row into the audit table immediately before one or more employee salaries are updated. The audit table row should contain value “Updating” in the action column. CREATE OR REPLACE TRIGGER audit_trigger BEFORE UPDATE OF salary ON employees_dup BEGIN INSERT INTO audit_table(ACTION) VALUES('Updating'); END;
b. Test your trigger by updating the salary of a non-existent employee (employee_id = 999), then querying the audit table to see that it contains a new row. UPDATE employees_dup SET salary=1000 WHERE employee_id = 999; ACTION
USER_NAME
LAST_CHANGE_DATE
Updating APEX_PUBLIC_USER
14-DEC-14 12.14.51.576711 PM
Updating APEX_PUBLIC_USER
14-DEC-14 12.15.28.161834 PM
Inserting APEX_PUBLIC_USER
14-DEC-14 12.12.01.701007 PM
c. Modify your trigger so that it prevents employees’ salaries being updated outside working hours. The trigger should allow updates at other times (and still insert a row into the audit table), but should raise an application error if an update is attempted before 8am or after 6pm on any day. CREATE OR REPLACE TRIGGER audit_trigger BEFORE UPDATE OF salary ON employees_dup BEGIN IF (TO_CHAR(SYSDATE, 'HH24') < 9 OR TO_CHAR(SYSDATE, 'HH24') > 18) THEN RAISE_APPLICATION_ERROR(-20101, 'Update during business hours'); END IF; INSERT INTO audit_table(ACTION) VALUES('Updating'); END;
d. You want to test your modified trigger. However, you need to make sure that right now the database time is outside working hours. Remember that the database could be anywhere in the world and therefore the database may not be in your time zone! Find the current database time by executing: SELECT TO_CHAR(SYSDATE,'HH24:MI') FROM dual; e. Now modify your trigger so that it will raise the application error if you try to update a salary within the next hour. For example, if the database time is 10:30, modify the trigger code to include: … BETWEEN ’10:30’ AND ’11:30’ … f. Test your modified trigger by trying to update the salary of employee_id 100 to a new value of 25000. UPDATE employees SET salary = 25000 WHERE employee_id = 100;
3. Triggers: A. Retrieve the code for the AFTER INSERT trigger you created in the previous practice, question 1B. If you have lost the code, here it is again: CREATE OR REPLACE TRIGGER emp_audit_trigg AFTER INSERT ON employees BEGIN INSERT INTO audit_table (action) VALUES ('Inserting'); END; B. Modify this trigger so that a DELETE on the employees table will fire the same trigger. Use the conditional predicates so an insert adds a row to the audit_emp table with ‘Inserted’ for the action column and a delete adds a row with ‘Deleted’ in the action column. Save the script and test your trigger by inserting an employee row and then deleting the same row, querying the audit table each time. CREATE OR REPLACE TRIGGER emp_audit_trigg AFTER DELETE ON employees BEGIN INSERT INTO audit_table (action) VALUES ('Deleted'); END; C. Add a new column called emp_id to the audit_table table. This column will contain the employee id of the worker whose record was inserted or deleted. Modify your trigger to be a row trigger so it will fire once for each row affected. The inserts into the audit_emp table should now include the employee id of the affected employee. CREATE OR REPLACE TRIGGER emp_audit_trigg before DELETE OR INSERT ON employees_dup FOR EACH ROW BEGIN INSERT INTO audit_table (action,emp_id) VALUES ('Deleted',:old.employee_id); END;
D. Test your trigger. First, turn off Autocommit in Application Express (you will need to rollback your changes later). Then, delete the three Sales Representatives (job_id = ‘SA_REP’). Query the audit table; you should see that three audit rows have been inserted. Finally, rollback your changes.
ACTION
USER_NAME
LAST_CHANGE_DATE
EMP_ID
Updating APEX_PUBLIC_USER
14-DEC-14 12.14.51.576711 PM
-
Updating APEX_PUBLIC_USER
14-DEC-14 12.15.28.161834 PM
-
Deleted
APEX_PUBLIC_USER
14-DEC-14 12.45.22.645332 PM
103
Inserting APEX_PUBLIC_USER
14-DEC-14 12.12.01.701007 PM
-
4. Create a row trigger: A. That displays the maximum salary in the employees table, and is fired immediately before an employee’s salary is updated. CREATE OR REPLACE TRIGGER emp_update_salary before update on employees_dup FOR EACH ROW declare v_max_salary number(8,2); BEGIN If(:new.salary != :old.salary)then select max(salary) into v_max_salary from employees_dup; dbms_output.put_line('Max salary'|| v_max_salary); end if; END; B. Test your trigger by attempting to update the salary of employee_id 100 to a new value of 25000. What happens and why? I have an error.
