Oracle PL/SQL Trigger

Yusuf SEZER October 11, 2020 ORACLE

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.

CREATE [OR REPLACE ] TRIGGER My_Trigger_Name
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF My_Column_Name]
ON My_Table_Name
 -- When you specify Referencing, then you can use this keyword in triggered sql statement. For Example->MyOld.DeletedRowId
[REFERENCING OLD AS MyOld NEW AS MyNew] 
[FOR EACH ROW]
WHEN (My_Condition)
DECLARE
   -- definitions
BEGIN
   -- commands
EXCEPTION
   -- exception handling
END;

CREATE [OR REPLACE ] TRIGGER My_Trigger_Name

{BEFORE | AFTER | INSTEAD OF }

{INSERT [OR] | UPDATE [OR] | DELETE}

[OF My_Column_Name]

ON My_Table_Name

-- When you specify Referencing, then you can use this keyword in triggered sql statement. For Example->MyOld.DeletedRowId

[REFERENCING OLD AS MyOld NEW AS MyNew]

[FOR EACH ROW]

WHEN (My_Condition)

DECLARE

-- definitions

BEGIN

-- commands

EXCEPTION

-- exception handling

END;

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.

CREATE OR REPLACE trigger TRIGGER_NEW_PRODUCT
BEFORE INSERT ON PRODUCTS
BEGIN
    DBMS_OUTPUT.put_line('New Product inserted.');
END;

CREATE OR REPLACE trigger TRIGGER_NEW_PRODUCT

BEFORE INSERT ON PRODUCTS

BEGIN

DBMS_OUTPUT.put_line('New Product inserted.');

END;

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.

CREATE OR REPLACE trigger TRIGGER_NEW_PRODUCT
BEFORE INSERT ON Products
BEGIN
    INSERT INTO INTO Log(table_name, log, user, date) VALUES('Products', 'Insert', USER, SYSDATE);
END;

CREATE OR REPLACE trigger TRIGGER_NEW_PRODUCT

BEFORE INSERT ON Products

BEGIN

INSERT INTO INTO Log(table_name, log, user, date) VALUES('Products', 'Insert', USER, SYSDATE);

END;

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.

ALTER TRIGGER trigger_name DISABLE;

1	ALTER TRIGGER trigger_name DISABLE;

Enable Trigger in Oracle PL/SQL

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

ALTER TRIGGER trigger_name ENABLE;

1	ALTER TRIGGER trigger_name ENABLE;

List Triggers in Oracle PL/SQL

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

SELECT trigger_name, status FROM SYS.user_triggers;

1	SELECT trigger_name, status FROM SYS.user_triggers;

Drop Trigger in Oracle PL/SQL

The DROP keyword is used to delete the trigger.

DROP TRIGGER trigger_name;

1	DROP TRIGGER trigger_name;

Enable ALL Triggers on Table Oracle PL/SQL

ALTER TABLE table_name ENABLE ALL TRIGGERS;

1	ALTER TABLE table_name ENABLE ALL TRIGGERS;

Disable ALL Triggers in Table Oracle PL/SQL

ALTER TABLE table_name DISABLE ALL TRIGGERS;

1	ALTER TABLE table_name DISABLE ALL TRIGGERS;

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.

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

Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux

About Yusuf SEZER

Leave a Reply Cancel reply