Oracle Learning (15) triggers

Triggers enable consistency and completeness of data between tables. When a base table is modified, the corresponding trigger is automatically executed. Operations on table data are inserted, modified, and deleted, and the corresponding triggers for data maintenance are roughly three kinds of inserts, updates, and deletes.

There are three types of triggers:

(1) DML trigger: triggered when a DML operation is performed on the table.

(2) Alternative triggers: because it is not possible to perform DML operations directly on a view of more than two tables in Oracle, an alternative trigger is given, which is an Oracle-specific processing method for view operations.

(3) system triggers: It can be triggered in an Oracle database system event, such as the database is turned on and off.

Keywords in triggers:

There are two very important keywords in the trigger, one is old, the other is new,old used to decorate the value before the operation is completed, new is used to decorate the finished value, can represent a row. In addition, only row-level triggers can use these two keywords.

Creating a trigger using SQL statements

1, use SQL to create a trigger syntax format:

create [or Replace] trigger [schema.] Trigger_name  
{before | after | instead of}  
{delete [or insert][or update [of Column,... n]]}  
[schema.] table_name | View_name  
[For each row [when (condition)]]  
sql_statement[,... N]

Where the Before: Trigger executes before the action; After: The trigger executes after the action; instead of: Specifies to create an alternate trigger.

Delte, INSERT, UPDATE: Specifies that the event is triggered, and that multiple events are connected by or.

Column: Specifies which columns to perform an update trigger on.

For each row: Indicates that the trigger is row-level, and only row-level triggers can use the old and new keywords.

Sample code:

Create or replace trigger Moduleinserttrigger after insert on
t_module  
begin
    Dbms_output.put_line (' the Name is: ' | | : New.name);  
End

2, create the limit of the trigger

(1) Trigger code size must be less than 32KB

(2) A valid statement in a trigger can include a DML statement, but not a DDL statement; rollback, commit, and savepoint are also not available. However, you can use Create, alter, DROP TABLE, and ALTER...COMPILE statements for system triggers.

(3) long, long raw, and lob restrictions:

A. Cannot insert data to long or long raw

B. Data from long or long raw can be converted to a character type, but not more than 32KB

C. You cannot declare a variable using long or long raw

D. Not available in long or long raw columns: new and: Parent

E. In LOB: The new variable cannot be modified

(4) The restriction of referencing the package variable: If the UPDATE or DELETE statement detects the current update conflict, Oracle performs rollback to savepoint and restarts the update.