Basic PL/SQL programming (9) [Transaction consistency, isolation, concurrency] [row-level triggers, statement triggers, replace

1. Trigger:
Concept:
It can be seen as a special process, which is automatically executed and does not allow parameters to be included.
The trigger can be used to automatically perform some operations, such as row triggers and data triggers. It is mainly a trigger for Data Control languages.
 
2. transactions:
Concept: to ensure data integrity. One or more SQL statements are used to form a logical unit. Then execute the command. In this way, all the results will succeed or fail.
 
Features:
Atomicity: When two or more tables are operated, one operation is successful, and other operations are unsuccessful. An error occurs, which is an atomic error. Both succeed or fail, that is, two constitute one unit.
Consistency: for example: the bank account payment description 'payer minus the corresponding money, and the recipient must add the corresponding money. '
Isolation: the combination of isolation and concurrency enables stronger isolation and lower concurrency. Opposite
 
Commit and Rollback:
Overview:
After we modify the table DML, if we do not commit, we only modify the data in the memory, but the table is not modified. However, we can use another user to check this content.
This is the isolation level of transactions.
 
Concurrency and isolation:
Overview:
Two users query and modify a table at the same time. Data inconsistency.
Example:
Currently, there are five tickets for a train, two tickets for a train, five for B, and four for B. In this case, the train ticket is sold for 6 times. This is unreasonable. Therefore, if we operate a train ticket, [Lock] the ticket and [unlock] the ticket after the ticket is handled. When Party B handles the ticket, it cannot operate the ticket. If you click submit, the system will prompt "unable to complete the operation, not so many votes ." This reduces concurrency and increases isolation.
 
Permanent: once submitted, you cannot roll back again. Once rolled back, you cannot submit again. Because the physical data has been changed after the submission. So you cannot roll back any more.
 
 
Trigger application:
Note: rollback statements cannot be written in the trigger statement body.
RAISE_APPLICATION_ERROR ('-20000', 'cannot be deleted') two parameters are error number and prompt content, implement control trigger, error number in
-Between 20999----20000
 
Two important memory tables: OLD and NEW
Old and New are used as ": old. id and: new. id"
 
: New -- reference the latest column value;
: Old -- to reference a previous column value. These two variables exist only when the keyword "for each row" is used. there are two update statements, while insert only has: new and delect only has: old;
 
 
 
 
 
Trigger case:
Case 1: Create a trigger-delete the row corresponding to emp when deleting the dept table
 
I. Create a trigger
Create or replace trigger del_dept -- create a trigger
After delete on dept -- triggered after deleting the dept table
For each row -- row-Level Trigger
Begin -- trigger statement Block
Delete from emp where eid =: old. eid; -- old indicates the memory table. Here, this call indicates the application of dept's eid at the time of deletion.
End del_dept; -- end
Ii. Execute the triggered statement
Delete from dept where eid = 1;
 
Case 2: Create a trigger-restrict the deletion of rows in the emp table (Row-Level Trigger)
Create or replace trigger del_xianzhi_dept -- create a trigger
After delete on dept -- after deleting a dept table
For each row -- row-Level Trigger
Begin
If: old. eid = 3 then
RAISE_APPLICATION_ERROR (-20001, 'cant'); -- the system prompts an error and does not perform any operations.
Dbms_output.put_line ('aaaaa ');
End if;
End del_xianzhi_dept;
 
 
Case 3: Statement-level triggers
Note: Statement-level triggers do not need to be written for each row.
 
Create or replace trigger ti -- create a trigger
After insert or delete or update on dept -- Statement-Level Trigger can trigger all three actions
Begin -- below is some logic processing.
If inserting then
Dbms_output.put_line ('I ');
Elsif deleting
Dbms_output.put_line ('D ');
Else updating
Dbms_output.put_line ('U ');
End if;
End;
/
 
 
3. The replacement trigger can only be created on the view, which can solve the problem that the view cannot update multiple tables at the same time.
We all know that a view composed of two tables cannot operate on both tables at the same time. However, replacing the trigger can solve this problem.
 
Create or replace trigger tr_view1 -- create a trigger
Instead of insert on view1 -- replace the trigger on The view
For each row -- a row-level trigger. You can use new
Begin -- business processing. Insert two tables
Insert into emp values (: new. id, 'aaa ');
Insert into dept (: new. id,: new. id, 'bbb ');
End;
/

From the column zhang6622056