Examples on Triggers
Instructor: Mohamed Eltabakh
meltabakh@cs.wpi.edu
1
Example 1
If the employee salary increased by more than 10%, make sure the
‘rank’ field is not empty and its value has changed, otherwise reject the
update
If the trigger exists, then drop it first
Create or Replace Trigger EmpSal
Before Update On Employee
For Each Row Compare the old and new salaries
Begin
IF (:new.salary > (:old.salary * 1.1)) Then
IF (:new.rank is null or :new.rank = :old.rank) Then
RAISE_APPLICATION_ERROR(-20004, 'rank field not correct');
End IF;
End IF;
End;
/ Make sure to have the “/” to run the command
2
Example 2
If the employee salary increased by more than 10%, then increment the
rank field by 1.
In the case of Update event only, we can specify which columns
Create or Replace Trigger EmpSal
Before Update Of salary On Employee
For Each Row
Begin
IF (:new.salary > (:old.salary * 1.1)) Then
:new.rank := :old.rank + 1;
End IF;
We changed the new value of rank field
End;
/
The assignment operator has “:”
3
Example 3: Using Temp Variable
If the newly inserted record in employee has null hireDate field, fill it in
with the current date
Since we need to change values, then it must
Create Trigger EmpDate
be “Before” event
Before Insert On Employee
For Each Row
Declare Declare section to define variables
temp date;
Begin
Select sysdate into temp from dual; Oracle way to select the current date
IF (:new.hireDate is null) Then
:new.hireDate := temp;
End IF; Updating the new value of
End; hireDate before inserting it
/
4
Example 4: Maintenance of
Derived Attributes
Keep the bonus attribute in Employee table always 3% of the salary
attribute
Create Trigger EmpBonus Indicate two events at the
Before Insert Or Update On Employee same time
For Each Row
Begin
:new.bonus := :new.salary * 0.03;
End; The bonus value is always
/ computed automatically
5
Combining Multiple Events in
One Trigger
l If you combine multiple operations
l Sometimes you need to know what is the current operation
Create Trigger EmpBonus Combine Insert and Update
Before Insert Or Update On Employee
For Each Row
Begin
IF (inserting) Then … End IF;
IF (updating) Then … End IF;
End; Can do something different under
/
each operation
Before vs. After
l Before Event
l When checking certain conditions that may cause the operation to be cancelled
l E.g., if the name is null, do not insert
l When modifying values before the operation
l E.g., if the date is null, put the current date
l After Event
l When taking other actions that will not affect the current operations
l The insert in table X will cause an update in table Y
Before Insert Trigger:
:new.x := …. //Changing value x that will be inserted
After Insert Trigger:
:new.x := … //meaningless because the value is already inserted
Row-Level vs. Statement-Level
Triggers
l Example: Update emp set salary = 1.1 * salary;
l Changes many rows (records)
l Row-level triggers
l Check individual values and can update them
l Have access to :new and :old vectors
l Statement-level triggers
l Do not have access to :new or :old vectors (only for row-level)
l Execute once for the entire statement regardless how many records are
affected
l Used for verification before or after the statement
8
Example 5: Statement-level
Trigger
Store the count of employees having salary > 100,000 in table R
Indicate three events at the
same time
Create Trigger EmpBonus
After Insert Or Update of salary Or Delete On Employee
For Each Statement Remember: In Oracle,
Begin it is not written
delete from R;
insert into R(cnt) Select count(*) from employee where salary > 100,000;
End;
/
Delete the existing record in R, and
then insert the new count.
9
Order Of Trigger Firing
Loop over each affected record
Before Trigger Event After Trigger
Before Trigger (row-
After Trigger
(statement-level) (row-level) (row-level) (statement-level)
level)
10
Some Other Operations
l Dropping Trigger
SQL> Drop Trigger <trigger name>;
l If creating trigger with errors
SQL > Show errors;
It displays the compilation errors
11
Example on Triggers
Sum of loans taken by a customer does not exceed 100,000…
Assume primary keys cannot be updated
l Which table ? è Borrower & Loan
l Which event ? è Borrower (Insert) & Loan (update)
l Which Timing ? è ??? Lets see
l Which Granularity ? è row-level
12
Part 1
Sum of loans taken by a customer
does not exceed 100,000
Create Trigger CustMaxLoan1
After Insert On Borrower
For Each Row
Declare Takes into account the new loan assigned to
sumLoan int; the customer (because it is “After Insert”
Begin
Select sum(amount) into sumLoan
From loan L, Borrower B
where L.loan_number = B.loan_number
And B.customer_name = :new.customer_name;
IF sumLoan > 100,000 Then
RAISE_APPLICATION_ERROR(-20004, ’Cannot insert record.');
End IF;
End;
/
13
Part 2
Sum of loans taken by a customer
does not exceed 100,000
Create Trigger CustMaxLoan2
After Update of amount On Loan
For Each Row
Declare Get the customer to whom the updated loan
sumLoan int; custName varchar2(100); belongs
Begin
Select customer_name into custName From Borrower
Where loan_number = :new.loan_number;
Select sum(amount) into sumLoan Get the sum of loans for this customer
From loan L, Borrower B
where L.loan_number = B.loan_number
And B.customer_name = custName;
IF sumLoan > 100,000 Then
RAISE_APPLICATION_ERROR(-20004, ’Cannot insert record.');
End IF; 14
End;
Example 2
What if you are requested to
enforce this part ???
Sum of loans taken by a customer does not exceed 100,000…
Assume primary keys cannot be updated
For each table, create “Before Update” trigger preventing a change on PK columns
Create Trigger PK-No-Update
Before Update of loan_number On Loan
For Each Row
Begin
RAISE_APPLICATION_ERROR(-20004, 'Cannot Update PK…');
End;
/
15
