After Row-Level Trigger v12
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.