The main contents of this article are as follows:
1. Trigger type
1.1 DML triggers
1.2 Alternative triggers
1.3 System triggers
2. Trigger composition
3. Create a Trigger
3.1 Trigger Trigger Order
3.2 Creating a DML Trigger
3.3 Creating an alternative (INSTEAD of) trigger
3.3 Creating a System Event trigger
3.4 System trigger Event properties
3.5 Using Trigger predicates
3.6 Recompile triggers
4. Delete and enable triggers
5. Triggers and data dictionaries
6. Examples of application of database triggers
--------------------------------------------------------------------------------------------------------------- ------------------
Triggers are a technique that is provided by many relational database systems.
in Oracle systems, triggers are similar to procedures and functions, there is a PL/SQL block that declares, executes, and processes the exception.
1 Trigger Type
triggers are stored as separate objects in the database, unlike stored procedures and functions, stored procedures and functions require the user to display the call before execution,
and the trigger is run by an event to start。 That is, the trigger is when an event occursAuto-run implicitly。 Andtriggers cannot receive parameters。 So running the trigger is called trigger or ignition (firing).
Oracle Events
refers to an insert, update, and delete operation on a database table or a similar operation on a view。 Oracle extends the capabilities of triggers to trigger Oracle, such as database startup and shutdown. So the trigger is often used to complete the constraints of complex business rules that are difficult to complete by the integrity constraints of the database, or to monitor various operations on the database to realize the function of auditing.
1.1 DML triggers
Oracle can trigger on DML statements and cantriggering before or after a DML operation, and cantrigger on each row or statement operation。
1.2 Alternative triggers
Because in Oracle, it is not possible to operate directly on a view that is established by more than two tables. Therefore, an alternative trigger is given. It is Oracle 8 dedicated toto perform a view operationA method of processing.
1.3 system triggers
ORACLE 8i provides a third type of trigger called a system trigger. It can be used in Oracle databasein the event of a systemTriggering, such as the start and shutdown of an Oracle system.
2. The trigger consists of:
1). Triggering Events: The event that caused the trigger to be triggered. For example:
DML Statements(INSERT, UPDATE, DELETE statements perform data processing operations on a table or view),
DDL Statements(such as Create, ALTER, DROP statements creates, modifies, deletes schema objects in the database),
Database System Events(such as system startup or exit, exception errors), user events (such as logging in or exiting the database).
2). Trigger Time: The trigger is the trigger eventbefore (before) or later (after) triggers, that is, the trigger event and the order of operation of the trigger.
3). Trigger Action: The purpose and intent of the trigger after it is triggered is exactly what the trigger itself is going to do. For example, PL/SQL blocks.
4). Trigger Object：
include tables, views, schemas, databases。 Trigger actions are performed only if a trigger event that matches the trigger condition occurs on these objects.
5). Trigger Conditions: A logical expression is specified by the When clause. Only if the value of the expression is true, the trigger event is encountered to perform the triggering action.
6). Trigger Frequency: Describes the number of times the action defined within the trigger is executed. That
statement-level (statment) and row-level (ROW) triggers。
statement-level (STATEMENT) triggers: means that the trigger executes only once when a trigger event occurs;
line-level (ROW) triggers: A trigger is executed once for each row of data affected by the operation when a triggering event occurs.
When writing triggers, you need to be aware of the following points:
1).
trigger does not accept parameters。
2). There can be up to 12 triggers on a table, but only one for the same time, the same event, and the same type of trigger. And there can be no contradiction between the triggers.
3). On a table
More triggersTo the DML operation on the table.
The greater the performance impact。
4).
trigger Max is 32KB。 If you do, you can create a procedure and then invoke it in a trigger with a call statement.
5). On the Trigger'sThe
execution Section can only be used with DML statements(SELECT, INSERT, UPDATE, DELETE), you cannot use DDL statements (CREATE, ALTER, DROP).
6). In the triggercannot contain transaction control statements(Commit,rollback,savepoint).
because the trigger is part of the trigger statement, when the trigger statement is committed, rolled back, the trigger is also committed, rolled back。
7).
any procedures, functions that are called in the body of the trigger cannot use transaction control statements。
8). Inany long and BLOB variables cannot be declared in the trigger body。 The new value, old, and both cannot be any long and BLOB columns in the table.
9). Different types of triggers (such as DML triggers, INSTEAD of triggers, system triggers) have a large difference in syntax format and function.
create [or replace] trigger Trigger_name
{before | after}
{Insert | delete | update [of column [, Column ...]}
[OR {INSERT | DELETE | UPDATE [of column [, Column ...]} ...]
on [schema.] table_name | [Schema.] View_name
[Referencing {old [as] old | NEW [as] new| Parent as parent}]
[For each ROW]
[When condition]
Pl/sql_block | Call procedure_name;
which
--before and after indicate that the trigger's trigger timing is either pre-trigger or post-trigger, and the pre-trigger is the trigger that is currently created before the triggering event is executed, and the trigger is the trigger that is currently created after the trigger event is executed.
--
the For each row option describes the trigger as a row trigger。 The difference between a row trigger and a statement trigger is that a row trigger requires that when a DML statement operation affects multiple rows of data in a database, the trigger is activated for each row of data, as long as they conform to the trigger constraint, and the statement trigger takes the entire statement action as a trigger event, and when it meets the constraints, Activates the trigger once. When the For each ROW option is omitted, the before and after triggers are statement triggers, whereas the instead OF triggers are only row triggers.
--
referencing clause description related name, the corresponding name can be used in the PL/SQL block and when clause of a row trigger to refer to the current new, old column value, with the default correlation name of both OID and new. When you apply a related name in a PL/SQL block of a trigger, you must precede them with a colon (:), but you cannot add a colon in the When clause.
-The When clause describes the trigger constraint condition. When Condition is a logical expression, it must contain a correlation name, not a query statement, or a PL/SQL function. The trigger constraint specified by the When clause can only be used in before and after row triggers, not in instead of row triggers and other types of triggers.
The stored procedure to execute when a base table is modified (INSERT, UPDATE, DELETE) is automatically triggered based on the base table changes it is attached to, so it is independent of the application, and database triggers are used to guarantee the consistency and integrity of the data.
You can create up to 12 types of triggers per table, which are:
Before insert
Before insert for each row
After insert
After insert for each row
Before update
Before upate for each row
After update
After update for each row
Before delete
Before delete for each row
After delete
After delete for each row
2.1 Trigger Trigger order
1. Execute before statement level trigger;
2. For each line affected by the statement:
2.1. Execute before row-level triggers
2.2. Executing DML statements
2.3. Executing after-row triggers
3. Executing after-statement-level triggers
2.2 Creating a DML trigger
The trigger name is not the same as the name of the procedure and the package.It's a separate name space., so the trigger name can have the same name as the table or procedure, but the trigger name cannot be the same in one pattern.
limitations of DML triggers
1).
the character length of the CREATE trigger statement text cannot exceed 32KB；
2).
The SELECT statement in the trigger body can only be a select ... Into ... Structure, or the SELECT statement that is used to define the cursor.
3). In the trigger
cannot use database transaction control statement COMMIT; ROLLBACK, Svaepoint statement；
4). A procedure or function called by a trigger cannot also use a database transaction control statement;
5). In the trigger
cannot use long, long RAW type；
6). The column values of the LOB type column can be referenced within the trigger, but cannot be modified by: NEW to modify the data in the LOB column;
basic points for DML triggers
1. Trigger Timing: Specifies the trigger time for the trigger. If specified as before, it is triggered before DML operations are performed to prevent certain error actions from occurring or to implement some business rules, or, if specified as after, to trigger after DML operations to log the operation or to do some post-processing.
2. Triggering Events: The event that caused the trigger to be triggered, that is, the DML operation (INSERT, UPDATE, DELETE). Can be either a single trigger event or a combination of multiple triggering events (only with or logical combinations, and not with and logic combinations).
3. Conditional predicates: When you include a combination of multiple triggering events (INSERT, UPDATE, DELETE) in a trigger, you need to use the following conditional predicate provided by Oracle in order to perform different processing for different events separately.
3.1). INSERTING: Evaluates to True when the trigger event is insert, otherwise false.
3.2). UPDATING [(column_1,column_2,..., column_x)]: If the column_x column is modified when the triggering event is update, then the value is true, otherwise false. where column_x is optional.
3.3). DELETING: When the trigger event is delete, the value is true, otherwise false.
4. Postback object: Specifies which table or view the trigger is created on.
4.1). Trigger type: Is statement-level or row-level trigger.
4.2). Trigger condition: Specifies a logical expression by the When clause, allowing only the trigger condition to be specified on a row-level trigger, specifying the list of columns following updating.
Issue: When a trigger is triggered, the value of the column in the record being inserted, updated, or deleted is used, and sometimes the pre-and post-column values are used.
Implementation:: The NEW modifier accesses the value of the column after the operation is completed
: Old modifier to access the value of the top of the operation
Features: INSERT UPDATE DELETE
Old NULL actual value actual value
NEW actual value actual value NULL
Instance:
Prepare the data:
--Prepare Table CREATE TABLE p_table (p_no varchar (5) Not NULL PRIMARY KEY, --part number P_name VARCHAR (a) UNIQUE, --part name P_weight NUMERIC (6,2), --weight p_city VARCHAR () ); --origin--random insertion point in the table select * from p_table for UPDATE;
Example 1, create a Helloword-level update
row-level triggers (with for each row, no statement triggers)
--An UPDATE trigger of HelloWorld level create or Replace trigger Update_triggerafter update on p_tablefor each row --if removed, is a statement trigger, there is no output of Hello for each line: Only the last execution of the Begin Dbms_output.put_line (' Hello ... ') end;--the weight of p_city=2 to 10, and modified how many data on the output how many helloupdate p_table set p_weight = P_weight + WHERE p_city=2;
Output results
--HelloWorld of triggers: Write a trigger to print ' HelloWorld ' Create or replace trigger P_triggerafter insert on P_ta when inserting records into the p_table table Blefor each rowbegin dbms_output.put_line (' HelloWorld ') and End;insert into P_table VALUES (' 7 ', ' 7 ', 7, ' 1 ');
Example 3, using: New,: Old modifier
--Use: New,: Old modifier create or replace trigger New_triggerafter update on p_tablefor each rowbegin dbms_output.put_ Line (' Old weight: ' | |: Old.p_weight | | ', New weight: ' | | : new.p_weight); end;--let P_city=1 weight add 0.1update p_table Set p_weight = P_weight + 0.1 WHERE p_city=1;
Output results
Example 4,Write a trigger to back up the corresponding record in the P_table_bak table when the p_table record is deleted
--Prepare table CREATE TABLE My_emp_bak as SELECT * from p_table where 1 = 2;create or replace trigger P_table_bakbefore delete on P_ Tablefor each row begin inserts into My_emp_bak values (: Old.p_no,: Old.p_name,: Old.p_weight,: old.p_city) ; end; DELETE from P_table; SELECT * from My_emp_bak for UPDATE;
The result is that p_table has no value, and P_table_bak has a value
oracle--Trigger
