Suppose you are asked about certain activities on your database, in this example on a certain table. You need to keep track who has inserted, updated, or even deleted which record in this table.
For inserting and updating records you may include additional columns such as "last inserted/updated by" and "last inserted/updated timestamp", but what's about the deleted records?
Using an Action Log Table
Using an action log table offers you the possibility to keep track for all DML statements if needed. Follow the straight forward example below and you will participate in the benefits of action logging:
-- Create action log table
CREATE TABLE ACTION_LOG (
-- Create trigger to log inserting, updating,
-- deletion of ABC_USER
CREATE OR REPLACE TRIGGER TRG_ABC_USER_ACTION
BEFORE INSERT OR UPDATE OR DELETE
ON ABC_USER FOR EACH ROW
IF ( INSERTING ) THEN L_ACTION := 'INSERT'; END IF;
IF ( UPDATING ) THEN L_ACTION := 'UPDATE'; END IF;
IF ( DELETING ) THEN L_ACTION := 'DELETE'; END IF;
INSERT INTO ACTION_LOG
SELECT :OLD.USER_ID, :OLD.USER_NAME, L_ACTION,
SYSDATE, S.USERNAME, S.OSUSER, S.MACHINE,
S.TERMINAL, S.PROGRAM, S.CLIENT_INFO
FROM V$SESSION S
WHERE S.AUDSID = USERENV('SESSIONID');
In the first step the action log table is created. You may enlarge this example by your own needs, e.g. by table name to log different tables within the same action table.
In the second step the trigger is created in the table which you wish to get the logging information. In this case it's the ABC_USER table. According to the current DML statement the local action variable is set to either 'INSERT', 'UPDATE', or 'DELETE'. The insert statement to the logging table gathers the current information about the manipulated user data (USER_ID, USER_NAME) and the current session settings. The current session settings are selected from V$SESSION using the general purpose function USERENV() to match the current session identifier.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/936/viewspace-60616/，如需转载，请注明出处，否则将追究法律责任。