ORACLE trigger exercises

Development and Design of Oracle triggers

 

1. What is a trigger?
A database trigger is a stored PL/SQL block, which is associated with a base table. When a specific database maintenance (insert, delete, update) is performed on the table, implicitly execute a PL/SQL block.

 

Ii. Functions of triggers:
. Prevents unauthorized database operations and maintains database security
. Audits database operations to store historical data
. Complete database Initialization
. Control Database Data Integrity
. Modify relevant data
. Complete data replication
. Automatically complete database statistical calculation
. Restrict the time and permissions of database operations to control the security of entities.

 
Iii. Trigger composition:
1. trigger time: Order of trigger events (before, afer) [2]

2. trigger event: What SQL statement will trigger (insert, delete, update) [3]

3. Trigger child: the operation to be performed when the trigger is triggered (a complete PL/SQL Program)

4. Trigger type: the number of times the trigger is executed (statement-level, row-level) [2] // The statement-level execution is performed only once, and the row-level execution is performed multiple times.

[*] Up to 12 triggers of different types can be created for a table: 3*2*2 = 12

 

4. Precautions for creating a trigger:
1. You can call stored procedures and packages in triggers. You cannot call triggers in stored procedures.

2. The commit, rollback, and savepoint statements cannot be used in triggers.

3. Do not indirectly call stored procedures and functions that contain commit, rollback, and savepoint statements in triggers.

 

5. Create a statement-Level Trigger:
Statement-Level Trigger: see the PowerPoint Tutorial: storage process 1.ppt [page19] This trigger is only executed once during database operations.
Note:
. The of option in update is used to specify the columns to be modified by the statement.
. Use the replace option when the trigger to be created already exists.

