Chapter 16, Problems
Triggers
A trigger is a special PL/SQL procedure that fires, or executes, in response to a specific triggering event. For example, you might write a trigger to enforce a business rule on INSERT statements on a particular table, maintain referential integrity in a distributed database, or track user logons. A trigger has three parts:
- A header line that defines the triggering event
- An optional WHEN clause that restricts the trigger's firing to a specific condition (for example, the trigger fires only when salary is greater than $50,000)
- The actual trigger code itself
This chapter tests your ability to (among other things) define triggers for a variety of events, work with correlation variables (special pseudo-column names that represent things like the old and new values of a column), and use triggers to implement business logic.
Beginner
- What is a trigger?
- For which of the following events can you create a trigger?
- An INSERT, UPDATE, or DELETE statement on a specific object
- An execution of a specific procedure
- A user logon (or logoff)
- A DDL statement, such as DROP or ALTER, on a specific object
- What is one of the main differences between the execution of a trigger and the execution of a stored procedure?
- What are the two modes a trigger can have?
- Which of the following terms describes the situation in which the execution of one trigger results in the execution of another, or possibly more, different triggers?
- Trigger torrent
- Cascading triggers
- Chain reaction
- Interlock
- Recursive nesting
- What is the difference between a statement-level trigger and a row-level trigger?
- What clause makes a trigger fire only when a specific condition is true?
- Which of the following triggers populates the employee_id column of the employee table with the next employee_seq sequence value?
CREATE OR REPLACE TRIGGER employee_ins_t1
BEFORE INSERT
ON employee
FOR EACH ROW
BEGIN
INSERT INTO employee (employee_id)
VALUES (employee_seq.nextval);
END;
CREATE OR REPLACE TRIGGER employee_ins_t1
BEFORE INSERT
ON employee
FOR EACH ROW
BEGIN
SELECT employee_seq.nextval
INTO :new.employee_id
FROM dual;
END;
- Which of these special "pseudo" records are allowed inside a trigger?
- :NEW
- :CURRENT
- :OLDEST
- :PARENT
- :OLD
- :NEWEST
- :RECORD
- What system privileges are required to create a trigger?
- What is the difference between the ALTER ANY TRIGGER privilege and the CREATE ANY TRIGGER privilege?
- Why does the following trigger generate a "ORA-00920: invalid relational operator" error?
CREATE OR REPLACE TRIGGER emp_before_ins_t
BEFORE INSERT
ON employee
FOR EACH ROW
WHEN (:NEW.mgr is null)
BEGIN
IF (:NEW.sal > 800)
THEN
:NEW.sal := 850;
END IF;
END;
- How can you view a trigger's compilation errors?
- How many different ways can you recompile a trigger?
- Why might you want to omit the OR REPLACE clause when you first create a trigger?
- Which of the following statements are correct, and which are incorrect?
- A user can create a trigger in any database schema if she has the CREATE ANY TRIGGER privilege.
- A user can create a trigger in any database schema (with the exception of SYS) if she has the CREATE ANY TRIGGER privilege.
- Triggers can trap only DML events such as INSERT, DELETE, and UPDATE.
- True or false? You can define a trigger for:
- Any schema-level object
- A table
- A view
- Any nested table
- The entire database or a user schema
- Examine the following trigger:
CREATE OR REPLACE TRIGGER upd_employee_commision
FOR EACH ROW
BEGIN
<<Trigger logic>>
END;
Which of the following statements must you add to the trigger definition to make sure this trigger executes only after updating the comm column of the emp table?
- AFTER UPDATE(comm) ON emp
- AFTER UPDATE ON emp
- AFTER UPDATE OF comm ON emp
- AFTER comm UPDATE ON emp
- Examine the following trigger:
CREATE OR REPLACE TRIGGER insert_employee
AFTER INSERT ON emp
BEGIN
<<Trigger logic>>
END;
Which of the following statements must you add to the trigger definition to make sure it executes only once for each INSERT operation on the emp table?
- FOR EVERY ROW
- WHEN (new.sal IS NULL)
- FOR EACH ROW
- No modifications are necessary
- Why does the following trigger fail when it's executed?
CREATE OR REPLACE TRIGGER ins_emp_summary
AFTER INSERT
ON emp
BEGIN
INSERT INTO emp_summary (empno, period, ytd_salary)
VALUES (:new.empno, SYSDATE, :new.sal);
END;
-
What is a mutating table?
Intermediate
- What happens when the following trigger executes?
CREATE OR REPLACE TRIGGER employee_ins_t1
BEFORE UPDATE
ON employee
FOR EACH ROW
DECLARE
cur PLS_INTEGER := DBMS_SQL.open_cursor;
fdbk PLS_INTEGER;
stmt VARCHAR2(2000);
BEGIN
stmt := 'BEGIN IF :old.' ||
emp_pkg.col_name ||
'= ' ||
emp_pkg.col_value ||
'''' ||
' THEN :new.salary := :new.salary * 2; ' ||
' END IF;' ||
' END; ';
DBMS_SQL.parse (cur, sql_stmt, DBMS_SQL.native);
fdbk := DBMS_SQL.execute (cur);
END;
- Provide a template for a trigger that raises an error when a client application violates a business rule (e.g., a trigger that raises an error if a user attempts to delete a row from the employee table).
- You want to issue DML statements or execute PL/SQL stored programs inside a trigger. Which of the following describes how you must grant the necessary privileges on the underlying object?
- Privileges on the underlying object must be granted through the database roles.
- Privileges on the underlying object must be granted directly from the user who owns the object.
- Privileges can be granted either through database roles or directly from the user who owns the object.
- Indicate whether the following triggers are valid or invalid (a valid procedure both compiles and executes without error):
CREATE OR REPLACE TRIGGER emp_audit_trg
BEFORE INSERT OR UPDATE
ON employee
FOR EACH ROW
BEGIN
IF (inserting)
THEN
INSERT INTO employee_audit
VALUES (:new.empno, USER, 'Inserting a row into table_a');
ELSE
INSERT INTO employee_audit
VALUES (:new.empno, USER, 'Updating a row in table_a');
END IF;
COMMIT;
END;
CREATE OR REPLACE TRIGGER format_table_trig
AFTER INSERT
ON format_table
FOR EACH ROW
WHEN (new.tablecode = 3334)
DECLARE
seq_sql VARCHAR(200);
cursor_handle INTEGER;
execute_ddl INTEGER;
BEGIN
seq_sql := 'CREATE SEQUENCE ' ||
SUBSTR (:new.table_id, 1, 21) ||
'_SEQ START WITH 0 INCREMENT BY 1 MINVALUE 0';
cursor_handle := DBMS_SQL.open_cursor;
DBMS_SQL.parse (cursor_handle, seq_sql, DBMS_SQL.native);
execute_ddl := DBMS_SQL.execute (cursor_handle);
DBMS_SQL.close_cursor (cursor_handle);
END;
CREATE OR REPLACE TRIGGER set_scott_on_logon
AFTER logon
ON SCHEMA
DECLARE
seq_sql VARCHAR(200) := 'alter package emp_pkg compile';
cursor_handle INTEGER := DBMS_SQL.open_cursor;
execute_ddl INTEGER;
BEGIN
DBMS_SQL.parse (cursor_handle, seq_sql, DBMS_SQL.native);
execute_ddl := DBMS_SQL.execute (cursor_handle);
DBMS_SQL.close_cursor (cursor_handle);
END;
- DDL and transaction control statements such as ROLLBACK, COMMIT, and SAVEPOINT are not allowed in the body of a trigger. Can you circumvent this restriction by calling a stored procedure, which does contain the statement, in the trigger's body?
- Sometimes, depending on the context of the application being constructed, you need to implement logic that requires explicit (or implicit) transaction control. For example, suppose you want to create a sequence when a user inserts a row into a table. How would you perform this function in Oracle8i? In previous versions of Oracle?
- True or false (note that number 32 really is a magical number for triggers!)?
- The trigger body cannot contain more than 32 lines of PL/SQL code.
- The size of a trigger cannot be more than 32K.
- Oracle allows up to 32 triggers to cascade at any one time.
- LONG or LONG RAW column can be referenced in a SQL statement within a trigger only if they can be converted into a constrained datatype. The maximum length for these datatypes can be up to 32K.
- Sometimes you need to enable or disable triggers when you perform certain tasks, such as loading data or reorganizing a table. Write a script that enables or disables all triggers for the user who runs it.
Expert
- If you create several triggers of the same type for the same table, in what order do the triggers fire?
- Optimize the performance of the following trigger and explain how this technique can minimize the number of times the trigger fires:
CREATE OR REPLACE TRIGGER employee_ins_t1
AFTER UPDATE OR DELETE OR INSERT
ON employee
FOR EACH ROW
BEGIN
IF (UPDATING) THEN
IF :old.sal <> :new.sal THEN
Employee_pkg.update_emp (:new.employee_id, :new.sal);
END IF;
END IF;
END;
- An HR system has an employee table that holds a row for each employee within the company. Each record in the table has a manager field, (mgr), that holds the id for the employee's manager. Write a trigger so that when a manager record is deleted, the mgr field of that manager's employees is set to NULL. In other words, implement the following SQL statement:
WHEN AN EMPLOYEE IS DELETED,
UPDATE employee SET
mgr = null
WHERE
mgr = employee id of the deleted employee
- Due to a runtime error, an AFTER LOGON trigger in your database has become invalid. As a consequence, all users receive the following error when trying to connect to the database:
ORA-04098: trigger 'ON_LOGON' is invalid and failed re-validation
How can you fix the problem?
- What are the possible implications of using the pseudo-column names :OLD and :NEW in the following trigger, which uses autonomous transactions
?
/* Filename on web page: trigauto.sql */
CREATE OR REPLACE TRIGGER ins_emp
AFTER INSERT
ON emp
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
vsal NUMBER;
BEGIN
SELECT SUM (sal)
INTO vsal
FROM emp e
WHERE e.deptno = :new.deptno;
BEGIN
INSERT INTO dept_history
VALUES (:new.deptno, vsal);
EXCEPTION
WHEN DUP_VAL_ON_INDEX
THEN
UPDATE dept_history
SET sal = vsal
WHERE deptno = :new.deptno;
END;
COMMIT;
END;
- Suppose that the procedures called by the following triggers are recompiled. What happens at the next execution of each trigger?
CREATE OR REPLACE TRIGGER loc_proc_trigger
BEFORE UPDATE
ON bonus
FOR EACH ROW
BEGIN
update_bonus;
END;
/
CREATE OR REPLACE TRIGGER rem_proc_trigger
BEFORE UPDATE
ON bonus
FOR EACH ROW
BEGIN
update_bonus@rdb;
END;
/
