Provides you with an in-depth understanding of Oracle triggers

In Oracle databases, Oracle triggers are automatically executed to respond to database changes. The following describes how to use Oracle triggers for your reference.

We can set it to trigger or execute before or after a trigger event. Events that trigger events include the following:

DML events
DDL events
Database events

DML event triggers can be statements or row-level triggers. The DML statement trigger triggers the DML row-level trigger before or after the statement changes. You can define multiple triggers for a single event and type, but there is no way to enhance the multi-trigger command. The following table lists trigger events that can be used by users:

Event trigger description
INSERT trigger when inserting a row into a table or view
Trigger triggered when updating a row in a table or view
Trigger triggered when DELETE deletes a row from a table or view
CREATE triggers when an object is added to a database or project using the CREATE statement.
ALTER triggers when an ALTER statement is used to change an object of a database or project.
DROP triggers when the DROP statement is used to delete objects of a database or project.
START triggers when the database is opened, and triggers after the event
SHUTDOWN is triggered when the database is shut down. It is triggered before the event.
LOGON is triggered when a session is established and before the event
LOGOFF is triggered when the session is closed and before the event
Trigger when SERVER errors occur and trigger after the events

The syntax for creating a trigger is as follows:

 
 

  
  
CREATE [OR REPLACE] TRIGGER trigger_name  
  
  
{before|after|instead of} event  
  
  
ON {table_or_view_name|DATABASE}  
  
  
[FOR EACH ROW[WHEN condition]]  
  
  
trigger_body  
 
 

Only DML trigger INSERT, UPDATE, DELETE) statements can use the instead of trigger, and only the table's DML trigger can be BEFORE or AFTER trigger.

Like a constraint, triggers can be set to disabled or enabled to close or open their EXECUTE bodies (EXECUTE), and the triggers can be set to disabled or enabled using the alter trigger statement:

 
 

  
  
ALTER TRIGGER trigger_name ENABLE;  
  
  
ALTER TRIGGER trigger_name DISABLE;  
 
 

To disable or enable all triggers for a TABLE, use the alter table statement.

 
 

  
  
ALTER TABLE table_name DISABLE ALL TRIGGERS;  
  
  
ALTER TABLE table_name ENABLE ALL TRIGGERS;  
 
 

Delete TRIGGER use DROP TRIGGER

 
 

  
  
DROP TRIGGER trigger_name;  
 
 

 

Differences between Oracle procedures and functions

Use of Oracle to_char Functions

Implementation of oracle function return table

Learn about the Oracle FBI Index

How to install Oracle as a Linux Service