How To Create Trigger In Oracle With Example

Learn all about Triggers in PL SQL, their types, usage and advantages:

In thePL SQL Transactions tutorial of the PL/SQL series, we have learned about COMMIT, ROLLBACK, and SAVEPOINTS statements.

In this article, we will explore triggers in PL SQL and their advantages, types, and usage. We will discuss how to create, trigger, enable, and disable PL/SQL Triggers with the help of example programs.

Let's begin with the discussion!!

Triggers in PL SQL

What You Will Learn:

Triggers In PL/SQL
- Advantages Of Triggers
- Usages Of Triggers
- Types Of Triggers In PL/SQL
- Create Triggers
- Triggering Triggers In PL SQL
- NEW And OLD Clause
- Disable And Enable Trigger
- Frequently Asked Questions And Answers
Conclusion
- Recommended Reading

Triggers In PL/SQL

A stored program that is fired by default or by some events is called a trigger.

A trigger is executed due to the following circumstances listed below:

By a DDL (Data Definition Language) statement like DROP, ALTER, or CREATE.
By a DML (Data Manipulation Language) statement like UPDATE, INSERT, or DELETE.
By a database action like SHUTDOWN, STARTUP, LOGOFF, and LOGON.

A trigger can be set on a schema, view, or database that has an event attached.

Advantages Of Triggers

These are listed below:

Ability to enforce referential integrity.
Ability of monitoring.
Ability to log and hold data on accessing tables.
Ability to stop transactions which are not valid.
Ability to enforce security features.
Ability to produce derived column values by default.

Usages Of Triggers

These are listed below:

Prevents improper transactions.
Accumulates information on table usage.
Monitor critical information.

Types Of Triggers In PL/SQL

The triggers can be categorized based on parameters.Types of triggers are listed below:

#1) Categorization on the trigger level.

ROW Level trigger: It gets executed for each record that got updated by a DML statement.
STATEMENT Level trigger: It gets executed only once by the event statement.

#2) Categorization on the trigger timing.

BEFORE trigger: It gets executed prior to the specific event that has taken place.
AFTER trigger: It gets executed post the specific event that has taken place.
INSTEAD OF trigger: It is a special type of trigger and it gets executed for each record that got updated by a DML statement.

#3) Categorization of the trigger event.

DML trigger: It gets executed if a DML event like an UPDATE, INSERT or DELETE is performed.
DDL trigger: It gets executed if a DDL event like a DROP, ALTER, or CREATE is performed.
DATABASE trigger: It gets executed if a database event like SHUTDOWN, STARTUP, LOGOFF, and LOGON has taken place.

Create Triggers

Syntax for creating a trigger:

CREATE [OR REPLACE ] TRIGGER trigger_n   {BEFORE | AFTER | INSTEAD OF }   {INSERT [OR] | UPDATE [OR] | DELETE}   [OF column_n]   ON table_n   [REFERENCING OLD AS o NEW AS n]   [FOR EACH ROW]   WHEN (condition)    DECLARE      << Declaration statement >> BEGIN      << Block of executable code>> EXCEPTION     << Exception handling if any >>      END;

Here,

CREATE [OR REPLACE] TRIGGER trigger_n – This is for creating, replacing, or updating a trigger having a name as trigger_n.
{BEFORE | AFTER | INSTEAD OF} – This is for determining the time when the trigger will be fired. The INSTEAD OF is for creating a trigger that has a view.
{INSERT [OR] | UPDATE [OR] | DELETE} – This is for executing the DML actions.
[OF column_n] – This is for mentioning the column name that shall be modified.
[ON table_n] – This is for mentioning the table name that is attached to the trigger.
[REFERENCING OLD AS o NEW AS n] – This is for referring to the old and new values by the DML statement like UPDATE, INSERT or DELETE.
[FOR EACH ROW] – This determines a row-level trigger, i.e., the trigger will be fired for each row that is modified, else the trigger will fire just once when the SQL statement is executed, which is known as table level trigger.
WHEN (condition) – This gives a condition for rows for which the trigger would be executed. This is applicable for only row-level triggers.

Let us again consider a table which is named STUDENT.

Let us now create a row-level trigger for the STUDENT table that would get executed by the DML statement like UPDATE, INSERT or DELETE on that table. The trigger will compute and show the age difference between current and previous values.

Code Implementation for trigger creation:

CREATE OR REPLACE TRIGGER age_changes BEFORE DELETE OR INSERT OR UPDATE ON student  FOR EACH ROW  WHEN (NEW.CODE > 0)  DECLARE     age_diff number;  BEGIN     age_diff := :NEW.age  - :OLD.age;     dbms_output.put_line ('Prevoius age: ' || : OLD.age);     dbms_output.put_line ('Current age: ' || : NEW.age);     dbms_output.put_line ('Age difference: ' || age_diff);  END;  /

Output of the above code:

Create Triggers

The above code has some important characteristics. They are listed below:

For a table level trigger, OLD and NEW addresses are not available. We can use these references for record level triggers.
If we want to apply another query in the same trigger, then we need to use the keyword AFTER as a trigger which can modify a table again only after the previous modifications are properly applied.
The trigger discussed above is getting executed prior to any DELETE, UPDATE, or INSERT action on the table. However, we can also design a trigger that gets fired on a single operation as well (for example, AFTER INSERT that will execute the trigger whenever a record is inserted with the help of the INSERT operation on the table).

Triggering Triggers In PL SQL

To fire the above trigger, we need to do any DML operation like DELETE, INSERT or UPDATE on the table. Let us again insert some values in the Student table with the help of the below query:

INSERT INTO STUDENT VALUES (4, 'MARY', 16, 97);

Once the INSERT operation is completed in the Student table, the trigger age_changes gets executed.

The output of the query code:

Triggering Triggers

Since a new record is created and the previous age is unavailable, the previous age and Age difference computation comes as null in the above output.

Now, let us modify a record with UPDATE statement with the help of the below query:

UPDATE student SET age = age + 1 WHERE code = 7;

Once the UPDATE operation is completed in the Student table, the trigger age_changes gets executed.

The output of the above query:

modify a record with UPDATE statement

NEW And OLD Clause

A new clause is used to store the new value for the columns of the table for the trigger execution. It is used in record level triggers. An old clause is used to store the old value for the columns of the table for the trigger execution. It is also used in record level triggers.

Thus the new and old clause is used to hold and refer to the new and old values within a trigger body.

Compound Trigger

A compound trigger is used to define the operations for all the timing points within the trigger body. It gives the provision to merge all the actions at different timings within one trigger body.

The various timing points are listed below:

AFTER STATEMENT level
BEFORE ROW level
AFTER ROW level
BEFORE STATEMENT level

Disable And Enable Trigger

We can enable and disable a trigger with the help of a DDL statement ALTER.

Syntax:

ALTER TRIGGER trigger_n [ENABLE|DISABLE];

Here, trigger_n is the name of the trigger that we want to enable or disable.

Now let us disable the trigger with the help of the below query:

ALTER TRIGGER STUDENT_DETAILS DISABLE;

The output of the query code:

Trigger altered

Now let us enable the same trigger with the help of the below query:

ALTER TRIGGER STUDENT_DETAILS ENABLE;

The output of the query code:

Enable Trigger

Frequently Asked Questions And Answers

Q #1) How many types of triggers exist in PL SQL?

Answer: There are two types of triggers in PL/SQL. They are Row-level trigger and Statement-level trigger.

Q #2) Which type of trigger uses the old and new qualifiers?

Answer: The old and new qualifiers can be used only with row-level triggers. They are not compatible with statement-level triggers.

Q #3) What is the difference between trigger new and trigger old?

Answer: A new trigger gives a list of new versions of the record objects. An old trigger gives a list of old versions of the record objects.

Q #4) What is a row-level trigger in PL SQL?

Answer: A row-level trigger gets triggered for a row only once due to an event. For example, if deletion is considered as a triggering event for a table, a delete statement is affecting two rows, then the trigger shall be fired two times.

Q #5) What is a trigger? Explain with an example.

Answer: A trigger is a stored procedure that gets fired by default if an incident occurs in the database. For example, if a row is inserted into a table or we are modifying some values in the table.

Q #6) What are the after triggers?

Answer: The after triggers are fired post the execution of a DML statement but prior to the commit to the database. It is also capable of rolling back its action.

Conclusion

In this tutorial, we have discussed some basic concepts of Triggers in PL SQL that are essential to use them while programming. We have covered the following topics listed below:

Triggers.
Types of Triggers.
Various operations on triggers.

<< PREV Tutorial | NEXT Tutorial>>