PL/SQL -- triggers and plsql triggers

PL/SQL -- triggers and plsql triggers

I. Introduction
The trigger is stored as an independent object in the database. Unlike the stored procedure and function, the stored procedure and function must be displayed and called before execution, A trigger is triggered by an event. Oracle events refer to the insert, update, and delete operations on the tables or views of the database, or similar operations on the views. Oracle extends the trigger function to ORACLE, such as user logon and database startup and shutdown. Therefore, triggers are often used to complete the constraints of complex business rules that are difficult to complete due to database integrity constraints, or to monitor various operations on the database to implement audit-related functions.

Trigger composition:

Trigger event: trigger in any situation, such as insert, update, and 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 execution body after the trigger is triggered. 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 separately for each row of data that receives the operation.
Oracle triggers are divided into the following three types:
1. DML trigger DML Data Manipulation Language)
DML statements (insert, update, and delete) can be triggered before or after DML operations, and each row or statement operation can be triggered.
2. Alternative triggers
In ORACLE, views created from more than two tables cannot be operated directly. Therefore, an alternative trigger is provided. It is a specific solution for view operations in ORACLE 8.
3. system triggers
ORACLE 8i provides a third type of trigger called a system trigger. It can be triggered in ORACLE Database System Events, such as ORACLE system startup and shutdown.

2. DML triggers

Oracle can trigger a dml statement before or after a dml operation, and can trigger each row (Row-level) or statement (level-level) operation. DML triggers are divided into two types:

• Row trigger: refers to the trigger activated for each affected row. The definition is similar to the statement trigger, with the following two exceptions:

1. The definition statement contains the for each row clause.
2. In BEFORE ...... In the for each row trigger, you can reference the affected ROW values.

• Statement trigger: a trigger for a specific statement or statement group executed on a table or view in some situations. Can be associated with the INSERT, UPDATE, DELETE, or combination. However, no matter what combination is used, each statement trigger will only activate the specified statement once. No matter how many rows are updated, only one update statement trigger is called.

The syntax for creating a trigger is as follows:

Create or replace trigger trigger_name
{Before | after}
{Insert | delete | update [of column]}
On [schema] table_name
[Referencing {old [as] old | new [as] new | parent as parent}]
[For each row]
[When condition]
Trigger body;

The structure of the following person table is as follows:

DROP TABLE person ;CREATE TABLE person (id NUMBER(11) NOT NULL ,username VARCHAR2(255 ) NULL ,age NUMBER(11) NULL ,password VARCHAR2(255) NULL ,PRIMARY KEY (id))

1. insert row-level triggers

Create or replace trigger trigger_insert before insert on person referencing new as new old as old for each rowdeclare v_username varchar2 (50); begin v_username: =: new. username;: new. age: =: new. age + 2; -- insert age plus 2 dbms_output.put_line (v_username); -- insert the username field of the inserted record end trigger_insert; -- add data insert into person values (1, 'zhang san ', 20, 'hangsan ');

2. update row-level triggers

Create or replace trigger trigger_update_before before update on person referencing new as new old as old for each rowdeclare v_username varchar2 (50); begin v_username :=: new. username; -- Record the new name: new. age: =: new. age + 3; -- modify the age dbms_output.put_line (v_username); end trigger_update_before; -- after update trigger create or replace trigger trigger_update_after before update on person referencing new as new old as old for each rowdeclare v_old_name varchar2 (50); begin v_old_name: =: old. username; -- record the name dbms_output.put_line (v_old_name) before modification; end trigger_update_after; -- update data update person set username = 'item 3 1', age = 19 where id = 1;

3. delete row-level triggers

Create or replace trigger trigger_delete before delete on person referencing new as new old as old for each rowdeclare v_old_name varchar2 (50); begin v_old_name :=: old. username; -- get the deleted user name dbms_output.put_line (v_old_name); end trigger_delete; -- delete data delete from person where id = 1;

4. Statement triggers

-- Record the operation table information create table person_log (who varchar2 (30), when date, action varchar2 (50 )) -- trigger create or replace trigger trigger_person_statment before insert or update or delete on persondeclare -- save operation information v_action person_log.action % type; begin if inserting then v_action: = 'insert'; elsiupdatf ing then v_action: = 'update'; elsif deleting then v_action: = 'delete'; else raise_application_error (-20001, 'you should never ever get this error. '); end if; insert into person_log (who, action, when) values (user, v_action, sysdate); end trigger_person_statment; -- operate shujdeclarebegin insert into person values (1003, '123', 20, '123'); insert into person values (1342, '123', 20, '123 '); update person set age = 19 where id> 1000 and id <1003; -- execute a delete from person where id> 1000 and id <1003; -- execute the end command once after the trigger;

Trigger trigger order

1. Execute the BEFORE statement-Level Trigger;
2. For each row affected by the statement
2.1 execute the BEFORE row-Level Trigger
2.2 execute DML statements
2.3 execute the AFTER row-Level Trigger
3. Execute the AFTER statement-Level Trigger

Iii. Alternative triggers

