表结构如下
create table TBL_REST_WAITING_QUEUE
(
USER_CODE VARCHAR2(12),
USER_NAME VARCHAR2(12),
AGENT_CODE VARCHAR2(12),
BRAND VARCHAR2(30),
REQ_MESSAGEID VARCHAR2(20),
BEGIN_WAITING DATE default SYSDATE,
QUEUE_LIST VARCHAR2(50),
REQ_TYPE INTEGER,
REQ_REST_TYPE VARCHAR2(20),
REQ_REST_TXT VARCHAR2(100),
ISENABLEREST INTEGER default 0,
ISENABLERESTTIME DATE,
ISENABLEAGENTCODE VARCHAR2(20),
ISENABLETXT VARCHAR2(100),
ID INTEGER
)
要实现的效果就是当表的数据发生更改时, 如果 ISENABLEREST=1 , 就把 ISENABLERESTTIME 设置成SysDate
create table TBL_REST_WAITING_QUEUE
(
USER_CODE VARCHAR2(12),
USER_NAME VARCHAR2(12),
AGENT_CODE VARCHAR2(12),
BRAND VARCHAR2(30),
REQ_MESSAGEID VARCHAR2(20),
BEGIN_WAITING DATE default SYSDATE,
QUEUE_LIST VARCHAR2(50),
REQ_TYPE INTEGER,
REQ_REST_TYPE VARCHAR2(20),
REQ_REST_TXT VARCHAR2(100),
ISENABLEREST INTEGER default 0,
ISENABLERESTTIME DATE,
ISENABLEAGENTCODE VARCHAR2(20),
ISENABLETXT VARCHAR2(100),
ID INTEGER
)
要实现的效果就是当表的数据发生更改时, 如果 ISENABLEREST=1 , 就把 ISENABLERESTTIME 设置成SysDate
before INSERT OR DELETE OR UPDATE on TBL_REST_WAITING_QUEUE
for each row
pragma autonomous_transactiondeclare;
BEGIN
IF :NEW.ISENABLEREST=1 THEN
UPDATE ISENABLERESTTIME=SYSDATE WHERE id=:new.id;
END IF;
end tri_test;
更新A表,解发A表的trigger,trigger中又对表进行修改?会报错的。1楼的trigger中,带着自治事务的标识pragma autonomous_transactiondeclare;编译有错误。
UPDATE ISENABLERESTTIME=SYSDATE WHERE id=:new.id;
改成
new.isenableresttime := sysdate;
要好一些。
before INSERT OR UPDATE on TBL_REST_WAITING_QUEUE
for each row
BEGIN
IF :NEW.ISENABLEREST=1 THEN
:new.ISENABLERESTTIME:=SYSDATE;
END IF;
end;
AFTER INSERT OR UPDATE ON tbl_rest_waiting_queue
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
IF :NEW.ISENABLEREST = 1 THEN
UPDATE tbl_rest_waiting_queue SET ISENABLERESTTIME = SYSDATE WHERE id=:new.id;
END IF;
END MyTestTrigger;
BEFORE INSERT OR UPDATE ON tbl_rest_waiting_queue
FOR EACH ROW
DECLARE
BEGIN
IF :NEW.ISENABLEREST = 1 THEN
:New.Isenableresttime := SYSDATE;
END IF;
END MyTestTrigger;