Oracle触发器-监控用户MDL表记录

测试环境：Windows Server 2003 Enterprise Edition SP2

数据库环境：Oracle 10.2

在日常维护HIS数据库时，有时会遇到某些记录被变更了，虽然我们可以使用LogMNR分析归档日志文件，从中了解这个记录变更的过程，却不知道这条数据到底是谁变更的？这给我们的日常管理带来了诸多的问题和不便，如：我们在进行月报统计时，有时会出现当月的原有人数与上个月的现有人数不一致；信息的ID号位数或多或少；药品发放通知无故丢失的等等情况。

这时，我们需要借助Oracle触发器（Trigger）来帮助我们找出根源。关于触发器的定义，官方的解释是：A trigger is like a stored procedure that Oracle Database invokes automatically whenever a specified event occurs.

触发器与存储过程相似，但是它们又有不同之处，触发器是事件触发，只要满足执行规则，就会隐式的自动调用。而存储过程需要显式的使用execute进行调用。

我们也来看看官方的说明：Like a stored procedure, a trigger is a named PL/SQL unit that is stored in the database and can be invoked repeatedly. Unlike a stored procedure, you can enable and disable a trigger, but you cannot explicitly invoke it. While a trigger is enabled, the database automatically invokes it—that is, the trigger fires—whenever its triggering event occurs. While a trigger is disabled, it does not fire.

那么，对于为什么需要的触发器的疑问也就明了的吧？如果没有触发器的话，那么DML就可以无限制的操作基表，这样就不符合业务规则了。

触发器的类型有语句级触发器、多条件触发器、级联触发器、行级触发器，在这里，我就不一一介绍了，今天我们主要解决如果限制业务上不规范操作。

下面，我们先来学习一下，如何限制工作站随意退回已归档的信息？如果不加以限制，这种不规范的业务操作对月报统计方面有很大的影响，会出现上面说过的当月的原有人数与上个月的现有人数不一致的问题。

通过Statement Tracer for Oracle这款SQL跟踪软件，我们了解退回某条信息，首先更新PAT_VISIT表中的日期字段DISCHARGE_DATE_TIME。根据这一条，我们只要设置该字段一旦修改或更新即报错，就可以限制这种不规范的业务操作了。

下面直接给出语句，以供小伙伴们参考：

CREATE OR REPLACE TRIGGER SYS.TRI_PAT_VISIT

BEFORE UPDATE OF DISCHARGE_DATE_TIME

ON MEDREC.PAT_VISIT

FOR EACH ROW

BEGIN

IF :OLD.DISCHARGE_DATE_TIME is not null THEN

raise_application_error(-200001,'WARING!!! Illegal operation!');

dbms_output.PUT_LINE('WARING!!! Illegal operation!');

END IF;

END;

/

创建完触发器后，如果确实需要退回某条信息，我们只需要将MEDREC.TRI_PAT_VISIT触发器禁用即可，语句是：

ALTER TRIGGER MEDREC.TRI_PAT_VISITDISABLE;

业务操作完后，记得要重新启动该触发器哦，语句是：

ALTER TRIGGER TRI_PAT_VISITENABLE;

对于那些规定字段长度的，比如信息的ID号的长度，我们要求是7位阿拉伯数字，那我们就要可以通过触发器，规定ID号的字段必须有7位数，否则就报错。

语句如下：

CREATE OR REPLACE TRIGGER SYS.TRI_PATIENT_ID

BEFORE INSERT OR UPDATE OF PATIENT_ID

ON MEDREC.PAT_MASTER_INDEX

FOR EACH ROW

BEGIN

IF LENGTH(:NEW.PATIENT_ID)7 THEN

RAISE_APPLICATION_ERROR(-200002,'WARING!!!PATIENT_ID length is wrong!');

DBMS_OUTPUT.PUT_LINE('WARING!!!PATIENT_ID length is wrong!');

END IF;

END;

/

最后，我们有一种情况是需要记录对某条信息进行MDL操作时的具体信息，比如是哪个账号，什么时候，用什么程序，对哪个字段进行更新或删除了，该字段的原值是什么的等等？

下面，我们以 药品发放通知 DRUG_PROVIDE_NOTICE表的人为删除进行监控。

1、首先，我们需要了解该表的数据结构，在CHENM用户下创建DRUG_PROVIDE_NOTICE_UD表，用于记录对该表DRUG_PROVIDE_NOTICE进行MDL操作时的登录账号，机器名，IP地址，操作时间及模块，更新或删除表数据时的原值。

创建表语句如下：

C:\>SQLPLUS "CHENM/PASSWORD@SIDAS SYSDBA"

SQL>CREATE TABLE CHENM.DRUG_PROVIDE_NOTICE_UD

(

LOGIN_USERVARCHAR2(60),

MACHINEVARCHAR2(60),

IPADDRESSVARCHAR2(20),

MDL_TIMEDATE,

MODULEVARCHAR2(100),

OLD_PROVIDE_STORAGEVARCHAR2(8),

OLD_APPLICANT_STORAGEVARCHAR2(8),

OLD_DOCUMENT_NOVARCHAR2(10)

);

2、退出CHENM账号，以SYS账号进入数据库，创建监视用户对DRUG_PROVIDE_NOTICE进行MDL操作的触发器。语句如下：

C:\>SQLPLUS "SYS/PASSWORD@SIDAS SYSDBA"

SQL>CREATE OR REPLACE TRIGGER SYS.TRI_DRUG_PROVIDE_NOTICE_UD

BEFORE INSERT OR UPDATE ON PHARMACY.DRUG_PROVIDE_NOTICE

FOR EACH ROW

DECLARE

V_PROGRAM CHENM.DRUG_PROVIDE_NOTICE_UD.MODULE%TYPE;

BEGIN

SELECT NVL(S.PROGRAM,S.MODULE)

INTO V_PROGRAM

FROM V$SESSION S

WHERE S.SID = (SELECT DISTINCT SID FROM V$MYSTAT);

INSERT INTO CHENM.DRUG_PROVIDE_NOTICE_UD

(LOGIN_USER,

MACHINE,

IPADDRESS,

MDL_TIME,

MODULE,

OLD_PROVIDE_STORAGE,

OLD_APPLICANT_STORAGE,

OLD_DOCUMENT_NO)

VALUES

(ORA_LOGIN_USER,

SYS_CONTEXT('USERENV','TERMINAL'),

SYS_CONTEXT('USERENV','IP_ADDRESS'),

SYSDATE,

V_PROGRAM,

:OLD.PROVIDE_STORAGE,

:OLD.APPLICANT_STORAGE,

:OLD.DOCUMENT_NO);

END TRI_DRUG_PROVIDE_NOTICE_UD;

/

至此，我们的监控用户对某张表进行MDL操作时记录具体信息的问题就解决了。一旦有人修改，记录会自动插入到监控表里面！