Oracle trigger 3-DDL trigger

Oracle trigger 3-DDL trigger Oracle trigger 2-DML trigger http://www.bkjia.com/database/201304/200549.html 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. To CREATE a ddl trigger, the syntax is as follows: 1 CREATE [or replace] trigger TRIGGER name -- CREATE a trigger and specify a name, or replace is optional. Option 2 {BEFORE | AFTER} {DDL event} ON {DATABASE | SCHEMA} -- specifies that the trigger is triggered BEFORE or AFTER a DDL event. The range is on database, on schema3 [WHEN (...)] -- Optional WHEN clause, which uses logical judgment to avoid meaningless trigger execution 4 DECLARE -- trigger details 4-75 Variable declarations6 BEGIN7... some code... 8 END; Examples: SQL> CREATE OR REPLACE TRIGGER hr. testtrigger2 after create on schema -- on schema is only triggered by 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 BEGIN4 -- the following uses the event attribute 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 event DDL event trigger time ALTER triggers associate statistics when you use SQL alter commands on any object in the database when using SQL's ANALYZE command on any object in the database when the statistical data is associated with a database object, AUDIT is triggered. When the SQL audit command is used to open the AUDIT, COMMENT is triggered to COMMENT on the database object, and CREATE is triggered. The SQL CREATE command is used to CREATE a database. When an object is triggered, events used in the DDL list will trigger disassociate statistics. When the association between statistical data and database objects is removed, the DROP command is triggered. When the DROP command of SQL is used to delete database objects, the GRANT command is triggered to GRANT permissions through the GRANT command of SQL. when NOAUDIT is triggered when audit is disabled through SQL noaudit, RENAME is triggered when the RENAME command of SQL is used to RENAME the object. When the REVOKE is revoked from authorization through the SQL revoke statement, TRUNCATE is triggered to TRUNCATE the table through the SQL trun 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 event and attribute function name return value ORA_CLIENT_IP_ADDRESS Client IP address ORA_DATABASE_NAME database name ORA_DES_ENCRYPTED_PASSWORD the user's DES algorithm-encrypted password ORA_DICT_OBJ_NAME lists the number of affected objects and names of the database object names triggered by number of objects affected by the primary region of the database objects that trigger DDL and list of names ORA_DICT_OBJ_TYPE the number of authorized ORA_GRANTEE database object types that trigger DDL ORA_INSTANCE_NUM database instance number ORA_IS_ALTER_COLUMN if the column specified by the Operation Parameter Column, returns true, otherwise falseORA_IS_CREATING_NES If a nested table is being created in TED_TABLE, true is returned. Otherwise, falseORA_IS_DROP_COLUMN. If the column specified by the column_name parameter is deleted, true is returned, otherwise, the user name ORA_PARTITION_POS SQL command where the falseORA_LOGIN_USER trigger is located can correctly add the number of permissions granted or revoked by ORA_PRIVILEGE_LIST. The number of ORA_REVOKEE recyclers ORA_ SQL _TXT triggers the number of rows of SQL statements of the trigger. ORA_SYSEVENT: the time when the DDL trigger is triggered. ORA_WITH_GRANT_OPTION returns true if the grant option is granted. Otherwise, false for more attribute functions, see the official documentation PL/SQL Language Reference-> Triggers and Oracle Database Data Transfer Utilities use events and properties Examples: -- Warn when creating Database objects, when deleting database objects, the create or replace trigger hr is blocked. no_dropBEFORE ddl on databasebeginif ORA_SYSEVENT = 'create' THENDBMS _ 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' THENRAISE _ 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 REPLAC E trigger into alter on schemadeclare -- cursor to get columns in a tableCURSOR curs_get_columns (cp_owner VARCHAR2, cp_table VARCHAR2) ISSELECT column_nameFROM primary 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_dic T_obj_owner, ora_dict_obj_name) LOOP -- if the current column was the one that was altered then say soIF ora_is_alter_column (v_column_rec.column_name) THEN -- if the table/column is core? IF is_application_column (partition, ora_dict_obj_name, v_column_rec.column_name) THENRAISE_APPLICATION_ERROR (-20001, 'Could not alter core application attributes '); end if; -- table/column is coreEND IF; -- current column was alteredEND LOOP; -- every column in the tableEND IF; -- table was alteredEND; -- List of attribute function return values create or replace trigger hr. revoke grant on your VARCHAR2 (30); your BINARY_INTEGER; v_grantee_list values; v_num_privs BINARY_INTEGER; v_priv_list values; values: = ora_grantee (v_grantee_list); v_num_privs: = ora_privilege_list (v_priv_list); IF v_grant_type = 'Role privilege' THENDBMS _ OUTPUT. put_line (CHR (9) | 'the following roles/privileges were granted'); FOR counter IN 1 .. revoke (CHR (9) | CHR (9) | 'privilege' | v_priv_list (counter); end loop; ELSIF v_grant_type = 'object privilege' THENDBMS _ OUTPUT. put_line (CHR (9) | 'the following object privileges were granted'); FOR counter IN 1 .. v_num_privsLOOPDBMS_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 aggregate ('with grant option'); ELSEDBMS_OUTPUT.put_line (''); end if; ELSIF v_grant_type = 'System PRIVILEGE 'THENDBMS _ OUTPUT. put_line (CHR (9) | 'the following system privileges were granted'); FOR counter IN 1 .. v_num_privsLOOPDBMS_OUTPUT.put_line (CHR (9) | CHR (9) | 'privilege' | v_priv_list (counter); end loop; ELSEDBMS_OUTPUT.put_line ('I have no idea what was granted'); END IF; FOR counter IN 1 .. v_num_granteesLOOPDBMS_OUTPUT.put_line (CHR (9) | 'Grant Recipient '| v_grantee_list (counter); end loop; END ;/