Oracle trigger creation and functions

Source: Internet
Author: User

After learning, we are familiar with creating Oracle sequences in c. Today, we mainly introduce how to create Oracle triggers and the functions and syntax of Oracle triggers, in addition, we can use specific examples to gain a deeper understanding.

1. create table t1: create table t1 (id number, name nvarchar (8 ));

2. CREATE an Oracle SEQUENCE: create sequence t1_id increment by 1 start with 1 MAXVALUE

3. Create an Oracle trigger:
Create trigger tig_insert_t1
Before insert on "YINZQ". "T1"
Begin
If (: new. id is null) then
Select t1_id.nextval into: new. id from dual; // where: new. id indicates the column of the new row in Table t1.
End if;
End;

4. Oracle trigger function
A trigger is a code block automatically executed when a specific event occurs. Similar to a stored procedure, a trigger differs from a stored procedure in that a stored procedure is explicitly called by a user or application, and a trigger cannot be called directly.
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

5. There are two types of trigger triggers: after and before.

Oracle trigger Syntax:
CREATE [or replace] TIGGER trigger name trigger time trigger event
ON Table Name
[For each row]
BEGIN
Pl/SQL statements
END

1) 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.

2) trigger time: specifies when the trigger will be executed. This value is optional:
Before --- indicates that the trigger is executed before the database action;
After --- indicates that the logpilot executes the database action.

3) trigger event: Specifies which database actions will trigger this trigger:
Insert: This trigger is triggered when the database is inserted;

Example: Enable Oracle to implement auto-increment Fields
Step: first create a sequence and then create an Oracle trigger!
Cata0 is the table name, And cata0_id is the field that requires auto-increment!
Create sequence SEQ_cata0
Increment by 1
Start with 1
Max value 9999999
Create trigger TRG_cata0 BEFORE
Insert on cata0
For each row begin
INTO: NEW. cata0_ID
From DUAL;
End TRG_cata0;
/***** @ Param stname: do not create a sequence table. Multiple tables are separated by commas **/
Create or replace procedure PROC_CREATE_SEQ_TRIG (stname in VARCHAR2)
AS
STRSQL VARCHAR2 (4000 );
TABLENAME VARCHAR2 (50 );
Pid varc ......

  1. Using Oracle triggers
  2. Do you know about Oracle triggers?
  3. Oracle triggers
  4. Oracle administrator creation procedure Overview
  5. Sample Code for creating the Split and Map functions in Oracle

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.