Oracle PL/SQL Trigger

This article contains information about Oracle PL/SQL Trigger, Types such as DML,DDL and Datase Triggers and examples.

What is Trigger in Oracle PL/SQL?

The structure used to run a specific command after or before various operations on the database is called a trigger.

Triggers have the same structure as the procedures in PL / SQL.

The most important feature that distinguishes the triggers from the procedures is that the triggers run automatically after an event.

For example after or before an insert. The keywords to be used when creating a trigger are as follows.

Trigger Types in Oracle PL/SQL

Triggers are operated in the following cases.

DML Triggers– INSERT, UPDATE, DELETE

DDL Triggers– CREATE, ALTER, DROP

Database Triggers – SERVERERROR, LOGON, LOGOFF, STARTUP ve SHUTDOWN

DML Triggers in Oracle PL/SQL

It is often used to record transactions in DML operations.

Before Insert Trigger in Oracle PL/SQL

In the example below, it will print “New Product inserted.” on the screen before the record is inserterd to the Products table. Because this is before insert trigger.

If we use AFTER instead of BEFORE as a keyword, then it will print “New Product inserted.” on the screen after the record is inserterd to the Products table.

Also there are triggers like BEFORE INSERT TRIGGER as below.

  • Before Delete Trigger
  • Before Update Trigger
  • After Insert Trigger
  • After Delete Trigger
  • After Update Trigger

The transaction can be recorded by adding a record to another table within the BEGIN and END block.

It will be sufficient to add a record to the Products table to run the trigger.

Disable Trigger in Oracle PL/SQL

The ALTER and DISABLE keywords are used to disable the trigger.

Enable Trigger in Oracle PL/SQL

The ALTER and ENABLE keywords are used to enable the trigger.

List Triggers in Oracle PL/SQL

SYS.user_triggers table can be used to get information about triggers.

Drop Trigger in Oracle PL/SQL

The DROP keyword is used to delete the trigger.

Enable ALL Triggers on Table Oracle PL/SQL

Disable ALL Triggers in Table Oracle PL/SQL

Note: Using too many triggers can cause a bottleneck on the database server.

You can find more detailed information about below topics in the below link.

PL/SQL Tutorial

You will find below topics in this article.

  1. What is PL/SQL
  2. Oracle PL/SQL Data Types and Variables and Literals
  3. Oracle PL/SQL Operators
  4. Oracle PL/SQL Conditional Statements
  5. Oracle PL/SQL Loops
  6. Oracle PL/SQL Procedures and Procedure Parameters
  7. Oracle PL/SQL Functions
  8. Oracle PL/SQL Cursor
  9. Oracle PL/SQL Records
  10. Oracle PL/SQL Exception
  11. Oracle PL/SQL Trigger
  12. Oracle PL/SQL Packages
  13. Oracle PL/SQL Collections

You can find more information about triggers at docs.oracle.com

Yusuf SEZER
Author: Yusuf SEZER

Leave a Reply

Your email address will not be published. Required fields are marked *