oracle 触发器做备份,使用触发器进行表数据备份-Oracle

功能很简单,字段多了点,多一个参数少一个就容易出问题.

如:

too many columns.

最近看了web js优化的,js对内部变量约定为_ 开头如:_name,_age.

触发器写的比较少,临时变量不支持_myVar.可惜.

1.Compilation errors for

Error: PLS-00103: Encountered the symbol “_” when expecting one of the following:

Text: _TMP_PROJECT_ID NUMBER(16,0);

2.ORA-00947:not enough values

列名多,而插入值少.如(A,B,C)VALUES(A,B)

3.ORA-00913:too many values  怎相反

如(A,B,C)VALUES(A,B,C,D)

引用

create or replace trigger TRI_AAA

before update or delete

on AAA

for each row

declare

ERR VARCHAR2(100);

–原表字段

TMP_PROJECT_ID NUMBER(16,0);

TMP_COMPANY_ID NUMBER(16,0);

TMP_CREATE_DATETIME DATE;

…

TMP_GROUP_ID NUMBER(16,0);

–新增字段

–TMP_USER_OPT VARCHAR2(64);

–TMP_USER_OPT_DATETIME DATE;

PRAGMA AUTONOMOUS_TRANSACTION;

begin

TMP_PROJECT_ID:=:OLD.PROJECT_ID;

TMP_COMPANY_ID:=:OLD.COMPANY_ID;

TMP_CREATE_DATETIME:=:OLD.CREATE_DATETIME;

…

TMP_GROUP_ID:=:OLD.GROUP_ID;

if updating then

INSERT INTO AAA_LOG(PROJECT_ID,COMPANY_ID,CREATE_DATETIME,…,TMP_GROUP_ID,’UPDATE’,SYSDATE);

commit;

elsif deleting then

INSERT INTO AAA_LOG(PROJECT_ID,COMPANY_ID,CREATE_DATETIME,…,TMP_GROUP_ID,’UPDATE’,SYSDATE);

commit;

end if;

EXCEPTION

WHEN TOO_MANY_ROWS THEN

INSERT INTO TAB_PUB_ERROR (userid, error, triname, tabfountain, tabend, edate)

VALUES(0,’TOO_MANY_ROWS’,’TRI_AAA’,’AAA’,’AAA’,sysdate);

COMMIT;

WHEN NO_DATA_FOUND THEN

INSERT INTO TAB_PUB_ERROR  (userid, error, triname, tabfountain, tabend, edate)

VALUES(0,’NO_DATA_FOUND’,’TRI_AAA’,’AAA’,’AAA’,sysdate);

COMMIT;

WHEN OTHERS THEN

err:=SUBSTR(SQLERRM(SQLCODE),1,100);

INSERT INTO TAB_PUB_ERROR  (userid, error, triname, tabfountain, tabend, edate)

VALUES(0,err,’TRI_AAA’,’AAA’,’AAA’,sysdate);

COMMIT;

end;

作者“westsince2001”