我想实现
当UPDATE TABLE1 SET COL1=‘A’时, COL2=1
当UPDATE TABLE1 SET COL1=‘B’时, COL2=2
也就是当前台程序把COL1 更新为某值时,数据库中COL2变成对应的另一个值(事先已规定好,比如COL1为A则COL2为1)请问怎么实现呢?
当UPDATE TABLE1 SET COL1=‘A’时, COL2=1
当UPDATE TABLE1 SET COL1=‘B’时, COL2=2
也就是当前台程序把COL1 更新为某值时,数据库中COL2变成对应的另一个值(事先已规定好,比如COL1为A则COL2为1)请问怎么实现呢?
SQL> create view v_test as select col1, case when col1='A' then 1 when col1 = 'B' then 2 else 0 end col2 from test;View createdSQL> insert into test values('A');1 row insertedSQL> select * from v_test;COL1 COL2
---- ----------
A 1SQL> update test set col1 = 'B';1 row updatedSQL> select * from v_test;COL1 COL2
---- ----------
B 2
当UPDATE TABLE1 SET COL1=‘A’时, COL2=1
当UPDATE TABLE1 SET COL1=‘B’时, COL2=2
也就是当前台程序把COL1 更新为某值时,数据库中COL2变成对应的另一个值(事先已规定好,比如COL1为A则COL2为1) */CREATE TABLE TEST_TABLE
(COL1 CHAR(1),
COL2 INT
);
-------- 测试数据
INSERT INTO TEST_TABLE VALUES('C',3);
INSERT INTO TEST_TABLE VALUES('D',4);
COMMIT;
---------更新前
SELECT * FROM TEST_TABLE;
COL1 COL2
C 3
D 4
--------------trigger
CREATE OR REPLACE TRIGGER UPD_TABLE
AFTER UPDATE OF COL1 ON TEST_TABLE
BEGIN
UPDATE TEST_TABLE
SET COL2 = DECODE(COL1,
'A',
1,
'B',
2)
WHERE COL1 IN ('A', 'B');
END;
-------- update
UPDATE TEST_TABLE SET COL1 = 'A'
WHERE COL1 = 'C';
COMMIT;---------更新后
SELECT * FROM TEST_TABLE;
COL1 COL2
A 1
D 4
用触发器参考这个贴里提到的用法http://topic.csdn.net/u/20090704/20/49c38a59-beaa-4fe2-8a84-b48f8042cc74.html?64689