It is used to execute an alternative operation to trigger the event. For example, an INSERT event replacement trigger is triggered by an INSERT statement. When an INSERT statement appears, the statement is not executed, but is executed to replace the Statement defined in the trigger.
Note the following when creating an INSTEADOF trigger:

• You can only create a view without specifying the WITHCHECKOPTION option.
• The BEFORE or AFTER option cannot be specified. The FOREACHROW Sub-item is optional, that is, the INSTEADOF trigger can only be triggered at the row level, or can only be a row-level trigger, and it is not necessary to specify.
• There is no need to create an INSTEADOF trigger on the view of a table. You only need to create a DML trigger.

Sample Code:

-- View create or replace view v_person as select username, age, password, id p_id from person; -- trigger create or replace trigger update_v_person instead of update on v_person begin update person set username = substr (: new. username, instr (: new. username, ',') + 2), age =: new. age, password =: new. password where id =: new. p_id; -- View end update_v_person; -- update name update person set username = 'chen1, donny1 ', age = 18 where id = 1 select * from person where id = 1;

Iv. system triggers
System event triggers are triggered when a system event, such as database startup or shutdown, includes database server startup or shutdown, user logon and logout, and database service errors. System triggers can be triggered in DDL statements (Database-defined statements, such as crate, alter, and drop.

The system trigger time and running time are as follows:

Event	Start Time	Description
Startup)	After	Activation upon instance startup
Shutdown)	Before	Activated when the instance is shut down normally
Server Error)	After	Activate the service if an error occurs.
Login)	After	Activated after successful login
Log off)	Before	Activate upon logout
Create)	Before, after	Activated before or after creation
Delete)	Before, after	Activated before or after cancellation
Alter)	Before, after	Activated before or after a change

Example

Record User Logon Information

-- Prepare the table and save the user logon information. create table loggin_event (username varchar2 (50), logintime date ); -- create or replace trigger t_login after LOGON DATABASEdeclarebegin -- save the USER login information insert into loggin_event (username, logintime) values (USER, sysdate); commit; end t_login;

Record the information of the created table

-- Record the information of the created table drop table test_create_log; create table test_create_log (event varchar2 (50), type varchar2 (50), name varchar2 (50), owner varchar2 (50 ), createtime date default sysdate); -- create or replace trigger t_create after create on database declare v_event varchar2 (50); v_type varchar2 (50); v_name varchar2 (50 ); v_owner varchar2 (50); begin -- read DDL event attribute v_event: = sysevent; -- event v_type: = dictionary_obj_type; -- object type, such as table v_name: = dictionary_obj_name; -- Object Name; if the table does not have v_owner: = dictionary_obj_owner; -- owner insert into test_create_log values (v_event, v_type, v_name, v_owner, sysdate); end t_create; select * from test_create_log; drop table t_1; create table t_1 (id number); select * from test_create_log;

4. Precautions for triggers

Before and afterz support the trigger time, whether to trigger before or after.

For each row indicates that the name trigger is a row trigger. When the Holy Spirit for each row statement, the trigger is the statement trigger, and the alternative trigger (instead of) is the row trigger.

The feferencing clause describes the relevant names. You can use the relevant names in the PL/SQL block and WHEN clause of the row trigger. Refer to the new and old column values. The default names are old and new. When the trigger's PL/SQL block should contain related names, you must add a colon (:) before them, but not in the when clause.

: The value after the new access operation is complete, which is the value before the old access operation is complete.

Insert is only valid for: new, update: new, and: old, and delete is only valid for: old.

Oracle triggers are generally used in the following aspects:

1. Database Security
User operations can be restricted based on time, for example, database data cannot be modified after work or holidays. User operations can be restricted based on the data in the database. For example, the price increase cannot exceed 10% at a time.

2. Implement complicated security authorization
The trigger can be used to control the security of objects and grant permissions to various database values.

3. provide complex audit functions
Audit user statements for database operations. Write updates to the database into the audit table.

4. Maintain table synchronization between different databases
Snapshots can be used between different databases to replicate data. However, some systems (for example, a system has two databases, one of which only provides system read and the other one provides system write) two databases are required
Real-time synchronization requires the use of triggers to replicate data from one database to another.

5. implement complex data integrity rules
Implements non-standard data integrity checks and constraints. Triggers can generate more complex limits than rules. And the rule is not
The trigger can reference columns or database objects. Provides variable default values.

6. implement complex non-standard database-related integrity rules
Triggers can be used to update related tables in the database. For example
The delete trigger can delete the matched rows in other tables. The trigger can reject or roll back the changes that undermine the integrity of the transaction and cancel the transaction trying to update the data. This trigger works when an external key that does not match its primary key is inserted.

7. synchronous table replication

8. event logging

Create a trigger using pl/SQL

In the trigger, you cannot select * from userinformation;
If yes, where can I select the information?

Error: ora-04098: the trigger is invalid and has not passed reverification. This indicates that when you create a trigger, an error is reported, but the trigger is created and is in a trigger with an error. You need to re-run a trigger creation script to see what error is reported?

PL/SQL triggers

You do not have permission. Use sys to log in and change