ORACLE_DML Trigger ____oracle

Trigger

A Brief introduction

A trigger is a procedure written by pl/sql or Java [procedure] that is triggered when a table or view is modified or a user action is made or the database system action is run [fire].

Trigger [Trigger] is triggered to run when the following actions occur:

· The user submits a DML statement for a particular table or view (Insert,update,delete)

· specific scenarios [schema]/user or database any scheme/user submits DDL statements (mainly to CREATE and ALTER)

· A specific scenario/user or any scheme/user in the database commits a database event [event], such as login/exit [Logon/logoff], error [errors], or Startup/shutdown [Startup/shutdown]

Triggers are similar to stored procedures [Stored procedure]. A database trigger can contain Java statements, or SQL statements and pl/sql structures, or other stored procedures. Triggers differ from stored procedures in how they are invoked. Stored procedures are explicitly invoked by a user, an application, or a trigger. Triggers are implicitly triggered by Oracle when an event [triggering event] occurs, regardless of the application of the trigger to the user who is connected to the database and what the user is using.

The events that can cause triggers to be invoked include:

· A DML statement that modifies table data (insert,update, or DELETE)

· DDL statements

· Start [startup], close system events such as [shutdown] or error messages ["Systems event]"

· logon [logon] and exit [logoff] and other user events

Two Trigger Composition:

A trigger [Trigger] contains 3 basic components:

· Trigger event [triggering event] or TRIGGER statement [triggering statement]

· Trigger constraint [trigger restriction]

· Trigger action [Trigger action]

Three. Type of trigger

1. Statement triggers

2, row triggers

3, INSTEAD of Trigger

4. System Condition Trigger

5. User event triggers

Four. Triggering sequence of triggers

1. Execute before-level statements

2. For each row that is affected by the statement

Before row level/execute DML statement/execute after row level trigger

3. Execute AFTER statement trigger

Five Attention

1. Triggers do not accept parameters.

2. There can be up to 12 triggers on a table, but only one at the same time, the same event, and the same type of trigger. And there can be no contradiction between the triggers.

3. The more triggers on a table, the greater the performance impact on the DML operations on the table.

4. The maximum trigger is 32KB. If you do, you can create a procedure and then invoke it in a trigger with a call statement.

5. You cannot use DDL statements (CREATE, ALTER, DROP) with DML statements (SELECT, INSERT, UPDATE, DELETE) in the execution part of the trigger.

6. A trigger cannot contain a transaction control statement (Commit,rollback,savepoint). Because triggers are part of the trigger statement, triggers are committed and rolled back when the trigger statement is committed and backed up.

7. No transaction control statements can be used for any procedure or function that is invoked in the trigger body.

8. You cannot declare any long and BLOB variables in the trigger body. The new value, the old value, and the OID cannot be made to any long and BLOB columns in the table.

9. Different types of triggers (such as DML triggers, INSTEAD of triggers, system triggers) have a significant difference in syntax format and function

Six Limitations of DML triggers

The character length of the 1.CREATE trigger statement text cannot exceed 32KB;

2. The SELECT statement within the trigger body can only be select ... Into ... Structure, or the SELECT statement used to define the cursor.

3. Cannot use database transaction control statement COMMIT in trigger; ROLLBACK, Svaepoint statement;

4. A procedure or function invoked by a trigger cannot use a database transaction control statement;

5. A long, long RAW type cannot be used in triggers;

6. You can reference the column value of a LOB type column within a trigger, but you cannot modify the data in the LOB column by: NEW;

ALTER Tigger Trigger_name DISABLE | ENABLE, available not available

Seven Two demo and detailed

----1. Row triggering

Create or replace trigger Biufer_abook_bookname---Created

Before insert or update or delete----before DDM

Bookmade---column name

On Abook----table name

Referencing old as Old_value new as New_value

For each row----row triggers

When (New_value.bookmade <> ' Xinhua ' and New_value.bookmade <> ' people ')

Begin

: New_value. Bookmade: = ' Xinhua ';

End

---2. Statement triggering

Create or Replace Trigger Biufer_abook_after

After insert or update or delete by bookname on Abook

Begin

If updating then

Dbms_output.put_line (' modification ');

elsif deleting Then

Dbms_output.put_line (' delete ');

elsif inserting Then

Dbms_output.put_line (' Insert ');

End If;

End

-----3.instead of triggers < views >

Create or replace Biuger_view_name

Instead of insert or update on view_name

Referencing new as N

For each row

Declare

..........

Begin

........

End

-----4. Mode triggers. Triggers can be set up at the schema-level operation

Create or Replace Trigger Biuger_schema

After drop on schema

Begin

Insert INTO .....

End

------5. Database-level triggers. is not associated with a particular table or view.

Create or Replace Trigger Trig_name

After startup on database

Begin

...........

End

Limitations of DML triggers

The character length of the 1.CREATE trigger statement text cannot exceed 32KB;

2. The SELECT statement within the trigger body can only be select ... Into ... Structure, or the SELECT statement used to define the cursor.

3. Cannot use database transaction control statement COMMIT in trigger; ROLLBACK, Svaepoint statement;

4. A procedure or function invoked by a trigger cannot use a database transaction control statement;

5. A long, long RAW type cannot be used in triggers;

6. You can reference the column value of a LOB type column within a trigger, but you cannot modify the data in the LOB column by: NEW;