PL/SQL triggers

PL/SQL triggers

PL/SQL triggers

Triggers are a technology provided by many relational database systems. In the Oracle system, triggers and similar procedures and functions have PL/SQL blocks that declare, execute, and handle exceptions.

① Trigger type
The trigger is stored in the database as an independent object. Unlike the stored procedure, the stored procedure starts or runs directly through other programs, A trigger is started by an event.
That is, the trigger runs automatically and implicitly when an event occurs. In addition, the trigger cannot receive parameters. Therefore, running a trigger is called firing ).
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.
1. DML triggers
ORACLE can trigger a DML statement before or after a DML operation, and can trigger each row or statement operation.
2. Replace the trigger
In ORACLE, views created from more than two tables cannot be operated directly. Therefore, an alternative trigger is provided.
3. system triggers
It can be triggered in ORACLE Database System Events, such as ORACLE system startup and shutdown.

② Trigger composition:
TRIGGER event: Under which conditions TRIGGER; for example: INSERT, UPDATE, DELETE.
TRIGGER time: whether the TRIGGER is triggered BEFORE or AFTER the TRIGGER event, that is, the TRIGGER event and the TRIGGER operation sequence.
TRIGGER itself: the purpose and intent of the TRIGGER after it is triggered. It is exactly what the TRIGGER itself is going to do. For example, PL/SQL block.
Trigger frequency: the number of times the action defined in the trigger is executed. STATEMENT-level (STATEMENT) triggers and ROW-level (ROW) triggers.
STATEMENT-Level Trigger: When a trigger event occurs, the trigger is executed only once;
ROW-Level Trigger: When a trigger event occurs, the trigger is executed independently for each ROW of data affected by the operation.

