ORACLE trigger: View, schema, database

View trigger, instead

We know that if a view is composed of only one table, there is no limit on the operations on The View. if the view is composed of multiple tables, the unpdate, insert, and delete operations on the view will fail. but sometimes we do what we need to do. in this case, you need to use the trigger, and then use the instead of keyword to specify some alternative operations.

For example, if you have a view, my_view creates a trigger as follows:

Create or replace trigger my_view_trigger

Instead of insert or update

On my_view

Declare

Insert into TMP (ENO) values (: New. Eno );

End;

 

Trigger is triggered when SQL: insert into my_view (Eno, name) values (88, 'test'); is executed.

However, the view's instead of Type trigger has a special relationship with other types of triggers. the name can also be used to replace the SQL operation that triggers it. that is, insert into my_view (Eno, name) values (88, 'test'); the operation of the SQL itself will not work. only PL/SQL statement blocks in the trigger are actually executed.

 

Note:

1. an instead of Type trigger can only be created for a view, and the view cannot have a check option (such as with check read only). This prevents conflicts between different functions. if it is a read-only view, you can't create a trigger and do some DML operations.

2. The before or after option cannot be specified, because the SQL that triggers the trigger is not actually executed, so before or after has no meaning.

 

Database, schema Level Trigger

Triggers for tables and views may be used by many developers. For databases, schema triggers are generally used by dBA for multiple points.

For exampleTrigger is triggered when DDL operations are performed on the schema. (a trigger for a table or view can only perform DML operations, but not DDL operations ).

A simple example

 

Create or replace trigger ddl_trigger

After DDL on Schema

Begin

Insert into tblog values (systimestamp, ora_sysevent, ora_login_user,
Ora_dict_obj_type, ora_dict_obj_name );
End;

Here, ora_login_user is the login name, ora_dict_obj_type object type (such as table or view), and ora_dict_obj_name is the object name, such as the table name or view name. you may see that these variables are not defined. it is actually defined by Oracle. You only need to use it.

Trigger is triggered if any user executes the following SQL statement: Create Table tmp_tb (ENO INT.

However, it seems that the specific schema cannot be directly specified, but it can only be used for all schemas.

 

If you need to make some records each time you log on, you can create the following trigger:

Create or replace trigger logon_trigger
After logon Database
Begin
Insert into tblog values (ora_login_user, ora_client_ip_address, systimestamp );
End;

 

Here, the logon database does not mean that the database is started, but is connected to a session every time.