Oracle BASE <5>--triggers

First, Trigger

　　a trigger is a block of code that executes automatically when a particular event occurs. For example, a record is added to the log table each time an action is made to add or delete an employee table. triggers and stored procedures are distinguished by: Triggers are automatically executed according to certain conditions, and stored procedures are manual strips.

(a) Type of trigger:

　　　1. Row-level trigger: Triggers when manipulating data rows. Row-level triggers execute once for each row affected by a DML statement

　　Row-level triggers can create row-level triggers by specifying a For each row clause in the CREATE TRIGGER command

2. Statement-level triggers: Triggers that are triggered once by a DML statement. Row-level triggers and statement-level triggers also become DML triggers together.

　　　　( statement trigger is the default type of trigger created by the CREATE TRIGGER command )

3. INSTEADOF Trigger: The trigger is mainly defined on the view.

4. Mode trigger: User transaction trigger.

5. Database trigger: Creates a trigger on a database event.

(ii) A trigger can be divided into three parts:

　　　1. Trigger statement: The trigger statement is the event that can cause a trigger, that is, a DML statement such as INSERT, DELETE, UPDATE, or a DDL statement or database event that executes on a schema object on a table or view. All events that can cause a trigger are statements that are triggers.

2. Trigger limit: The trigger restriction condition contains a Boolean expression that must be true to activate the trigger, and if the value is False or unknown, the trigger action is not run.

3. Trigger action: The body of the trigger when the trigger action contains some SQL statements and code that runs when the trigger is executed and the trigger restriction condition is true. Row-level triggers allow statements in a trigger operation to access the column values of a row.

　　

( c) The common functions of the trigger are as follows:

1. Allow/restrict the modification of the table.

2. Automatic generation of derived columns.

3, enforce data consistency.

4. Enforce complex integrity constraints.

5, provide audit and log records.

6, to prevent invalid event handling.

7. Start the complex business logic.

Because triggers are an object that monitors data, the data and tables in the database are monitored at any time, resulting in poor performance with triggers. This is particularly important.

Second, create the trigger:

Grammar:

　　CREATE [OR REPLACE] TRIGGER trigger_name--Name of the trigger

{before | After-fires a trigger before or after an event occurs

| INSTEAD of}-- represents the execution of the trigger code in place of the event that caused the trigger to invoke

{INSERT | DELETE | udpate | [of Column[,column]]}

[OR {INSERT | DELETE | UPDATE [of Column[,column]]}

on [schema.] Table_or_view_name

[Referencing [NEW as New_row_name] [old as Old_row_name]]

[For each ROW]

[When (condition)]

[DECLARE

Variable_declation]

BEGIN

statements;

[EXCEPTION

Exception_handlers]

End[trigger_name];

Description

　　Trigger_name: Name of the trigger

Before | After: Indicates that the trigger is activated before or after the event occurs

INSTEAD of: Indicates that the trigger code can be executed in place of the event that caused the trigger to invoke.

INSERT | DELETE | Udpate: Specifies the type of database operation that constitutes the trigger event, and update can also specify a list of columns.

Referencing: Specifies additional names for new rows (to be updated) and old lines (before update), and defaults to the default is new and older.

　　(old value: the statement that existed before the DML statement.) UPDATE and delete usually refer to old values)

(The new value NEW:DML the data value created) is referenced by adding a dot after old or "new", and then after the field name.

Table_or_view_name: The name of the table or view that the trigger monitors.

For each row: Indicates whether a trigger is executed on each of the affected rows, a row-level trigger. If this statement is not used, it is a statement trigger, and the statement is a row-level trigger.

When (condition): a condition that restricts the execution of a trigger, which can include checking for old and new data values.

DECLARE: Declaring local variables used by triggers

Statements: The PL-SQL statement executed by the trigger.

Exception_handlers: Exception handling code.

Three. Management of triggers

(1) Status of the trigger

　　　　　Desabled: Status not Enabled

Enabled: Active

Grammar:

　　Alter TRIGGER trigger_name {desabled | enabled}; --Enable and disable triggers

Drop Trigger trigger_name; --Delete trigger

Sql> DESC user_triggers; --View triggers

Example 1: Generating an AutoNumber (row-level trigger)

--Create a test table, student table
CREATE TABLE Student
(
stu_id number CONSTRAINT pk_id PRIMARY KEY,
Stu_name VARCHAR2 () not NULL,
Stu_phone VARCHAR2 () not NULL
);

--Create a sequence, starting with 1, adding 1 each time
CREATESEQUENCE seq_student
STARTWith1       -- sequence start, count from 1
    INCREMENT by 1     --< Span style= "color: #008080;" > 1
    nomaxvalue         per increase -- Not set the maximum value
    nocycle             -- add only, do not loop
    CACHE 20;          Span style= "color: #008080;" >-- cache

--Create a trigger

CreateOrReplaceTriggerStudent_insert_seq
BeforeInsert on student -- before inserting
For each row - row-level trigger
Begin
: new.stu_id:= seq_student.nextval; -- Set the inserted stu_id property to the next value of the sequence- -action of the trigger
end Student_insert_seq;

--Inserting data
INSERTinto student (Stu_name,stu_phone)VALUES (' Zhang San ',' a ');
INSERT into student (Stu_name,stu_phone) VALUES (' John Doe ','+');

-- query, Data insertion success
SELECT * from student

Example 2: Implementing the table Operation log

--Create a new log table sequenceCREATE SEQUENCE logs_id_squ INCREMENTBy1STARTWith1 MAXVALUE9999999Nocycle NOCACHE;--Log tableCREATETABLE logs (log_id number (10) primary key Log_ TABLE varchar2 (10) not null10number (10varchar2 (15) );  

--Create a log trigger: table-level triggerCreateOrReplaceTriggerTrig_log afterINSERTORUPDATEORDELETEOnEmpForEach rowDECLAREV_usernameVARCHAR2 (20);BEGINSELECTUSERInto V_usernamefrom dual;--Get Current UserIF INSERTINGThenINSERTinto logsVALUES (Logs_id_squ.nextval,‘Emp‘,‘Insert‘,: New. Empno,sysdate,v_username); elsif UPDATINGThenINSERTinto logsVALUES (Logs_id_squ.nextval, ' emp " ' ,:new. Empno,sysdate,v_username); end if;end trig_log;                

Two: The difference between a trigger and a stored procedure

1. Triggers are automatically executed (automatic execution in some cases) cannot be displayed for debugging

A trigger is an object trigger flaw that monitors data: low performance

2. Stored procedures can be called

Oracle BASE <5>--triggers