Oracle triggers and Oracle triggers
Oracle triggers
A trigger is a special storage process that is automatically triggered by the Oracle system when a database occurs. Triggers are usually used to enhance database integrity constraints and business rules. For tables, triggers can implement more complex constraints than CHECK constraints.
The types of triggers in Oracle include DML triggers, alternative triggers, system event triggers, and DDL triggers.
Trigger category
- DML triggers
DML triggers are triggered by DML statements, such as INSERT, UPDATE, and DELETE statements.
For all DML events, DML triggers can be divided into BEFORE triggers and AFTER triggers based on the trigger time, indicating that actions are taken BEFORE and AFTER a DML event.
In addition, DML triggers can be divided into statement-level triggers and row-level triggers. Statement-level triggers are triggered once for a specific statement, and row-level triggers are triggered once for each row affected by the statement.
- INSTEADTrigger
Instead of trigger (Alternative trigger) is used to execute an alternative operation to trigger an event. For example, an instead of trigger for an INSERT event is triggered by an INSERT statement, when an INSERT statement appears, the statement is not executed. INSTEAD, the Statement defined in the instead of trigger is executed.
- System event triggers
A System Event trigger is triggered when a system event such as database startup or shutdown occurs.
- DDL trigger
DDL triggers are triggered by DDL statements, such as CREATE, ALTER, and DROP statements. DDL triggers are also divided into BEFORE triggers and AFTER triggers.
Trigger Creation
To CREATE a TRIGGER, use the create trigger statement. The syntax is as follows:
CREATE [OR REPLACE] TRIGGER trigger_name[BEFORE|AFTER|INSTEAD OF] trigger_event{ON table_name|view_name|DATABASE}[FOR EACH ROW][ENABLE|DISABLE][WHEN trigger_condition][DECLARE declaration_statements;]BEGIN trigger_body;END [trigger_name];