Oracle's triggers

A trigger is a specific event that occurs when an automatic code block is executed automatically, and the user cannot control that the user can only control the triggered event, the triggered action, and the triggering process is performed automatically.

To define a trigger:

1. Name

2, Trigger time: is triggered before the event (before), or after the (after) trigger

3. Events: Insert (insert), delete (delete), update (updated)

4. Action target: on table_name

5. Referenced value: Old (previous value), new value

6, Scope: For each row (row level) or no line represents the statement level

7. Constraints: Optional, where constraints, cannot contain query statements, and cannot call PL/SQL functions

8. Main statement: Begin ...

Example:Create or replace trigger biufer_employess_emp_id

before insert or update of emp_id on Employees

　　　　　　referencing old as Old_value New as New_value

　　　　　　For each row

　　　　　　 when (new_value.emp_id =9)

　　　　　　Begin

　　　　　　　: new_value.age: = 26;

　　　　　End biufer_employess_emp_id;

The trigger is triggered when the following SQL statement is executed:

INSERT into Employees (emp_id,emp_name,position,age,address)
VALUES (9, ' Zhou Xun ', ' development Engineer ', NULL, ' Nanjing Road No. 99th ');

Triggers are automatically executed without notifying the user, when viewing the Employees table again:

　　　　9 Development Engineer, 26 Nanjing Road 99th

However, the trigger is not committed, and the trigger cannot use the COMMIT/ROLLBACK/DDL statement, which can only be submitted by the user (or other program)

Trigger type

1. Statement Trigger

A trigger for a specific statement or group of statements that executes on a view on a table or in some cases. Can be associated with an INSERT, delete, update, or combination. However, regardless of the combination, each statement trigger is activated only once for the specified statement. For example, the UPDATE statement trigger is called only once, regardless of how many rows are being update.

Example: A user who needs to DML a table is required to perform a security check to see if it has the appropriate privileges.

CREATE TABLE foo (a number);

Create Trigger Biud_foo

Before insert or update or delete on Foo

Begin

If user not in (' Donny ') then

Raise_application_error ( -20001, ' You don ' t has access to modify this table. ‘);

End If;

End Even Sys,system users cannot modify Foo table

2, line Trigger

Is the trigger that is activated for each row affected.

Example: Generating a self-increment sequence number for a primary key

CREATE TABLE foo (ID number,data varchar2 (20));

Create sequence foo_seq;

Create or Replace Trigger BIFER_FOO_PK

Before insert on Foo

For Eache Row

Begin

Select Foo_seq.nextval into:new.id from dual;

End

SQL statements

INSERT into foo (data) VALUES (' Donny ');//Add ID value automatically

INSERT into Foo VALUES (5, ' Chen '); Inserting the row 5 will be replaced with the value generated by the Foo_seq.nextval

Select *from foo;

3. Instead OF trigger

　　Create or Replace view company_name as select Emp_id,emp_name,position,age,address from scctt.employess;

　　update company_name Set emp_name = ' Tom ' where emp_id = 13;

Create or Replace Trigger Update_name_company_name

Instead of update on Company_Name

Begin

Update employess Set emp_id: =: new.emp_id,

Emp_name: =: New.emp_name,

where employess_id =: old.emp_id;

End

4. System Condition Trigger

System events: Database startup, shutdown, server error

Create trigger Ad_startup after startup on database

Begin

---do some stuff

End

5. User Event Trigger

User events: User logon, Logoff, Create/alter/drop/analyze/audit/grant/revoke/rename/trancate/logoff

Example: Record Delete Object

CREATE TABLE Drop_object (obj_name varchar2 (+), Obj_type varchar2 (+), dropped_on date); Log table

Create or Replace trigger Log_drop_trigger//Trigger

Before drop on Donny.schema

Begin

INSERT into Drop_object values (ora_dict_obj_name,ora_dict_type,sysdate);

End

SQL statements:

CREATE TABLE Drop_me (a number);

CREATE View Drop_me_view as select *from drop_me;

Drop View Drop_me_view;

drop table Drop_me;

Select *from droped_objects

Disabling and enabling triggers
ALTER TRIGGER <trigger_name> disable;
Alter trigger <trigger_name> enable;

The role of triggers

1. Allow/restrict the modification of the table

2. Automatically generate derived columns, such as self-increment fields.

3, enforce data consistency.

4. Provide audit and logging

5. Prevent invalid transaction processing

6. Enable Complex business logic

　　　　　　

Oracle's triggers