③ Create a trigger
The general syntax for creating a trigger is:
CREATE [or replace] TRIGGER trigger_name
{BEFORE | AFTER}
{INSERT | DELETE | UPDATE [OF column [, column…]}
ON [schema.] table_name
[For each row]
[WHEN condition]
Begin
Trigger_body;
End;
Where:
BEFORE and AFTER indicate that the trigger time series are pre-trigger and post-trigger, respectively,
The trigger is triggered before the trigger event is executed, and the trigger is triggered after the trigger event is executed.
The for each row option indicates that the trigger is a ROW trigger.
The difference between a row trigger and a statement trigger is as follows: a row trigger requires that when a DML statement operation affects multiple rows of data in the database,
Trigger is activated once as long as they meet the trigger constraints;
The statement trigger uses the entire statement operation as a trigger event. when it meets the constraints, the trigger is activated once.
If the for each row option is omitted, BEFORE and AFTER triggers are statement triggers, while instead of triggers are ROW triggers.
The WHEN clause specifies the trigger constraint. Condition is a logical expression, which must contain the relevant name, but cannot contain query statements, nor can it call PL/SQL functions.
The trigger constraints specified by the WHEN clause can only be used in BEFORE and AFTER row triggers, and cannot be used in instead of row triggers or other types OF triggers.

The stored procedure to be executed when a base table is modified (INSERT, UPDATE, DELETE). The execution is automatically triggered based on the changes to the base table it is attached to. Therefore, it is irrelevant to the application,
Database Triggers can ensure data consistency and integrity.

Up to 12 types of triggers can be created for each table. They are:
BEFORE INSERT
BEFORE INSERT FOR EACH ROW
AFTER INSERT
AFTER INSERT FOR EACH ROW

BEFORE UPDATE
BEFORE UPDATE FOR EACH ROW
AFTER UPDATE
AFTER UPDATE FOR EACH ROW

BEFORE DELETE
BEFORE DELETE FOR EACH ROW
AFTERDELETE
AFTER DELETE FOR EACH ROW

④ Trigger order
1. Execute the BEFORE statement-Level Trigger;
2. For each row affected by the statement:
Execute the BEFORE row-Level Trigger
Execute DML statements
Execute the AFTER row-Level Trigger
3. Execute the AFTER statement-Level Trigger

⑤ Create a DML trigger
The trigger name can have the same name as the table or process, but the trigger name cannot be the same in a mode.
Trigger restrictions
The length of the create trigger statement text cannot exceed 32 KB;
The SELECT statement in the trigger can only be SELECT... INTO... Structure, or the SELECT statement used to define the cursor.
The database transaction control statement COMMIT, ROLLBACK, and SVAEPOINT statements cannot be used in triggers;
The procedure or function called by the trigger cannot use the database transaction control statement;

Problem: When a trigger is triggered, you must use the column values in the inserted, updated, or deleted records, and sometimes use the values in the pre-and post-operation columns.
Implementation: value of the column after the NEW modifier completes access
: The value at the top of the access completed by the OLD Modifier
Feature INSERT UPDATE DELETE
Old null is valid.
NEW Valid valid NULL
[Example]
Create or replace trigger hello_trigger
After
Update on employees
For each row
Begin
Dbms_output.put_line ('Hello ...');
Dbms_output.put_line ('old. salary: '|: old. salary |', new. salary '|: NEW. salary );
End;

Then execute: update employees set salary = salary + 1000;

⑥ Create an alternative trigger
The general syntax for creating a trigger is:
CREATE [or replace] TRIGGER trigger_name
INSTEAD
{INSERT | DELETE | UPDATE [OFcolumn [, column…]}
ON [schema.] view_name
[For each row]
[WHENcondition]
Begin
Trigger_body;
End;
Where:
The instead of option enables ORACLE to activate the trigger without executing the trigger event.
You can only create an instead of trigger for views and object views, rather than an instead of trigger for tables, modes, and databases.
The for each row option indicates that the trigger is a ROW trigger.
The difference between a row trigger and a statement trigger is as follows: a row trigger requires that when a DML statement operation affects multiple rows of data in the database, trigger is activated once as long as they meet the trigger constraints;
The statement trigger uses the entire statement operation as a trigger event. when it meets the constraints, the trigger is activated once.
If the for each row option is omitted, BEFORE and AFTER triggers are statement triggers, while instead of triggers are ROW triggers.
The WHEN clause specifies the trigger constraint. Condition is a logical expression, which must contain the relevant name, but cannot contain query statements, nor can it call PL/SQL functions.
The trigger constraints specified by the WHEN clause can only be used in BEFORE and AFTER row triggers, and cannot be used in instead of row triggers or other types OF triggers.
INSTEAD_OF is used to trigger DML of a view. Because a view may be composed of multiple tables joined, not all joins are updatable.
However, you can perform updates as needed,
For example:
Create or replace view emp_view
AS
SELECT deptno, count (*) total_employeer, sum (sal) total_salary
FROM emp group by deptno;

The direct deletion in this view is invalid:
SQL> DELETE FROM emp_view WHERE deptno = 10;
Delete from emp_view WHERE deptno = 10
*
ERROR is located in row 1st:
ORA-01732: The data manipulation operation for this view is invalid

However, you can create an INSTEAD_OF trigger to perform the required processing for the DELETE operation, that is, DELETE all the benchmark rows in the EMP table:

Create or replace trigger emp_view_delete
Instead of delete on emp_view FOR EACH ROW
BEGIN
Delete from emp WHERE deptno =: old. deptno;
END emp_view_delete;

Delete from emp_view WHERE deptno = 10;

7. Create a system event trigger
System event triggers provided by ORACLE can be triggered on DDL or database systems. DDL refers to the data definition language, such as CREATE, ALTER, and DROP.
Database System events include database server startup or shutdown, user logon and logout, and database service errors. The syntax for creating a system trigger is as follows:

1. The general syntax for creating a trigger is:
Create or replace trigger [Rule Ema.] trigger_name
{BEFORE | AFTER}
{Ddl_event_list | database_event_list}
ON {DATABASE | [schema.] SCHEMA}
[WHEN_clause]
Begin
Trigger_body;
End;
Ddl_event_list: one OR more DDL events are separated by OR. database_event_list: one OR more database events are separated by OR;
System event triggers can be created in both a mode and a whole database.
When it is built on a SCHEMA, the trigger is activated only when the DDL operations and errors caused by the users specified in the SCHEMA are activated. The default mode is the current user mode.
When a DATABASE is built on it, all user DDL operations and errors caused by the DATABASE, as well as DATABASE startup and shutdown can activate the trigger.
To create a TRIGGER on a DATABASE, you must have the administer database trigger permission.

Sequence deletion trigger:
Drop trigger trigger_name;
When you delete a TRIGGER name in another user mode, you must have the permission to drop any trigger,
When deleting a TRIGGER created on a DATABASE, you must have the administer database trigger system permission.
In addition, when a table or view is deleted, triggers created on these objects are also deleted.

Trigger status
Database TRIGGER status:
ENABLE: When a TRIGGER event occurs, the TRIGGER of a database TRIGGER in the active state is triggered.
DISABLE: When a TRIGGER event occurs, the database TRIGGER in the invalid state will not be triggered, which is the same as the TRIGGER without this database.
The two statuses of database Triggers can be converted to each other. Format:
Alter tigger trigger_name [DISABLE | ENABLE];
Example: alter trigger emp_view_delete DISABLE;
The alter trigger statement can only change the status of one TRIGGER at a time, while the alter table statement can change the usage status of all triggers related to the specified TABLE at a time. Format:
Alter table [schema.] table_name {ENABLE | DISABLE} all triggers;
For example, invalidate all triggers on the table EMP:
Alter table emp disable all triggers;

Oracle -- plsql Composite data type

-------------------------------------- Split line --------------------------------------

Rlwrap

SQLPLUS spool to dynamic Log File Name

Oracle SQLPLUS prompt settings

Accelerate SQL return by setting SQLPLUS ARRAYSIZE (row prefetch)

PL/SQL Developer Practical Skills

-------------------------------------- Split line --------------------------------------