ORACLE trigger 3-ddl trigger

DDL trigger, which is triggered when a DDL statement is executed. Schema triggers and database Triggers are divided according to the scope of action. Schema triggers applies to one user, while database Triggers applies to all users of the entire database.

Create a DDL trigger

To create a DDL trigger, the syntax is as follows:

1 Create [or replace] trigger name -- create a trigger and specify a name. or replace is optional.
2 {before | after} {DDL event} on {database | schema} -- specify that the trigger is triggered before or after the DDL event. The range is on database and on schema.
3 [When (...)] -- Optional when clause. Use logical judgment to avoid meaningless trigger execution.
4 declare -- trigger details 4-7

5 variable declarations
6 begin
7... some code...
8 end;

Examples:

SQL> Create or replace trigger HR. testtrigger
2 after create on schema -- on schema is only triggered by the create table under the HR user, but not by other users. If it is on database, other users will trigger this trigger when creating table.
3 begin
4 -- the following uses event attributes
5 dbms_output.put_line ('I believe you have created a' |
6 ora_dict_obj_type | 'called' |
7 ora_dict_obj_name );
8 end;
9/
Trigger created.

Available events

Available DDL events

DDL events	Trigger time
Alter	Triggered when SQL alter command is used for any object in the database
Analyze	Triggered when you use the SQL analyze command on any object in the database
Associate statistics	Triggered when statistic data is associated with a database object
Audit	Triggered when an audit is opened using the SQL Audit Command
Comment	Triggered when commenting on database objects
Create	Triggered when a database object is created using the SQL CREATE Command
DDL	All events in the list are triggered.
Disassociate statistics	Triggered when the Association of statistical data and database objects is removed
Drop	Triggered when the drop command of SQL is used to delete database objects
Grant	Triggered when the grant command of SQL is used to grant permissions
Noaudit	Triggered when audit is disabled through SQL noaudit
Rename	Triggered when an object is renamed using the SQL RENAME Command
Revoke	Triggered when the authorization is revoked using the SQL revoke statement
Truncate	Triggered when a table is truncated using the SQL truncate statement.

Available attributes

Oracle provides a series of functions to provide information about what triggers the DDL trigger and the status light of the trigger. In the above trigger example, attributes are used.

DDL trigger events and attribute functions

Function Name	Return Value
Ora_client_ip_address	Client IP Address
Ora_database_name	Database Name
Ora_des_encrypted_password	DEs of the current userAlgorithmEncrypted password
Ora_dict_obj_name	Name of the database object for triggering DDL
Ora_dict_obj_name_list	Number and name list of affected objects
Ora_dict_obj_owner	Database Object owner that triggers DDL
Ora_dict_obj_owner_list	Number and name list of affected objects
Ora_dict_obj_type	Database object type that triggers DDL
Ora_grantee	Number of authorized persons
Ora_instance_num	Number of database instances
Ora_is_alter_column	True is returned if the column specified by the Operation Parameter column_name; otherwise, false is returned.
Ora_is_creating_nested_table	If a nested table is being created, true is returned; otherwise, false is returned.
Ora_is_drop_column	If the column specified by the column_name parameter is deleted, true is returned; otherwise, false is returned.
Ora_login_user	Username of the trigger
Ora_partition_pos	You can correctly add partition clause positions in SQL commands.
Ora_privilege_list	The number of permissions granted or revoked.
Ora_revokee	Number of recyclers
Ora_ SQL _txt	The number of rows of the SQL statement that triggers the trigger.
Ora_sysevent	Time when the DDL trigger is triggered
Ora_with_grant_option	Returns true if the authorization has the grant option. Otherwise false

For more property functions, see the official documentation PL/SQL language reference-> triggers and Oracle database data transfer utilities

Use Events and Properties

Examples:

-- A warning is issued when a database object is created. It is blocked when a database object is deleted.

Create or replace trigger HR. no_drop
Before DDL on Database
Begin
If ora_sysevent = 'create'
Then
Dbms_output.put_line ('Warning !!! You have created a' |
Ora_dict_obj_type | 'called' |
Ora_dict_obj_name | '; username (creater):' |
Ora_dict_obj_owner | '; IP:' |
Ora_client_ip_address | '; event:' |
Ora_sysevent );
Elsif ora_sysevent = 'drop'
Then
Raise_application_error (-20000,
'Cannot create the '| ora_dict_obj_type |
'Named' | ora_dict_obj_name |
'As requested by '| ora_dict_obj_owner );
End if;
End;

-- Which column of the database table is operated?

Create or replace trigger preserve_app_cols
After alter on Schema
Declare
-- Cursor to get columns in a table
Cursor curs_get_columns (cp_owner varchar2, cp_table varchar2)
Is
Select column_name
From all_tab_columns
Where owner = cp_owner and table_name = cp_table;
Begin
-- If it was a table that was altered...
If ora_dict_obj_type = 'table'
Then
-- For every column in the table...
For v_column_rec in curs_get_columns (
Ora_dict_obj_owner,
Ora_dict_obj_name
)
Loop
-- If the current column was the one that was altered then say so
If ora_is_alter_column (v_column_rec.column_name)
Then
-- If the table/column is core?
If is_application_column (
Ora_dict_obj_owner,
Ora_dict_obj_name,
V_column_rec.column_name
)
Then
Raise_application_error (
-20001,
'Could not alter core application bubutes'
);
End if; -- table/column is core
End if; -- current column was altered
End loop; -- every column in the table
End if; -- table was altered
End;
-- Attribute function Return Value List

Create or replace trigger HR. what_privs
After Grant on Schema
Declare
V_grant_type varchar2 (30 );
V_num_grantees binary_integer;
V_grantee_list ora_name_list_t;
V_num_privs binary_integer;
V_priv_list ora_name_list_t;
Begin
V_grant_type: = ora_dict_obj_type;
V_num_grantees: = ora_grantee (v_grantee_list );
V_num_privs: = ora_privilege_list (v_priv_list );

If v_grant_type = 'Role privilege'
Then
Dbms_output.put_line (
CHR (9) | 'the following roles/privileges were granted'
);

For counter in 1 .. v_num_privs
Loop
Dbms_output.put_line (
CHR (9) | CHR (9) | 'privilege' | v_priv_list (Counter)
);
End loop;
Elsif v_grant_type = 'object privilege'
Then
Dbms_output.put_line (
CHR (9) | 'the following object privileges were granted'
);

For counter in 1 .. v_num_privs
Loop
Dbms_output.put_line (
CHR (9) | CHR (9) | 'privilege' | v_priv_list (Counter)
);
End loop;

Dbms_output.put (CHR (9) | 'on' | ora_dict_obj_name );

If ora_with_grant_option
then
dbms_output.put_line ('with grant option ');
else
dbms_output.put_line ('');
end if;
elsif v_grant_type = 'System privilege'
then
dbms_output.put_line (
CHR (9) | 'the following system privileges were granted'
);

For counter in 1 .. v_num_privs
Loop
Dbms_output.put_line (
CHR (9) | CHR (9) | 'privilege' | v_priv_list (Counter)
);
End loop;
Else
Dbms_output.put_line ('I have no idea what was granted ');
End if;

For counter in 1 .. v_num_grantees
Loop
Dbms_output.put_line (
CHR (9) | 'Grant reiient '| v_grantee_list (Counter)
);
End loop;
End;
/