Triggers in Oracle databases

Triggers in Oracle databases

A trigger is a special stored procedure that is automatically triggered by the Oracle system when a database event 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. The corresponding trigger_event content is as follows:
{INSERT | DELETE | UPDATE [OF column [,...]} 1
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.

--------------------------------------------------------------------------------

BEFORE row triggers
When developing database applications, constraints should be used to restrict the input data to ensure that the data complies with commercial or enterprise rules, in some cases, constraints may not be complex business logic or enterprise rules, because BEFORE row triggers are considered.
AFTER row triggers
To audit DML operations, you can use statement-level triggers or the audit functions provided by Oracle. To audit data changes, you should use the AFTER row triggers.

--------------------------------------------------------------------------------

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.
DML operations can be refined to columns, that is, the trigger is activated when a DML operation is performed on a column.
In a row-Level Trigger, to obtain data before and after a DML operation, Oracle provides two special identifiers: OLD and NEW. column_name and: NEW. column_name obtains the old data and new data of the column respectively. INSERT triggers can only use: NEW, DELETE can only use: OLD, and UPDATE can be used.

Example 1:
1. First, Log On As SYSDBA, create the user SIEGE, and grant related permissions (this step can be omitted ):

Create user siege identified by "123456 ";
Grant create session to siege;
Grant create table to siege;
Grant create tablespace to siege;
Grant create view to siege;
Grant create any trigger to siege;
Create tablespace learning;
DATAFILE 'd: \ oraclexe \ app \ oracle \ oradata \ XE \ leaning. dbf'
SIZE 100 M
Autoextend on next 5 m maxsize 200 M;

Alter user siege default tablespace leaning;
Alter user siege quota unlimited on leaning; 1
2. Create a student table and a record table and insert relevant data:

Create table student (
Sid NUMBER (4 ),
Sname VARCHAR2 (10 ),
Sage NUMBER (4)
);
Create table record (
Content VARCHAR2 (80 ),
Rtime timestamp
);
Insert into student VALUES (1001, 'candy ', 24 );
Insert into student VALUES (1002, 'tracy ', 25 );
Insert into student VALUES (1003, 'Sam ', 23); 1
3. Create a trigger for the student table:

Create or replace trigger update_student_trigger
AFTER UPDATE
ON student
FOR EACH ROW
BEGIN
Insert into record values ('data before the UPDATE operation is performed is: sid = '|: OLD. sid, SYSDATE );
END update_student_trigger; 1
4. Test trigger:
Modify student table data:

UPDATE student SET sage = 26; 1
Check the record data and find that three data items have been updated, which indicates that the trigger is a row trigger:

1. After the UPDATE operation is performed, the data before the operation is: sid = 1001 15-FEB-15 0020.3.42.000000
2. After the UPDATE operation is performed, the data before the operation is: sid = 1002 15-FEB-15 0020.3.42.000000
3. After the UPDATE operation is performed, the data before the operation is: sid = 1003 15-FEB-15 0020.3.42.000000

Instead of trigger
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.
However, the instead of trigger in Oracle cannot target tables, but views. Not all columns in a view are supported. For example, if a column is computed using a mathematical or function, you cannot perform DML operations on the column. In this case, you can use the instead of trigger.
Example 2:
Create a view for the student table in Example 1. The statement is as follows:

Create view student_view
AS
SELECT sid, sname, sage + 1 NEW_age
FROM student
With check option; 1
After creating a view, insert it:

Insert into student_view VALUES (1004, 'Peter ', 22); 1
At this point, a ORA-01773 error is reported. In this case, we should create an instead of trigger for student_view:

Create or replace trigger insteadof_student_view
INSTEAD OF INSERT
ON student_view
FOR EACH ROW
BEGIN
Insert into student (sid, sname, sage)
VALUES (: NEW. sid,: NEW. sname,: NEW. new_age );
END insteadof_student_view;
1
After the trigger is created, execute the preceding insert statement again to insert the trigger.

System event triggers
A System Event trigger is triggered when a system event such as database startup or shutdown occurs. To create a system event trigger, you must use the on database clause, that is, the created trigger is a DATABASE-level trigger, and the system event trigger must have the DBA permission. System events are as follows:

System Events	Description
LOGOFF	Log out from the database
LOGON	User Login Database
SERVERERROR	Server Error
SHUTDOWN	Shut down database instances

Example 3:
Log On As SYSDBA and create table_event:

Create table table_event (
Event VARCHAR2 (30 ),
Time DATE
)
1
Then create the tr_startup trigger and the tr_shutdown trigger:

Create or replace trigger tr_startup
AFTER startup ON DATABASE
BEGIN
Insert into table_event VALUES (ora_sysevent, SYSDATE );
END tr_startup;
1

--------------------------------------------------------------------------------
Create or replace trigger tr_shutdown
BEFORE shutdown ON DATABASE
BEGIN
Insert into table_event VALUES (ora_sysevent, SYSDATE );
END tr_shutdown; 1
Close and start the database and view the table_event table:

SHUTDOWN 16/02/2015 01:01:02
STARTUP 16/02/2015 01:01:56

This indicates that the system trigger has taken effect.

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];