oracle 触发器

浏览数：32 / 时间：2015年06月12日

需求如下：

公司内部开发人员没有形成统一规范，开发各种的系统，采用不同的用户表清单，现在为方便用户，将各系统用户表信息进行统一。

解决方案如下：

1.初始时，采用存储过程做成一个计划任务，每天定时更新。

 1 CREATE OR REPLACE PROCEDURE "UPDATE_DDCM_USERS" AS
 2 
 3   sEmpno         VARCHAR2(20) default ‘‘;
 4   sDept          VARCHAR2(40) default ‘‘;
 5   sFName         VARCHAR2(20) DEFAULT ‘‘;
 6   sLName         VARCHAR2(20) DEFAULT ‘‘;
 7   sFLName        VARCHAR2(20) DEFAULT ‘‘;
 8   sEmail         VARCHAR2(20) DEFAULT ‘‘;
 9   sPassword      VARCHAR2(20) DEFAULT ‘‘;
10   sRETDT         VARCHAR2(20) DEFAULT ‘‘;
11   iusercnt1     number;
12 
13   CURSOR USER IS  
14     SELECT EMP_NO,
15            E_PASS,
16            F_NM,
17            L_NM,
18            C_NM,
19            E_MAIL,
20            DEPT_NM,
21            RETDT 
22       FROM SFMG.USERLIST;   
23 
24 BEGIN
25     FOR X in USER LOOP
26     
27     sEmpno     := X.EMP_NO;
28     sDept      := X.DEPT_NM; 
29     sFName     := X.F_NM;
30     sLName     := X.L_NM;
31     sFLName    := X.C_NM;
32     sEmail     := SUBSTR(X.E_MAIL, 0, INSTR(X.E_MAIL, ‘@‘)-1);
33     sPassword  := X.E_PASS;
34     sRETDT     := X.RETDT;
35   
36     begin
37       select count(*) into iusercnt1 from USER_LIST where EMP_NO = sEmpno;
38     EXCEPTION
39       WHEN no_data_found THEN
40         iusercnt1 := 0;
41     end;
42 
43        IF iusercnt1 = 0 THEN
44       INSERT INTO USER_LIST
45       VALUES
46         (sEmpno,
47          sDept,
48          sFName,
49          sLName,
50          sFLName,
51          sEmail,
52          sPassword,
53          ‘‘
54         );
55     ELSE
56       IF sRETDT = ‘00000000‘ THEN
57         UPDATE USER_LIST SET DEPT_NM= sDept,F_NM = sFName,L_NM = sLName,FL_NM = sFLName,
58                              E_MAIL = sEmail,E_PASS = sPassword,ADMIN_FLAG = ADMIN_FLAG
59         where EMP_NO = sEmpno;
60             END IF;
61     END IF;
62 
63 END LOOP;
64   COMMIT;
65 EXCEPTION
66   WHEN PROGRAM_ERROR THEN
67     ROLLBACK WORK;
68 END; 
69 -- 复制用户清单从sfmg.userlist@whdb，应该做为计划任务执行。

缺点：由于每天只是更新一次，用户变更信息后，没有及时更新。

2.采用DML 行级触发器解决。

 1 create or replace trigger async_userlist
 2 after insert or update on sfmg.user_list
 3 for each row
 4 
 5 begin
 6      if INSERTING then
 7        insert into dcc.user_list (emp_no,dept_nm,f_nm,l_nm,e_mail,e_pass,admin_flag)
 8          values ( :new.empno,:new.dept_en,:new.name_cn,:new.name_en,substr(:new.email,0,instr(:new.email,‘@‘)-1),:new.password,‘‘);
 9      elsif updating then
10        update dcc.user_list set dept_nm = :new.dept_cn,f_nm = :new.name_cn, l_nm = :new.name_en,
11                                  e_mail = substr(:new.email,0,instr(:new.email,‘@‘)-1) ,e_pass = :new.password
12        where emp_no = :new.empno;
13      end if;
14 end async_userlist;