create table BK_DEPT
(
DEPTID NUMBER not null,
DEPTNAME VARCHAR2(50),
DEPTNUMBER NUMBER,
MEMO1 VARCHAR2(100),
MEMO2 VARCHAR2(1000),
MEMO3 NUMBER
)
create table BK_ADMINUSER
(
USERID NUMBER not null,
USERNAME VARCHAR2(50),
USERPASSWORD VARCHAR2(20),
USERPHONE VARCHAR2(20),
USERVIEWNAME VARCHAR2(20),
USERSDEPTNUMBER NUMBER,
USERSDEPTNAME VARCHAR2(50),
USERCREATETIME VARCHAR2(50),
USERLOGINTIME VARCHAR2(50),
USEREXITTIME VARCHAR2(50),
USERTYPENUM VARCHAR2(500),
USERIP VARCHAR2(50),)
当BK_DEPT表里的部门名修改后 根据DEPTNUMBER =USERSDEPTNUMBER 同时修改BK_ADMINUSER表的USERSDEPTNAME
(
DEPTID NUMBER not null,
DEPTNAME VARCHAR2(50),
DEPTNUMBER NUMBER,
MEMO1 VARCHAR2(100),
MEMO2 VARCHAR2(1000),
MEMO3 NUMBER
)
create table BK_ADMINUSER
(
USERID NUMBER not null,
USERNAME VARCHAR2(50),
USERPASSWORD VARCHAR2(20),
USERPHONE VARCHAR2(20),
USERVIEWNAME VARCHAR2(20),
USERSDEPTNUMBER NUMBER,
USERSDEPTNAME VARCHAR2(50),
USERCREATETIME VARCHAR2(50),
USERLOGINTIME VARCHAR2(50),
USEREXITTIME VARCHAR2(50),
USERTYPENUM VARCHAR2(500),
USERIP VARCHAR2(50),)
当BK_DEPT表里的部门名修改后 根据DEPTNUMBER =USERSDEPTNUMBER 同时修改BK_ADMINUSER表的USERSDEPTNAME
SQL> create table BK_DEPT
2 (
3 DEPTID NUMBER not null,
4 DEPTNAME VARCHAR2(50),
5 DEPTNUMBER NUMBER,
6 MEMO1 VARCHAR2(100),
7 MEMO2 VARCHAR2(1000),
8 MEMO3 NUMBER
9 );Table createdSQL> create table BK_ADMINUSER
2 (
3 USERID NUMBER not null,
4 USERNAME VARCHAR2(50),
5 USERPASSWORD VARCHAR2(20),
6 USERPHONE VARCHAR2(20),
7 USERVIEWNAME VARCHAR2(20),
8 USERSDEPTNUMBER NUMBER,
9 USERSDEPTNAME VARCHAR2(50),
10 USERCREATETIME VARCHAR2(50),
11 USERLOGINTIME VARCHAR2(50),
12 USEREXITTIME VARCHAR2(50),
13 USERTYPENUM VARCHAR2(500),
14 USERIP VARCHAR2(50)
15 );Table createdSQL> INSERT INTO BK_DEPT SELECT 1,'DEPT',8888,'A','B',88 FROM DUAL;1 row insertedSQL> INSERT INTO BK_ADMINUSER(USERID,USERSDEPTNUMBER,USERSDEPTNAME) SELECT 1,8888,'HAHA' FROM DUAL;1 row insertedSQL> SELECT USERID,USERSDEPTNUMBER,USERSDEPTNAME FROM BK_ADMINUSER; USERID USERSDEPTNUMBER USERSDEPTNAME
---------- --------------- --------------------------------------------------
1 8888 HAHASQL> CREATE OR REPLACE TRIGGER TREATE_DATA
2 AFTER UPDATE ON BK_DEPT FOR EACH ROW
3 BEGIN
4 UPDATE BK_ADMINUSER SET BK_ADMINUSER.USERSDEPTNAME = :NEW.DEPTNAME
5 WHERE BK_ADMINUSER.USERSDEPTNUMBER = :NEW.DEPTNUMBER;
6 END;
7 /Trigger createdSQL> UPDATE BK_DEPT SET DEPTNAME = 'TEST';1 row updatedSQL> SELECT USERID,USERSDEPTNUMBER,USERSDEPTNAME FROM BK_ADMINUSER; USERID USERSDEPTNUMBER USERSDEPTNAME
---------- --------------- --------------------------------------------------
1 8888 TESTSQL>