Oracle trigger and Oracle trigger

Oracle trigger and Oracle trigger

In the past, we made projects. All are front-end backend codes. Because the database is written by the Project Manager. I did not have much Optimization on the database. After a long time, I forgot about the database. Recently, I used the Oracle database. As we all know, I used this database, it is a project with a large data volume. Optimization is necessary. Therefore, I will take the time to review it after work and record it. I will not only share it with you, but also review it later. If I do not speak correctly, please leave a message to correct it. I will accept it and change it to improve.

1. What is a trigger? When an insert, update, or delete statement is issued on a specified table, Oracle automatically executes the sequence of statements defined in the trigger. In this definition, we can see the role of the trigger, and add, delete, modify, and query. Why is the trigger not queried. Because Oracle triggers are a pending action for data changes. For example, if you delete a field from a table, the deletion trigger starts and runs its command. Similarly, the modification and addition methods are the same. When you execute the add, delete, and modify method to change the table data, the trigger you set will be executed. Example: after a new employee is inserted successfully, a message "New Employee inserted successfully" is automatically printed ";

Create trigger saynewemp <span style = "white-space: pre"> </span> // create a trigger <span style = "font-family: Arial, Helvetica, sans-serif; "> saynewemp is the trigger name </span> after insert <span style =" white-space: pre "> </span> // trigger on emp <span style =" white-space: pre "> </span> // target table declare <span style =" white-space: pre "> </span> // declare begin <span style =" white-space: pre "> </span> // start dbms_output.put_line (" New Employee inserted successfully ") <span style = "white-space: pre"> </span> // print an end statement when triggering. <span style = "white-space: pre "> </span> // result

The insert trigger of an employee table is ready. After executing the insert operation on the employee table, the action in the trigger will be executed. As shown above, a sentence will be automatically printed. 2. trigger Application Scenario 1: complex security checks, such as the requirements in our project. If there is a weekend holiday, the database cannot be changed, then we can use the trigger to restrict him. The second is data validation. For example, when you buy 100 yuan for 1 yuan, when the boss asks you for money, are you sure you want to count the database? Are there any mistakes? 3: Database Audit, which is a database operation record. Who can record what operations are performed on the database, oracle already has this built-in function. We can also use triggers to implement its fourth type: data backup and synchronization. This also helps us understand that after you operate the database, this triggers the backup operation, which is a bit like saving the game in the invincible standalone game hero. When it is your turn to operate, it will automatically execute an automatic save. 3. Create a. Trigger syntax

CREATE [or REPLACE] TRIGGER name <span style = "white-space: pre "> </span> // create a trigger {BEFORE | AFTER} <span style =" white-space: pre "> </span> // trigger execution sequence, run {DELETE | INSERT | UPDATE (OF column name)} <span style = "white-space: pre "> </span> // Method for which to set the trigger. In the UPDATE method, you can use OF to specify the column name ON table name <span style =" white-space: pre "> </span> // table [for each row [WHEN (condition)] <span style =" white-space: pre "> </span> // if there is a for each row, it is a ROW-level trigger, and vice versa, it is a statement trigger PLSQL block <span style =" white-space: pre "> </span> // execution block

Is it Abstract? In fact, you can clearly know their differences from their names. Let me give an example below. Example: We want to insert a field data (with three entries) in the employee table into a new table,

<span style="white-space:pre"></span>INSERT INTO EMP10 SELECT * FROM EMP WHERE NAME = 10;

If it is a statement-level trigger, it is triggered only once, because the statement-Level Trigger is for tables, and the row-Level Trigger, as mentioned earlier, is three records, three row-level triggers are triggered, that is, rows. In the preceding syntax, the ROW-level trigger for each row statement can be followed by the WHEN condition to specify the ROW.

These are the introduction of triggers. You can see what the trigger is, what it is used, and under what scenarios it is used. Although this article is very basic, it is helpful for new people.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.