Q&A

Q If I am using a trigger to enforce a business rule or a referential integrity rule, does this do anything about the records that predate creation of the trigger?

A No, it doesn't, and that's a good point to keep in mind. When you create a declarative constraint, you are really making a statement about the data that must always be true. You cannot create a constraint if data is present that violates that constraint. Triggers, on the other hand, affect only records that have been inserted, updated, or deleted after the trigger was created. For example, creating the triggers limiting an employee to only two-department assignments will do nothing about preexisting cases where an employee has more than two assignments.

Q The inserts in Listing 11.21 (lines 16_27) did not generate a mutating table error message, yet they did query the table. Why is this?

A Single-row inserts are an exception to the rule about querying the underlying table. However, if the insert is one that could possibly create more than one row, for example an INSERT INTO emp_dept SELECT..., the rule about not querying the mutating table still applies.

Q What's the difference between a statement-level trigger and a row-level trigger?

A A statement-level trigger is executed only once, either before or after the triggering SQL statement executes. It cannot refer to any values in the rows affected by the statement. A row-level trigger fires once for each row affected by the triggering SQL statement, and can reference the values for each of the rows.

Q Why should I generally validate business rules in a before trigger rather than an after trigger?

A It's potentially more efficient because you can prevent Oracle from doing the work involved in inserting, updating, or deleting a record. By validating in an after trigger, you are allowing Oracle to first update the table in question, update any indexes that might be affected by the change, and possibly fire off other triggers.

Q The triggers in Listing 11.6 maintain employee counts for each department as records are inserted into, updated in, and deleted from the emp_dept table. What happens, however, if a department record is deleted and then reinserted? Won't the employee count be reset to zero in that case, making it incorrect?

A Yes, this is absolutely true. Typically, in a production database, you would also have referential integrity constraints defined to prevent deletion of department records referenced by other tables.

Workshop

Use the following sections to test your comprehension of this chapter and put what you've learned into practice. You'll find the answers to the quiz and exercises in Appendix A, "Answers."

Quiz

1. Which data manipulation statements can support triggers?
2. What are the four basic parts of a trigger?
3. In a trigger, what are the correlation names :OLD and :NEW used for?
4. What is the name of the system view that can be used to retrieve trigger definitions?
5. What is a mutating table?
6. Name some possible uses for triggers.

Exercises

1. Write a set of triggers to maintain the emp_name and dept_name fields redundantly in the emp_dept relation, so that you do not have to join with the employee and department tables just to get a simple department listing.
2. Write the SQL statements necessary to populate the emp_name and dept_name fields for any existing emp_dept records.