The creation and application of Oracle database--Trigger

I. Related content

1. Understand the concept, role, and type of triggers.

2. Practice the creation and use of triggers.

Second, the specific operation

Experimental

1. Use triggers to perform a security check on DML operations performed on the Scott.emp table, and only the Scott user logs on to the database before DML operations can be performed on that table. (in question 1th, user is a system function that returns the current user.) Use two single quotation marks in a string to denote a single quotation mark . ）

Requirements: Perform DML operations on the EMP table, respectively, with the system user and Scott user, and test the effect of the trigger.

(1) Create a trigger under the Scott user

Statement:

Create or Replace TriggerTRI_DM1 beforeInsert or Update or Delete  onscott.empbegin   if User <>'SCOTT'  ThenRaise_application_error (-20001,'You Don"'T has access to modify the This table.'); End if; End; /

：

(2) Connect to the system user and perform DML operations on the EMP table

Statement:

Conn System/orcl1234; Insert  into Scott.emp (empno,ename)  values(8888,'shenxiao');

：

(3) Connect to the Scott user and perform DML operations on the EMP table

Statement:

Conn Scott/Tiger; Insert  into Scott.emp (empno,ename)  values(8888,'shenxiao');

：

2. Use triggers for synchronous replication between tables and backup tables.

(1) Create a copy of the Scott.emp table under the Scott user employee.

Statement:

Conn Scott/tiger;

CREATE TABLE employee as SELECT * from Scott.emp;

：

(2) Create a DML trigger that enables synchronous replication between Scott.emp and employee two tables under the Scott user.

Statement:

Create or Replace TriggerDuplicate_emp AfterUpdate or Insert or Delete  onscott.emp forEach rowbegin   ifInserting Then     Insert  intoEmployeeValues(: new.empno,:new.ename,:new.job,:new.mgr,: NEW.HIREDATE,:NEW.SAL,:NEW.COMM,:NEW.DEP   TNO); elsif deleting Then     Delete  fromEmployeewhereEmpno=: Old.empno; Else     UpdateEmployeeSetEmpno=: New.empno,ename=: New.ename,job=: New.job, Mgr=: New.mgr,hiredate=: New.hiredate,sal=: New.sal,comm=: New.comm, Deptno=: New.deptnowhereEmpno=: Old.empno; End if; End; /

：

(3) Insert, delete and update the Scott.emp table.

Delete: Delete from scott.emp where empno=7934;

Insert:

Insert into Scott.emp (empno,ename,job,sal) VALUES (1111, ' Zhangsan ', ' ANALYST ', 2900)

Update:

Update scott.emp set sal=3900 where empno=1111;

(4) Query the records inserted, deleted and updated in the Scott.emp table and employee tables.

Statement: SELECT * from Scott.emp;

SELECT * from employee;

：

3. Create a trigger to log the time and the user to the Scott.emp table for DML operations.

(1) Establish log table Emp_log.

Statement: CREATE TABLE Emp_log (who varchar2 (+), when Date,oper varchar2 (10));

：

(2) Create a statement-level trigger on the EMP table to record the operations performed on the EMP table into the Emp_log table.

Statement:

 Create or Replace TriggerDm1_log AfterInsert or Update or Delete  onscott.empDeclareOper Emp_log.oper%type; begin     ifInserting ThenOper:='Insert'; elsif deleting ThenOper:='Delete'; ElseOper:='Update'; End if; Insert  intoEmp_logValues(User, Sysdate,oper); End; /

：

(3) Perform DML operations on scott.emp to view the data in the Emp_log table.

Statement:

Insert  into Scott.emp (empno)  values(1112); Select *  from Emp_log;

：

Exercise

1. Describe the types of triggers and trigger conditions in the Oracle database.

A: The type of trigger and the trigger conditions are shown in the following table:

by partition type	Types of Triggers	Trigger conditions
According to the triggered time	Before trigger, refers to a pre-trigger	Triggers are triggered before the trigger statement executes
	After trigger, refers to the post-event trigger	Triggers are triggered after the trigger statement executes
	INSTEAD of triggers, refers to alternative triggers	Trigger statement replaced by trigger action
Follow the triggered event	DML triggers	Triggers triggered when DML operations are performed on a table or view
	DDL triggers	Triggers triggered when DDL operations are performed in the database
	User event triggers	Triggers associated with a DCL operation or Logon/logoff operation performed by a user
	System event Triggers	Refers to triggers triggered by database system events

where DML triggers, according to the number of records affected by DML operations when triggered, can be divided into:

Row-level triggers: DML statements are called once per line of action, and row-level triggers are invoked once

Statement-level Triggers: statement-level triggers are called only once, regardless of how many rows of data are affected by the DML statement

DDL triggers can also be divided into:

Database-level DDL triggers: Triggers are triggered by any user in the database that performs the appropriate DDL operation.

User-level DDL triggers: Triggers are triggered only when the user who specifies the scenario when the trigger is created, and the trigger is not triggered when another user executes the DDL operation.

2. Describe the components of a trigger and its role.

A: In an Oracle system, triggers include the following components:

Part	Role
1. Trigger Name	The trigger name is the name from which the trigger is created. Typically include: The time the trigger was executed, the action performed, the table involved, the columns involved, and so on.
2. Trigger statement	The trigger statement is an inducement for Oracle to perform trigger actions, including the definition of the trigger time, the triggering event, and the Trigger object. A trigger can be automatically called by the system only if the user performs an operation on the database that satisfies all the content defined in the trigger statement.
3. Trigger Limit conditions	Triggering a throttling condition is another factor that determines whether a trigger is automatically called by the system. When the user's action satisfies the trigger statement, the trigger is not necessarily called, at which point the system also checks whether the trigger constraint is defined in the trigger, and if so, whether the current operation satisfies the constraint.
4. Trigger action	The trigger action is the body of the trigger and is a PL/SQL program block that is automatically executed by the system. The system automatically executes the code in the trigger Action section when both the trigger statement and the trigger constraint are met.

3. Describe the role of alternative triggers.

For:

If the instead of clause is selected when creating a trigger, then the trigger is an alternative trigger. Alternative triggers can only be built on a view that cannot be built on a table. DML operations that the user performs on the view are replaced by actions in the override trigger.

Substitution triggers primarily address issues that can be caused when an update operation is performed on a non-updatable view. When you define a view, if the primary key of the underlying table is not selected in the view, or if the data in the view is from more than one underlying table, the user will not be able to directly perform insert, modify, delete operations on such views. In this case, the user can create an alternate trigger for the view that transforms the update operation on the views to the action on the underlying table.

The creation and application of Oracle database--Trigger