Both tables and databases can have triggers.
Table triggers are mechanisms that run before or after insert, update or delete operations.

Example: When there is a link between 2 databases, in a situation where one had an insert on table X a trigger can be used so that the same data can automatically be inserted on table Y (insert trigger – before insert, after insert).

A trigger can also be used in a situations such as preventing making changes outside of working hours, by configuring the trigger to check the working hours.

Types of triggers

Statement Level Trigger: Triggered whenever a particular statement is run. (example: blocking inserts during specified hours).

Row Level Trigger: Works on every row in a table. Used in scenarios where changes made are to be mirrored in another location.

Instead of Trigger: Can update xx fields, while using a view. Doesn’t have a wide variety of usage.

Example: To prevent delete/update/insert operations to the employees table outside of working hours

To disable the trigger:

Row Level Triggers

for each row: applies to every row in the table

Example: when updating the salary, it shouldn’t be below the average

As this codeblock works on a row level, it’s unable to find the average salary

The log table

Create a table

Create a sequence

Create a trigger

To check

The ‘new’ and ‘old’ keywords can only be used for each row, i.e. on a row level and not on a statement level.

The best way to add a field to an Auto increment is by using both a sequence and a trigger.

Example: To log the users and dates of transactions.

Create a procedure

Create a trigger


Note: If the previous 3 lines of code are placed between a begin and an end, it won’t be called.

To check

In newer DBs, fields that used to store data in long type, now store them as CLOBs (character large objects).

The Long data type can hold upto 2GBs of data, whereas CLOB can hold upto 4GB. Another advantage of using CLOB over Long is that is isn’t possibie to search for text within a Long field type.


This previous query returns a result for textbody2 but gives an error for textbody1.

type… table

Declared as ‘index by table’. Choosing between Binary integer or PLS_integer, PLS is preferred as it is faster.

Checking to see whether there are 6 employees: