ORACLE trigger 1-Introduction

Oracle official reference: PL/SQL language referenc-> 9 PL/SQL trigger

Reasons to use trigger:

■ Automatically generate calculated column values
■ Log events
■ Gather statistics on Table Access
■ Modify Table data when DML statements are issued against views
■ Enforce referential integrity when child and parent tables are on different nodes of a distributed database
■ Publish information about database events, user events, and SQL statements to subscribing applications
■ Prevent DML operations on a table after regular business hours
■ Prevent invalid transactions
■ Enforce complex business or referential integrity rules that you cannot define with Constraints

I am relatively poor in E, so I will not explain it one by one if I do not mislead others.

A trigger is stored in a database as an independent object. Unlike stored procedures and functions, a stored procedure and function must be displayed and called before execution, A trigger is started by an event. That is, the trigger runs automatically and implicitly when an event occurs. In addition, triggers cannot receive parameters like stored procedures. Oracle events refer to the insert, update, and delete operations on database tables or similar operations on views. Oracle extends the trigger function to Oracle, such as database startup and shutdown. Therefore, triggers are often used to complete the constraints of complex business rules that are difficult to complete due to the integrity constraints of the database, or to monitor various operations on the database to implement the audit function.

DML triggers

Oracle can trigger a DML statement before or after a DML operation, and can trigger each row or statement operation.

DDL trigger

The syntax for creating DDL triggers is similar to that for DML triggers, but trigger events are different (create table, alter index, drop trigger, etc.). These triggers do not apply to a table.

Alternative trigger

In Oracle, views created from more than two tables cannot be operated directly. Therefore, an alternative trigger is provided.

Database event triggers

Oracle provides the third type of triggers. Database event triggers are also called system triggers. It can be triggered in Oracle Database System Events, such as Oracle system startup and shutdown.

Trigger Components

1. Trigger name
2. Trigger statement
3. Trigger restrictions
4. trigger the operation

Trigger design guidelines:

■ Use triggers to ensure that whenever a specific event occurs, any necessary actions are done (regardless of which user or application issues the triggering Statement ).
For example, use a trigger to ensure that whenever anyone updates a table, its log file is updated.
■ Do not create triggers that duplicate database features.
For example, do not create a trigger to reject invalid data if you can do the same with constraints.
■ Do not create triggers that depend on the order in which a SQL statement processes rows (which can vary ).
For example, do not assign a value to a global package variable in a row trigger if the current value of the variable depends on the row being processed by the row trigger. if a trigger updates global package variables, initialize those variables in a before statement trigger.
■ Use before row triggers to modify the row before writing the row data to disk.
■ Use after row triggers to obtain the row ID and use it in operations. An after row trigger fires when the triggering statement results in ORA-2292.

■ If the triggering statement of a before statement trigger is an update or deletestatement that conflicts with an update statement that is running, then
Database does a transparent rollback to savepoint and restarts the triggering statement. The database can do this operation times before the triggering statement
Completes successfully. Each time the database restarts the triggering statement, the trigger fires. The rollback to savepoint does not undo changes to package
Variables that the trigger references. To detect this situation, include a counter variable in the package.
■ Do not create recursive triggers.
For example, do not create an After update trigger that issues an update Statement on the table on which the trigger is defined.

The trigger fires recursively until it runs out of memory

■ Use database Triggers judiciously. They fire every time any database user initiates a triggering event.
■ If a trigger runs the following statement, the statement returns the owner of the trigger, not the user who is updating the table:
Select username from user_users;
■ Only committed triggers fire.
A trigger is committed, implicitly, after the create trigger statement that creates it succeeds.

Therefore, the following statement cannot fire the trigger that it creates:
Create or replace trigger my_trigger
After create on Database
Begin
NULL;
End;
/
■ To allow the modular installation of applications that have triggers on the same tables, create multiple triggers of the same type, rather than a single trigger that
Runs a sequence of operations. Each trigger sees the changes made by the previously fired triggers. Each trigger can see old and new values.