In this tutorial, First, we would be discussing what is constraints and their types, and secondly, about the Oracle triggers and the usages before we could see the major difference between constraints and triggers.
In Oracle, the constraints are the rules that apply on the table's column to limit the data to enter into the table. We have various types of constraints that play different roles. The followings are the list of constraints used in the Oracle database.
- NOT NULL constraint - This constraint avoids null being entered into the database table
- Unique key (UK) constraint - This eliminates multiple rows of the same value in the same column or the group of columns with the same values of rows. The key point is that a unique constraint allows null values to be present in a table
- Primary key (PK) constraint - This PK constraint is the combination of NOT NULL and unique constraints in a single declaration. That is, it prevents multiple rows from having the same value in the same column or combination of columns and avoids null being entered into the database table. The table which has PK constraint is also called as Parent table.
- Foreign key(FK) constraint - This FK constraint applies in a table column and this table is called a Child table that will have values in one column to match values in another table column which basically a Parent table.
- Check constraint - This constraint ensures that the given conditions are met in a table.
- DEFAULT Constraint − It provides a default value for a column when no specific input.
The above constraints could be specified when a table is created or after the table is created. We could add them in either way, with CREATE TABLE statement - During the create table or ALTER TABLE statement - After the table created.
So far, you have understood the Oracle constraints and the following section will be explained for triggers.
A trigger is one of the Oracle objects that is stored in itself in the form of an Oracle PL/SQL program which is automatically executed or fired whenever the event happens in the database such as INSERT, UPDATE or DELETE, and so on.
The trigger works with the following events of the database,
- Database Definition Language (DDL) statement (CREATE, ALTER, or DROP)
- Database manipulation Language (DML) statement (DELETE, INSERT, or UPDATE)
- A database operation such as SERVER ERROR, LOGON, LOGOFF, STARTUP, SUSPEND, DB_ROLE_CHANGE or SHUTDOWN
Oracle database triggers can be defined at schema, table, view, or database level with the event is associated.
- Preventing invalid transactions occurs
- Auditing the sensitive data
- Event logging and storing information on the table
Major Difference Between Constraints and Triggers:
|It should be defined while creating the database object such as (CREATE TABLE) or after the creation (ALTER TABLE)||It should be defined in an Oracle PL/SQL block|
|The constraints affect all the rows that have been existed before and the ones added newly after the constraints are enabled||The Trigger affects only the rows which are added after the trigger is enabled.|
|We can not use constraints to implement the complex business rules as it is responsible only for maintaining the database integrity||Responsible for high-level business logic to implement as it is capable enough to handle the complicated business rules.|
|Support data integrity||Support data integrity but triggers can be imposed on many tables.|