Automatically add DML triggers to a specified batch of tables

Automatically add DML triggers to a specified batch of tables. In order to track operation records of a batch of tables, you need to perform a trigger tracking on the batch of tables (no audit is used because the Audit requires the SYS permission ), using a single table write trigger is obviously unrealistic. Oracle does not support creating a trigger for multiple tables at the same time. here I will use the Stored Procedure Method to automatically create a trigger. note: I still do not recommend using a large number of triggers, because a large number of triggers will affect the performance, and it is said that the trigger is a serial operation, so the speed will definitely be compromised, another reason is that triggers are prone to unpredictable recursive triggers. 1. First, we CREATE a log record TABLE (if a trigger is used only as a log record) [SQL] -- CREATE a DML log record TABLE CREATE TABLE dml_log (session_id number (22) NOT NULL, oper_user varchar2 (100), tabname varchar2 (50), oper_type varchar2 (50), server_host varchar2 (200), ip VARCHAR2 (20), run_program varchar2 (200), oper_date date ); -- Add comments to the columns comment on column dml_log.session_id is 'current session id'; comment on column dml_log.oper_user is 'operating system username '; Comment on column dml_log.tabname is 'Modified table name'; comment on column dml_log.oper_type is 'Operation type'; comment on column dml_log.run_program is 'application used to connect to the database '; comment on column dml_log.server_host is 'Operation client computer name'; comment on column dml_log.ip is 'Operation Client IP address'; comment on column dml_log.oper_date is 'Modified date '; 2. GRANT the permission to create a trigger to the table owner who wants TO add the TRIGGER [SQL] 15:06:08 SYS @ orcl> GRANT CREATE TRIGGER TO SCOTT; authorization successful. 3. Write the automatically created stored procedure [SQL] -- create a storage trigger, create or replace procedure autocreate_trigger (ptable varchar2) -- ptable format: 'EMP, EMP_BAK... 'As/* Description: automatically create DML triggers for the specified table, and write the DML operation records to the log table DML_LOG parameter: ptable -- table name, in the format of 'EMP, EMP_BAK... 'author DATE (YYYY. MM. DD) Cryking 2013.4.8 -- Created */V_ SQL VARCHAR2 (20000); TYPE TYPE_TABLENAME IS TABLE OF clob INDEX BY BINARY_INTEGER; V_TABNAME TYPE_TABLENAME; -- Automatic batch creation trigger egin SELECT * bulk collect into V_TABNAME from table (SPLITSTR (ptable, ','); -- Use commas to separate TABLE variables for x in 1 .. v_TABNAME.count loop V_ SQL: = 'create OR REPLACE TRIGGER TRI _ '| V_TABNAME (X) | CHR (10) | 'after insert or update or delete on' | V_TABNAME (X) | CHR (10) | 'for each row' | CHR (10) | 'desc' | CHR (10) | 'v _ session v $ SESSION. AUDSID % TYPE; '| CHR (10) | 'v _ program v $ SESSION. PROGRAM % TYPE; '| CHR (10) | 'begin' | CHR (10) | 'select userenv (''sessionid'') INTO V_SESSION from dual; '| CHR (10) | 'select program into V_PROGRAM from v $ session where audsid = userenv (''sessionid'');' | CHR (10) | 'case WHEN inserting then' | CHR (10) | 'insert INTO dml_log VALUES (V_SESSION, sys_context (''userenv '', ''OS _ user''), ''' | V_TABNAME (X) | ''', ''inser'', sys_context (''userenv '', ''host''), sys_context (''userenv'', ''ip _ address'), V_PROGRAM, SYSDATE); '| CHR (10) | 'when updating then' | CHR (10) | 'insert INTO dml_log VALUES (V_SESSION, sys_context (''userenv'', ''OS _ user ''), ''' | V_TABNAME (X) | ''', ''updat'', sys_context (''userenv'', ''host ''), sys_context (''userenv'', ''ip _ address'), V_PROGRAM, SYSDATE); '| CHR (10) | 'when deleting then' | CHR (10) | 'insert INTO dml_log VALUES (V_SESSION, sys_context (''userenv'', ''OS _ user ''), ''' | V_TABNAME (X) | ''', ''delete'', sys_context (''userenv'', ''host ''), sys_context (''userenv'', ''ip _ address'), V_PROGRAM, SYSDATE); '| CHR (10) | 'end CASE; '| CHR (10) | 'end;'; execute immediate v_ SQL; END loop; nd; For the SPLITSTR function, see this blog: http://www.bkjia.com/database/201301/181638.html In (II. General separator function (VARCHAR2), the storage does not handle any exceptions. (I think the exception is still handled by the application, so I did not handle the exception according to NEWKID on ITPUB .) 4. Execute storage to automatically add a trigger to the specified table [SQL] -- start to batch Add the trigger begin autocreate_trigger ('EMP, DEPT, EMP_BAK '); end; 5. Check whether the trigger is successfully added and is valid [SQL] 16:09:08 SCOTT @ orcl> select. NAME,. TYPE, B. CREATED, B. LAST_DDL_TIME, B. status 16:09:18 2 from user_dependencies a, USER_OBJECTS B 16:09:18 3 where. type = 'trigger' 16:09:18 4 and. REFERENCED_TYPE = 'table' 16:09:18 5 and. REFERENCED _ Name in ('emp', 'dept', 'emp' _ Bak') 16:09:18 and. name = B. OBJECT_NAME; name type created ready STATUS already available ------------ ------- TRI_EMP TRIGGER 08-4 month-13 08-4 month-13 VALID TRI_DEPT TRIGGER 08-4 month-13 08-4 month-13 VALID already TRIGGER 08-4 month-13 08-4 month -13 three VALID lines have been selected. 6. Verify the trigger function [SQL] 17:17:37 SCOTT @ orcl> insert into emp_bak VALUES (1231, 'cryking', 'manger ', 1, SYSDATE, 12000 ,'', 1); 1 line has been created. Used time: 00: 00: 00.00 -- query the log record table 17:15:19 SCOTT @ orcl> select * from dml_log; SESSION_ID OPER_USER TABNAME OPER_TYPE SERVER_HOST IP Route OPER_DATE ---------- hour ------------ hour start with discovery It is complicated and requires the view permissions of the relevant V $ SQLAREA, so the trail is performed in audit mode later...