After row-level trigger v16
This example shows an after row-level trigger. When a new employee row is inserted, the trigger adds a 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.
Then, the trigger adds a row to the
empchglog table with a description of the action.
In the first sequence of the following commands, two employees are added using two separate
INSERT commands. Then both are updated using a single
UPDATE command. The contents of the
jobhist table show 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.
Then, a single
DELETE command deletes both employees. The
empchglog table shows the trigger was fired twice, once for each deleted employee.