Shulou(Shulou.com)05/31 Report--

This article is to share with you what the oracle trigger is, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

What is an Oracle trigger?

First of all, let's take a look at what a trigger is, a sql statement that a trigger implicitly executes when it triggers an event, and the trigger cannot receive parameters. Oracle triggers trigger insert, update, delete operations on the database or similar operations on the view in the Oracle database, as well as some system events, such as closing or opening the database.

What are the categories of triggers?

(1) data manipulation language (DML) trigger: a trigger created on a table and triggered by DML time

(2), insteadof trigger: created on the view to replace the view to add, modify and delete operations.

(3) data definition language (DDL) trigger: defined on the schema, the trigger event is the creation and modification of database objects.

(4) Database system trigger: defined on the whole database, the trigger time is the operation of the database, such as the startup and shutdown of the database.

What are the components of a trigger?

(1), triggered events: that is, the circumstances under which trigger is triggered, such as insert, update, delete.

(2) the time of trigger: that is, whether the trigger is before the trigger event (before) or after the trigger event (after), and it is also the order of the triggered event and the trigger subject.

(3) trigger itself: that is, the action to be performed by the trigger when it triggers the event, for example: pl/sql block.

(4) the frequency of trigger: indicates the number of times the action defined in the trigger has been performed. Statement-level triggers and row-level triggers. A statement-level trigger means that when an event occurs, the trigger executes only once. Row-level triggers, on the other hand, execute a separate trigger for each row of data affected by the operation when an event occurs.

Oracle trigger example:

-- create tables

-- employee table

CREATETABLEemp (

Emp_IDNUMBER (10) PRIMARYKEY

Emp_nameVARCHAR2 (20) NOTNULL

Emp_sexVARCHAR2 (10)

Emp_addressVARCHAR2 (15)

);

-- log table

CREATETABLElogs (

LOG_IDNUMBER (10) PRIMARYKEY

LOG_TABLEVARCHAR2 (10) NOTNULL

LOG_DMLVARCHAR2 (10)

LOG_KEY_IDNUMBER (10)

LOG_DATEDATE

LOG_USERVARCHAR2 (15)

);

-- create a sequence

CREATESEQUENCElogs_id_squINCREMENTBY1

STARTWITH1MAXVALUE9999999NOCYCLENOCACHE

CREATESEQUENCEemp_id_squINCREMENTBY1

STARTWITH1MAXVALUE9999999NOCYCLENOCACHE

What are the restrictions on triggers?

(1) the character length of createtrigger statement cannot exceed more than 32kb.

(2) the select statement in the trigger can only be select.into. Structure, or the select statement used to define the cursor

(3) Database transaction control statements cannot be used in triggers, such as commit, rollback, savepoint statements.

(4) procedures or functions called by triggers cannot use database transaction control statements

(5) lang and langrow types cannot be used in triggers.

(6) triggers can refer to the column values of the lob type class, but cannot modify the values in the lob column by: new

(7) restrictions on table receiving to table constraints involved in triggers

This is what oracle triggers are, and the editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.