What's the difference between a trigger and a constraint in Oracle?

by nave
(chn)

What's the difference between a trigger and a constraint in Oracle?


To a certain extent there is only a subtle difference between a constraint and a trigger in an Oracle database as they can both be used to achieve the same result if that result is just to ensure data integrity. But we're putting the cart before the horse so let's start with the the definitions.

A trigger in Oracle is a PL/SQL procedure that is executed when a pre-defined event occurs. This event is known as the triggering event. Whilst the syntax of the trigger itself is specific, the trigger is allowed to call any other PL/SQL procedure or function. Oracle PL/SQL triggers can be defined at various levels: database, schema, view and table. This means for example that if you define a trigger at the database level then the trigger will be executed (fired) whenever the triggering event occurs no matter what the schema, view or table affected by the event and some events such as "shutdown" obviously have no effect on a schema/table/view anyway. Therefore triggers defined at the database level should be relevant for the whole database, those defined at the schema level should be relevant for every object in the schema and those defined at the view or table level must be relevant to the table or view concerned.

The next question is what can you do with a trigger. And the answer almost anything you can think of. For example an Oracle pl/sql table trigger could be used to keep a long-term history of changes (updates, inserts and deletes) made to the table by copying the old data to a history table. A database trigger could be used to prevent the database being shutdown except when logged in at a specific location. Object views in Oracle have to have a specific type of trigger called an "instead-of" trigger which is used to replace the normal insert, update and delete statements and to define how objects should be inserted, updated or deleted.

Constraints on the other hand are very simple and have only one purpose really - to ensure data integrity in your Oracle database. Constraints can only be defined at the column or table level and there are are only 4 types - check constraints, primary key constraints, not null constraints and foreign key (referential integrity) constraints.

Constraints do what the name suggest - they constrain the values that any data may have when insert into the table/column with the constraint. A "NOT NULL" column constraint in Oracle means that whenever a row is inserted into the table, a value must be supplied for that column either explicitly or by defining a default value. Primary key constraints ensure that the values entered into the column(s) are unique and the values are not undefined (i.e. not null). Unique constraints, like primary key constraints, ensure that values inserted into the column(s) with the constraint are unique but NULL (undefined) values are allowed. Referential integrity (foreign key) constraints ensure that a value in one table exists in another table. For example in a HR database you might have a table for departments and another for employees, a referential integrity constraint on the department_id in the employee table to the department id in the department table would ensure that no employee could be assigned to a department that doesn't exist. Of course you could achieve the same thing with a trigger, but constraints are much more fundamental so they are more efficient for enforcing simple data integrity rules in your Oracle database. Check constraints a condition that the value of the column must meet, they can be used to ensure that values for the constrained column fall within a range, or do not meet a limit (high or low). They can't be used to compare a value with another column in the same table or another table - for that you would need to define a PL/SQL trigger on the table.

You might also be interested in the following questions and answers:

For more help, see our series of PL/SQL tutorials and our series of Oracle SQL tutorials. For more formal training either on-line or in person see our
Oracle training page.

Click here to post comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.