Table of Contents Previous Next


5 Triggers : 5.6 Trigger Examples

The following INSERT is constructed so that several new rows are inserted upon a single execution of the command. For each row that has an employee id between 7900 and 7999, a new row is inserted with an employee id incremented by 1000. The following are the results of executing the command when three new rows are inserted.
The message, New employees are about to be added, is displayed once by the firing of the trigger even though the result is the addition of three new rows.
The following is an example of an after statement-level trigger. Whenever an insert, update, or delete operation occurs on the emp table, a row is added to the empauditlog table recording the date, user, and action.
In the following sequence of commands, two rows are inserted into the emp table using two INSERT commands. The sal and comm columns of both rows are updated with one UPDATE command. Finally, both rows are deleted with one DELETE command.
The contents of the empauditlog table show how many times the trigger was fired - once each for the two inserts, once for the update (even though two rows were changed) and once for the deletion (even though two rows were deleted).
The following example is an after row-level trigger. When a new employee row is inserted, the trigger adds a new row to the jobhist table for that employee. When an existing employee is updated, the trigger sets the enddate column of the latest jobhist row (assumed to be the one with a null enddate) to the current date and inserts a new jobhist row with the employee’s new information.
Finally, trigger adds a row to the empchglog table with a description of the action.
In the first sequence of commands shown below, two employees are added using two separate INSERT commands and then both are updated using a single UPDATE command. The contents of the jobhist table shows the action of the trigger for each affected row - two new hire entries for the two new employees and two changed commission records for the updated commissions on the two employees. The empchglog table also shows the trigger was fired a total of four times, once for each action on the two rows.
Finally, both employees are deleted with a single DELETE command. The empchglog table now shows the trigger was fired twice, once for each deleted employee.

5 Triggers : 5.6 Trigger Examples

Table of Contents Previous Next