1. // Example 1: Before trigger:
2. Create or replace trigger delemp
3. Before delete on EMP
4. Begin
5. If (to_char (sysdate, 'dy ') in ('saturday', 'sunday') or
6. To_number (to_char (sysdate, 'hh24') not between 8 and 18)
7. Then dbms_output.put_line ('it is not working time now. Please exit !!! ');
8. End if;
9. End;

[Trigger data dictionary]
SQL> select table_owner, table_name, trigger_body from user_triggers where trigger_name = 'delemp ';

1. // Example 2: After trigger:
2. Create or replace trigger insertemp
3. After insert on EMP // if it is before, it will be one fewer result than after.
4. Declare
5. V_empcount number (7 );
6. Begin
7. Select count (*) into v_empcount from EMP;
8. Dbms_output.put_line ('the total number of employees has reached: '| v_empcount |. ');
9. End;

 

1. // Example 3: Multiple trigger conditions
2. Create or replace trigger changeemp
3. Before delete or insert or update on EMP
4. Begin
5. If (to_char (sysdate, 'dy ') in ('saturday', 'sunday') or
6. To_number (to_char (sysdate, 'hh24') not between 8 and 18)
7. Then dbms_output.put_line ('it is not working time now. Please do not modify the data !!! ');
8. End if;
9. End;
11. // More perfect writing method:
12. Create or replace trigger changeemp
13. Before delete or insert or update on EMP
14. Begin
15. If (deleting and (to_char (sysdate, 'dy ') in ('saturday', 'sunday') or
16. To_number (to_char (sysdate, 'hh24') not between 8 and 18 ))
17. Then dbms_output.put_line ('it is not working time now, do not delete data! ');
19. Elsif (updating and (to_char (sysdate, 'dy ') in ('saturday', 'sunday') or
20. To_number (to_char (sysdate, 'hh24') not between 8 and 18 ))
21. Then dbms_output.put_line ('it is not working time now, do not update data! ');
23. Elsif (inserting and (to_char (sysdate, 'dy ') in ('saturday', 'sunday') or
24. To_number (to_char (sysdate, 'hh24') not between 8 and 18 ))
25. Then dbms_output.put_line ('it is not working time now, do not insert data! ');
27. End if;
28. End;

 

6. Create a row-Level Trigger:
Level Trigger: adds the option for each row to trigger the trigger on each row.

1. Notes for creating a row-Level Trigger:
(1) In a row-Level Trigger, adding old before the column name indicates that the column is modified, and adding new indicates that the column is modified.
(2) When cited in PL/SQL, add a colon to the front.
 

1. [Example 4: Row-Level Trigger] // all rows must be operated.
2. Create or replace trigger updateemp
3. Before update on EMP
4. For each row
5. Begin
6. Dbms_output.put_line (: Old. Sal | '--------->' |: New. Sal );
7. End;

 

1. [Example 5: Save historical data. This method is very important to save the historical data of key tables.]
2. Create or replace trigger changeemp
3. Before update or delete on EMP
4. For each row
5. Begin
6. Insert into oldemp (empno, ename, job, hiredate, Sal)
7. Values (: Old. empno,: Old. ename,: Old. Job, sysdate,: Old. Sal );
8. End;
10. SQL> Create Table oldemp
11. As select empno, ename, job, hiredate, Sal from EMP where 1> 2;

1. [Example 6: modify a foreign key]
2. Create or replace trigger updatedept
3. After update on Dept
4. For each row
5. Begin
6. Update EMP
7. Set EMP. deptno =: New. deptno
8. Where EMP. deptno =: Old. deptno;
9. End;

1. [Example 7: deleting Foreign keys and related data]
2. Create or replace trigger deletedept
3. Before delete on Dept
4. For each row
5. Begin
6. Delete from EMP where deptno =: Old. empno;
7. End;

 

VII. Trigger Management
1. invalidate the trigger:

1. SQL> alter trigger name disable; // invalid
2. SQL> alter trigger name enable; // takes effect
4. SQL> ALTER TABLE table name disable all triggers; // All triggers on a table are invalid.
5. SQL> ALTER TABLE table name enable all triggers; // make all triggers on a table take effect
7. SQL> drop trigger name; // delete a trigger;

 

　TriggerIs a special type of stored procedure, which is different from the stored procedure. A trigger is executed when an event is triggered. trigger events can be divided into three categories: DML events, DDL events, and database events, stored procedures can be called directly by the stored procedure name. When performing operations such as update, insert, and delete on a table,SQL ServerThe SQL statement defined by the trigger is automatically executed to ensure that the data processing must comply withSQL statementThe rule.

A trigger is a code block automatically executed when a specific event occurs. It is similar to a stored procedure, but users cannot directly call them.

　　Trigger Function

1. Allow/restrict table modifications

2. automatically generate a derived column, such as an auto-increment Field

3. Forced Data Consistency

4. provide audit and logging

5. Prevent invalid transaction processing

6. Enable complex business logic

　Trigger type

There are four types of triggers: 1. data Control Language (DML) triggers, 2. replace (instead of) Trigger, 3. data Definition Language (DDL) triggers, 4. database event triggers.

Data manipulation language (DML) triggers: DML triggers are defined on tables and created on tables. When writing a DML trigger, a trigger triggered by a DML event has two elements: 1. Determine the trigger table, that is, the table on which the trigger is defined. 2. determine the trigger event. The DML trigger event includes insert, update, and delete. The trigger is replaced by the instead of trigger, which is created on the view, it is used to replace the delete, insert, and modify operations on the view. The Data Definition Language (DDL) Trigger, referred to as the DDL trigger, is defined in the mode. The trigger event is the creation and modification of the data object; database event triggers are defined in the entire database or mode. A trigger event is a database event.

　　The syntax for Oracle to generate database triggers is:

Create [or replace] trigger name

{Before | after | instead of} trigger event 1 [or trigger event 2...]

On Table Name

When trigger Condition

[For each row]

Declare

Declaration

Begin

Subject

End;

　Where:

Trigger name: name of the trigger object. Because the trigger is automatically executed by the database, the name is only a name and has no substantive purpose. A trigger can be triggered by multiple different data manipulation language operations. In triggers, inserting, deleting, and updating predicates can be used to differentiate different data operation languages. These predicates can be used as judgment conditions in the IF branch Condition Statement.

Trigger time: specifies when the trigger is executed. This value is recommended. The trigger time can be before or after, indicating that the trigger action takes place before or after the DML statement is executed. Determine the trigger level. There are two types: Statement-Level Trigger and row-Level Trigger. Statement-level triggers indicate that SQL statements trigger only once, and row-level triggers indicate that each row affected by SQL statements must be triggered once.

Before: triggers are executed before database actions. During SQL statement execution, if a row-level before trigger exists, the SQL statement is executed before each row, you must execute a row-level before trigger before performing operations on the row. If a row-level after trigger exists, the SQL statement will execute the row-level after each row operation.

After: indicates that the logtail is executed after the database action. If a statement-level after trigger exists, the last statement-level after the SQL statement is executed is the statement-level after trigger.

Trigger event: Specifies which database actions will trigger this trigger, which is an insert, delete, or update event. The event can appear in parallel and be connected using or;

Insert: This trigger is triggered when the database is inserted;

Update: This trigger is triggered when the database is modified;

Delete: This trigger is triggered when a database is deleted.

Table Name: The table where the database trigger is located.

For each row: indicates that the trigger is a row-Level Trigger. If it is omitted, it is a statement-Level Trigger, which is executed once for each row of the table.

The trigger creator or a person with the permission to drop any tirgger can delete the trigger. The syntax for deleting a trigger is as follows:

Drop tirgger trigger name

You can use commands to set the available status of a trigger so that the trigger is temporarily closed or re-opened. That is, when the trigger is temporarily unavailable, it can be set to an invalid status and re-opened during use. The command syntax is as follows:

Alter trigger name {disable | enable}

Disable indicates that the trigger is invalid, and enable indicates that the trigger takes effect.

Similar to the stored procedure, triggers can use show errors to check compilation errors.

If multiple triggers are defined as triggering at the same time and event, and the last defined trigger is valid, the last defined trigger is triggered, and other triggers are not executed. You cannot use the commit, rollback, and savepoint statements in the trigger body, or directly or indirectly call stored procedures containing the preceding statements. When defining a trigger, consider the preceding situations and decide the trigger type based on specific needs.

　Trigger Function

The trigger is mainly used to achieve complex integrity and data consistency that cannot be guaranteed by the primary key and foreign key. In addition, there are many different triggersFunction:

(1) Enforce Restriction)

Triggers implement more complex constraints than check statements.

(2) tracking change auditing changes

Trigger can detect operations in the database, so that you cannot specify updates or changes in the database without permission.

(3) cascaded operation ).

The trigger can detect operations in the database and automatically cascade operations to affect the entire database. For example, a table trigger contains data operations (such as deletion, update, and insertion) on another table, which triggers the table trigger.

(4) Stored Procedure invocation ).

In response to the database update trigger, the sender can call one or more stored procedures, or even operate outside the DBMS (Database Management System) itself by calling external processes.

It can be seen that triggers can solve advanced business rules or complex behavior restrictions and achieve custom records. For example, a trigger can identify the differences between the statuses of a table before and after data modification, and perform some processing based on the differences. In addition, multiple triggers of the same type (insert, update, and delete) of a table can process the same data operation in a variety of ways.

Generally, the trigger performance is relatively low..

When a trigger is run, the system spends most of its processing time referring to other tables because these tables are neither in memory nor on Database devices, deleting and inserting tables are always in memory. It can be seen that the position of other tables referenced by the trigger determines the duration of the operation.