ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Logging of DML commands[akadia]

Logging of DML commands[akadia]

原创 Linux操作系统 作者:jlandzpa 时间:2019-04-09 15:09:05 0 删除 编辑

Action Logging

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 (
USER_ID INTEGER,
USER_NAME VARCHAR2(30),
ACTION VARCHAR2(30),
ACTION_DATE DATE,
SES_USERNAME VARCHAR2(30),
SES_OSUSER VARCHAR2(30),
SES_MACHINE VARCHAR2(64),
SES_TERMINAL VARCHAR2(30),
SES_PROGRAM VARCHAR2(48),
SES_CLIENT_INFO VARCHAR2(64)
);

-- 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
DECLARE
L_ACTION ACTION_LOG.ACTION%TYPE;
BEGIN
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');
END;
/
SHOW ERROR;

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2001-10-12

  • 博文量
    240
  • 访问量
    172287