Section 10: Using and Managing Triggers
10.01. Introduction to Triggers
♦False
♦Enforcing a complex business rule
♦Enforcing a complex database security check
♦Recalculating the total salary bill for a department whenever an employee’s salary is changed
♦Ensuring that a student never arrives late for a class (*)
♦Keeping a log of how many rows have been inserted into a table
3. Which of the following best describes a database trigger?
♦It allows users to log on to the database
♦It executes automatically whenever a particular event occurs within the database (*)
♦It prevents unique constraints from being violated
♦It executes automatically whenever a user clicks on a button with their mouse
♦It allows foreign key constraints to be violated
4. Which of the following are NOT allowed within a database trigger? (Choose two)
♦COMMIT (*)
♦A call to a packaged procedure
♦INSERT
♦A Boolean variable
♦SAVEPOINT (*)
5. A database trigger is a PL/SQL stored subprogram which is explicitly invoked just like a procedure or a function. True or False?
♦True
♦False (*)
6. A business rule states that an employee’s salary must be between 4000 and 30000. We could enforce this rule using a check constraint, but it is better to use a database trigger. True or False?
♦True
♦False (*)
7. You can use a database trigger to prevent invalid transactions from being committed. True or False?
♦True (*)
♦False
8. Which of the following events could NOT automatically fire a database trigger?
♦A user logging on to the database
♦A SQL INSERT statement
♦You click your mouse on a button to choose the correct answer to this question (*)
♦A DML operation on a view
♦The Database Administrator shuts down the database
9. A user’s schema contains procedure MYPROC, function MYFUNC, trigger MYTRIGG and package MYPACK which contains a public procedure PACKPROC. These subprograms have no parameters, and the function returns a NUMBER. Which of the following calls to these objects (from an anonymous block) are incorrect? (Choose two)
♦mypack.packproc;
♦mytrigg; (*)
♦myproc;
♦v_number := myfunc;
♦IF NOT myfunc THEN … (*)
10. Which of the following are good guidelines to follow when creating triggers? (Choose two)
♦Be aware of recursive and cascading effects (*)
♦Where possible, use triggers to enforce NOT NULL constraints
♦Avoid lengthy trigger logic by creating a procedure and invoking it from within the trigger (*)
♦Use triggers to replace functionality which is already built into the database
♦Always create more triggers than you need, because it is better to be safe
10.02. Creating DML Triggers Part I
AFTER DELETE ON departments
BEGIN
INSERT INTO audit_table (who, when)
VALUES (USER, SYSDATE);
COMMIT;
END;
♦You cannot use COMMIT inside a trigger. (*)
♦The last line of code should be END mytrigg;
♦The second line should be: AFTER DELETE OF DEPARTMENTS
♦Nothing is wrong, the trigger will execute successfully
2. Which of the following is the correct syntax for creating a DML trigger associated with the EMPLOYEES table? The trigger must fire whenever an employee’s JOB_ID is updated, but not if a different column is updated.
♦CREATE TRIGGER job_upd_trigg
AFTER UPDATE ON employees(job_id)
BEGIN …
♦CREATE TRIGGER job_upd_trigg
WHENEVER UPDATE OF job_id IN employees
BEGIN …
AFTER UPDATE ON employees.job_id
BEGIN …
AFTER UPDATE OF job_id ON employees
BEGIN …
3. We want to create a log record automatically every time any DML operation is executed on either or both of the EMPLOYEES and DEPARTMENTS tables. What is the smallest number of triggers that must be create to do this?
♦One
♦Two (*)
♦Three
♦Six
♦Eight
4. There are five employees in department 50. A statement trigger is created by:
AFTER DELETE ON EMPLOYEES
BEGIN …
DELETE FROM employees WHERE department_id = 50;
How many times will the trigger fire, and when?
♦Once, before the DELETE is executed
♦Five times, after each employee row is deleted
♦Once, after the DELETE is executed (*)
♦Six times, once after each row and once at the end of the statement
♦The trigger will not fire at all
5. Which of the following are possible keywords for the timing component of a trigger? (Choose three.)
♦BEFORE (*)
♦INSTEAD
♦WHENEVER
♦INSTEAD OF (*)
♦AFTER (*)
6. An AFTER UPDATE trigger can specify more than one column. True or False?
♦True (*)
♦False
7. A BEFORE statement trigger inserts a row into a logging table every time a user updates the salary column of the employees table. The user now tries to update the salaries of three employees with a single UPDATE statement, but the update fails because it violates a check constraint. How many rows will be inserted into the logging table?
♦None
♦One (*)
♦Three
♦Four
♦None of the above
8. We want to prevent employees from being deleted on Sundays. To do this, we create the following trigger:
……. DELETE ON employees — Line A
BEGIN
IF TO_CHAR(SYSDATE’,’DY’) = ‘SUN’ THEN
RAISE_APPLICATION_ERROR(-20101,’Invalid delete’);
END IF;
END;
Should this be a BEFORE or AFTER trigger, and why?
♦It should be a BEFORE trigger because if an AFTER trigger were created, the employee would already have been deleted by the time the trigger checks the date. (*)
♦It should be a BEFORE trigger because you cannot use RAISE_APPLICATION_ERROR with AFTER triggers.
♦It should be an AFTER trigger because the Oracle Server cannot fire the trigger until it knows that the employee has been deleted.
♦It does not matter, either a BEFORE or an AFTER trigger could be created.
10.03. Creating DML Triggers Part II
1. A row trigger has been created which is fired by UPDATE ON employees. A user now executes a single SQL statement which updates four rows of the EMPLOYEES table. How many times will the row trigger fire?
♦Once
♦Twice
♦Four times (*)
♦Five times
♦Eight times
2. Which of the following best describes conditional predicates in a trigger?
♦They are special variables which must be DECLAREd within the trigger.
♦They allow the trigger code to see what data values are being inserted into a row.
♦They are automatically declared boolean variables which allow the trigger body to detect which DML operation is being executed. (*)
♦They are special cursor attributes, like %ROWCOUNT and %NOTFOUND
3. The OLD and NEW qualifiers can be used with statement triggers as well as row triggers. True or False?
♦True
♦False (*)
4. You decide to create the following trigger:
BEFORE UPDATE ON employees
BEGIN
— Line A
RAISE_APPLICATION_ERROR(‘Cannot update salary’);
ELSE
INSERT INTO log_table values (USER, SYSDATE);
END IF;
END;
You want the trigger to prevent updates to the SALARY column, but allow updates to all other columns. What should you code at Line A?
♦IF UPDATING SALARY THEN
♦IF UPDATING(‘SALARY’) THEN (*)
♦IF UPDATE(‘SALARY’) THEN
♦IF UPDATING(SALARY) THEN
♦IF UPDATE(SALARY) THEN
5. Which of the following statements about INSTEAD OF triggers are NOT true? (Choose two.)
♦They can be created on a table. (*)
♦They can be created on a simple view.
♦They can be created on a complex view.
♦They can be statement triggers. (*)
♦They can be row triggers.
6. Whenever an employee’s JOB_ID is updated, we want to insert a row into a logging table to record the employee_id and the new value of JOB_ID. We create a row trigger whose body includes the following code:
INSERT INTO logging_table (emp_id, job_id)
VALUES — Point A
END;
At point A, which of the following will insert the correct data into the logging table? (Choose two.)
♦(:OLD.employee_id, :OLD.job_id);
♦(:OLD.employee_id, :NEW.job_id); (*)
♦(:NEW.employee_id, :OLD.job_id);
♦(:NEW.employee_id, :NEW.job_id); (*)
♦(NEW.employee_id, NEW.job_id);
7. Examine the following code. To create a row trigger, what code should be included at Line A?
BEFORE DELETE ON employees
—- Line A
BEGIN …
♦FOR EACH ROW (*)
♦FOR EVERY ROW
♦FOR ALL ROWS
♦Nothing is needed because DML triggers are row triggers by default.
8. The following view and trigger have been created:
CREATE OR REPLACE TRIGGER dept_view_trigg
INSTEAD OF UPDATE ON dept_view
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Sample Message’);
END;
WHERE department_id IN (50,80,81);
♦Two rows are updated and “Sample Message” is displayed once.
♦No rows are updated and “Sample Message” is displayed once.
♦No rows are updated and “Sample Message” is displayed twice. (*)
♦No rows are updated and “Sample Message” is displayed three times.
♦None of the above.
10.04. Creating DDL and Database Event Triggers
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
DECLARE
v_max_sal employees.salary%TYPE;
BEGIN
SELECT max(sal) INTO v_max_sal FROM employees;
END;
♦The trigger body is reading the same table (employees) that the triggering event is updating. (*)
♦You must use RAISE_APPLICATION_ERROR in a BEFORE trigger.
♦You can never use SELECT inside a DML trigger.
♦Nothing is wrong, the trigger will execute correctly.
2. User HARJIT wants to prevent any objects which he owns from being dropped. Harjit decides to execute the following code:
—- Line A
BEGIN
RAISE_APPLICATION_ERROR(-20201,’Attempted drop’);
END;
What should Harjit code at Line A?
♦BEFORE DROP ON TABLE
♦BEFORE DROP ON SCHEMA (*)
♦BEFORE DROP ON OWNER
♦BEFORE DROP ON USER_OBJECTS
3. You have been granted CREATE TRIGGER privilege. You can now create an AFTER LOGOFF ON SCHEMA trigger. True or False?
♦True
♦False (*)
4. The database administrator creates a trigger that automatically disconnects user HACKER whenever HACKER connects to the database. What type of trigger is this?
♦A DDL trigger
♦A Database Event trigger (*)
♦A DML trigger
♦A statement trigger
♦An INSTEAD OF trigger
5. The database administrator wants to write a log record every time any user’s session raises an ORA-00942 exception. The DBA decides to create the following trigger:
AFTER SERVERERROR ON DATABASE
BEGIN
— Line A
INSERT INTO log_table VALUES ( …);
END;
What should the DBA code at Line A?
♦IF (IS_SERVERERROR(942)) THEN (*)
♦IF (SERVERERROR = 942) THEN
♦IF (IS_SERVERERROR = 942) THEN
♦IF (IS_SERVERERROR(ORA-00942)) THEN
6. You can create a trigger which prevents DDL statements on an individual table, while still allowing DDL on other tables in the same schema. True or False?
♦True
♦False (*)
7. Mutating table errors can be caused by DML triggers, but not by database event triggers. True or False?
♦True (*)
♦False
8. Which of the following could NOT cause a DDL or Database Event trigger to fire?
♦A table is dropped.
♦A user connects to the database.
♦The DBA starts up the database.
♦A user deletes rows from the EMPLOYEES table. (*)
♦A specific exception is raised in a user’s session.
9. What is wrong with the following code?
AFTER UPDATE OR DELETE ON employees
BEGIN
CALL del_emp_proc
END;
♦The CALL statement should end with a semicolon (;)
♦You cannot use a CALL statement in a DML trigger.
♦When using CALL, only one DML statement can be tested, so UPDATE OR DELETE is wrong.
10.05. Managing Triggers
AFTER DELETE ON employees
BEGIN
…
END;
The user now tries to drop the EMPLOYEES table. What happens?
♦The table is dropped but the trigger is not dropped.
♦An error message is displayed because you cannot drop a table that is referenced by a trigger.
♦The table is dropped and the trigger is disabled.
♦Both the table and the trigger are dropped. (*)
2. Which command would you use to see if your triggers are enabled or disabled?
♦SELECT trigger_name, status
FROM USER_TRIGGERS;
(*)
♦SELECT object_name, status
FROM USER_OBJECTS
WHERE object_type = ‘TRIGGER’;
♦SELECT trigger_name, trigger_type
FROM USER_TRIGGERS;
DESCRIBE TRIGGER
3. By default, any user can create a DML trigger on a table in his/her schema. True or False?
True
False (*)
4. You have created several DML triggers which reference your DEPARTMENTS table. Now you want to disable all of them using a single SQL statement. Which command should you use?
♦ALTER TRIGGER DISABLE ALL ON departments;
♦ALTER TABLE departments DISABLE ALL TRIGGERS; (*)
♦ALTER TABLE departments DISABLE TRIGGERS;
♦DROP ALL TRIGGERS ON departments;
5. User KULJIT creates two triggers named EMP1_TRIGG and EMP2_TRIGG, which are both DML triggers referencing her EMPLOYEES table. Kuljit now wants to remove both of these triggers from the database. What command(s) should Kuljit use to do this?
♦DROP ALL TRIGGERS ON employees;
♦DROP TRIGGER emp2_trigg; (*)
♦DROP TRIGGER emp1_trigg AND emp2_trigg;
6. Which dictionary view would you query to see the detailed body code of triggers in your schema?
♦USER_SOURCE
♦USER_TRIGGER
♦USER_TRIGGERS (*)
♦USER_OBJECTS
♦None of the above, you cannot view the code of the trigger body after the trigger has been created.
InfoCad Blog 