IF :NEW.字段 = 1 THEN 处理...为了能够读取当前行改变前后各列的值,需要借助于行触发器定义的关联名。 OLD-修改之前的行 NEW-修改之后的行 对于INSERT语句只有NEW,而OLD没有意义 对于DELETE语句只有OLD,而NEW没有意义 如果是在PL/SQL块内访问关联名,则需要在关联名前加“:”,如同访问外部变量 如果在PL/SQL块外访问关联名,则不需要加“:”
额..你的描述 我没看清楚..不过基本就着这样了..CREATE OR REPLACE TRIGGER TRG_TEST AFTER UPDATE ON table1 FOR EACH ROWBegin IF(:new.XXX=1) then .... ELSE ... END IF; End;
CREATE OR REPLACE TRIGGER TR_DML_TableAAA AFTER INSERT OR DELETE OR UPDATE ON TableAAA FOR EACH ROW when new.account=1 //想这么加条件,但是对于把account修改成其它值的update也要插入值的 BEGIN IF INSERTING THEN INSERT INTO LOG values ('root',SYSDATE,'Other','AAAA-01-0001','修改用户操作成功【'||new.ACCOUNT||'】',0); ELSIF UPDATING THEN INSERT INTO HANDLELOG values ('root',SYSDATE,'Other','AAAA-01-0002','修改用户操作成功【'||new.ACCOUNT||'】',0); ELSIF UPDATING THEN //应该在这加判断是否把account改成1 INSERT INTO HANDLELOG values ('root',SYSDATE,'Other','AAAA-01-0002','修改用户操作成功【'||111111||'】',0); END IF; END;
明白你的意思了,可是要怎么判断update时把:new.account改成1和其它情况呢? IF(:new.XXX=1) and updating then .... ELSEIf updating then ... ELSEIf inserting then ... ELSEIf deleting then ... END IF;这样写行吗?
IF updating then IF :new.abcd = 1 then ... ELSE ... END IF; ELSIF inserting then ... ELSIF deleting then ... END IF;
我在pl/sql里生成下面这个trigger,怎么有个红叉呢?是哪错了? CREATE OR REPLACE TRIGGER TR_DML_AAA AFTER INSERT OR UPDATE ON tableAAA FOR EACH ROW BEGIN IF INSERTING THEN INSERT INTO HANDLELOG values (SYSDATE,'Other','AAAA-01-0001','添加用户操作成功【'||:new.ACCOUNT||'】',0); ELSIF UPDATING THEN IF (:new.USER_STATUS=2) THEN INSERT INTO HANDLELOG values (SYSDATE,'Other','AAAA-01-0003','删除用户操作成功',0); ELSE INSERT INTO HANDLELOG values (SYSDATE,'Other','AAAA-01-0002','修改用户基本信息操作成功【'||:new.ACCOUNT||'】',0); END IF; END IF; END;
那就这样,主要是修改了AFTER,IF 判断就照5L的来CREATE OR REPLACE TRIGGER TRG_TEST AFTER UPDATE or INSERT or DELETE ON table1 FOR EACH ROWBegin IF updating then IF (:new.abcd = 1) then ... ELSE ... END IF; ELSIF inserting then ... ELSIF deleting then ... END IF;End;
插入的字段是number(20)型的,怎么把date转换正number20,谢谢
我照上面的方法写了一个,但是提示TRIGGER ULTRANMS.TR_DML_SECURITY_ULTRA_USER 编译错误错误:PL/SQL: ORA-00907: missing right parenthesis 行:6 文本:INSERT INTO HANDLELOG values ('luxiaofei','10.143.238.90','10.143.238.90',TO_NUMBER(to_cha(SYSDATE,'yyyymmddhh)),'Other','AAAA-01-0001','添加用户操作成功【'||:new.ACCOUNT||'】',0);错误:PL/SQL: SQL Statement ignored 行:6 文本:INSERT INTO HANDLELOG values ('luxiaofei','10.143.238.90','10.143.238.90',TO_NUMBER(to_cha(SYSDATE,'yyyymmddhh)),'Other','AAAA-01-0001','添加用户操作成功【'||:new.ACCOUNT||'】',0);错误:PL/SQL: ORA-00907: missing right parenthesis 行:11 文本:INSERT INTO HANDLELOG values ('luxiaofei','10.143.238.90','10.143.238.90',TO_NUMBER(to_cha(SYSDATE,'yyyymmddhh)),'Other','AAAA-01-0002','修改用户基本信息操作成功【'||:new.ACCOUNT||'】',0);错误:PL/SQL: SQL Statement ignored 行:11 文本:INSERT INTO HANDLELOG values ('luxiaofei','10.143.238.90','10.143.238.90',TO_NUMBER(to_cha(SYSDATE,'yyyymmddhh)),'Other','AAAA-01-0002','修改用户基本信息操作成功【'||:new.ACCOUNT||'】',0);错误:PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
; 行:15CREATE OR REPLACE TRIGGER TR_DML_tableaaa AFTER INSERT OR UPDATE ON tableaaa FOR EACH ROW BEGIN IF INSERTING THEN INSERT INTO HANDLELOG values (TO_NUMBER(to_cha(SYSDATE,'yyyymmddhh)),'Other','AAAA-01-0001','添加用户操作成功【'||:new.ACCOUNT||'】',0); ELSIF UPDATING THEN IF (:new.USER_STATUS=2) THEN INSERT INTO HANDLELOG values (TO_NUMBER(to_cha(SYSDATE,'yyyymmddhh)),'Other','AAAA-01-0003','删除用户操作成功',0); ELSE INSERT INTO HANDLELOG values (TO_NUMBER(to_cha(SYSDATE,'yyyymmddhh)),'Other','AAAA-01-0002','修改用户基本信息操作成功【'||:new.ACCOUNT||'】',0); END IF; END IF; END;
处理...为了能够读取当前行改变前后各列的值,需要借助于行触发器定义的关联名。
OLD-修改之前的行
NEW-修改之后的行
对于INSERT语句只有NEW,而OLD没有意义
对于DELETE语句只有OLD,而NEW没有意义
如果是在PL/SQL块内访问关联名,则需要在关联名前加“:”,如同访问外部变量
如果在PL/SQL块外访问关联名,则不需要加“:”
AFTER UPDATE ON table1
FOR EACH ROWBegin
IF(:new.XXX=1) then
....
ELSE
...
END IF;
End;
CREATE OR REPLACE TRIGGER TR_DML_TableAAA
AFTER INSERT OR DELETE OR UPDATE ON TableAAA
FOR EACH ROW
when new.account=1 //想这么加条件,但是对于把account修改成其它值的update也要插入值的
BEGIN
IF INSERTING THEN
INSERT INTO LOG
values ('root',SYSDATE,'Other','AAAA-01-0001','修改用户操作成功【'||new.ACCOUNT||'】',0);
ELSIF UPDATING THEN
INSERT INTO HANDLELOG
values ('root',SYSDATE,'Other','AAAA-01-0002','修改用户操作成功【'||new.ACCOUNT||'】',0);
ELSIF UPDATING THEN //应该在这加判断是否把account改成1
INSERT INTO HANDLELOG
values ('root',SYSDATE,'Other','AAAA-01-0002','修改用户操作成功【'||111111||'】',0);
END IF;
END;
....
ELSEIf updating then
...
ELSEIf inserting then
...
ELSEIf deleting then
...
END IF;这样写行吗?
IF updating then
IF :new.abcd = 1 then
...
ELSE
...
END IF;
ELSIF inserting then
...
ELSIF deleting then
...
END IF;
CREATE OR REPLACE TRIGGER TR_DML_AAA
AFTER INSERT OR UPDATE ON tableAAA
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO HANDLELOG
values (SYSDATE,'Other','AAAA-01-0001','添加用户操作成功【'||:new.ACCOUNT||'】',0);
ELSIF UPDATING THEN
IF (:new.USER_STATUS=2) THEN
INSERT INTO HANDLELOG
values (SYSDATE,'Other','AAAA-01-0003','删除用户操作成功',0);
ELSE
INSERT INTO HANDLELOG
values (SYSDATE,'Other','AAAA-01-0002','修改用户基本信息操作成功【'||:new.ACCOUNT||'】',0);
END IF;
END IF;
END;
values (SYSDATE,'Other','AAAA-01-0001','添加用户操作成功【'||:new.ACCOUNT||'】',0);
AFTER UPDATE or INSERT or DELETE ON table1
FOR EACH ROWBegin
IF updating then
IF (:new.abcd = 1) then
...
ELSE
...
END IF;
ELSIF inserting then
...
ELSIF deleting then
...
END IF;End;
行:6
文本:INSERT INTO HANDLELOG values ('luxiaofei','10.143.238.90','10.143.238.90',TO_NUMBER(to_cha(SYSDATE,'yyyymmddhh)),'Other','AAAA-01-0001','添加用户操作成功【'||:new.ACCOUNT||'】',0);错误:PL/SQL: SQL Statement ignored
行:6
文本:INSERT INTO HANDLELOG values ('luxiaofei','10.143.238.90','10.143.238.90',TO_NUMBER(to_cha(SYSDATE,'yyyymmddhh)),'Other','AAAA-01-0001','添加用户操作成功【'||:new.ACCOUNT||'】',0);错误:PL/SQL: ORA-00907: missing right parenthesis
行:11
文本:INSERT INTO HANDLELOG values ('luxiaofei','10.143.238.90','10.143.238.90',TO_NUMBER(to_cha(SYSDATE,'yyyymmddhh)),'Other','AAAA-01-0002','修改用户基本信息操作成功【'||:new.ACCOUNT||'】',0);错误:PL/SQL: SQL Statement ignored
行:11
文本:INSERT INTO HANDLELOG values ('luxiaofei','10.143.238.90','10.143.238.90',TO_NUMBER(to_cha(SYSDATE,'yyyymmddhh)),'Other','AAAA-01-0002','修改用户基本信息操作成功【'||:new.ACCOUNT||'】',0);错误:PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
;
行:15CREATE OR REPLACE TRIGGER TR_DML_tableaaa
AFTER INSERT OR UPDATE ON tableaaa
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO HANDLELOG values (TO_NUMBER(to_cha(SYSDATE,'yyyymmddhh)),'Other','AAAA-01-0001','添加用户操作成功【'||:new.ACCOUNT||'】',0);
ELSIF UPDATING THEN
IF (:new.USER_STATUS=2) THEN
INSERT INTO HANDLELOG values (TO_NUMBER(to_cha(SYSDATE,'yyyymmddhh)),'Other','AAAA-01-0003','删除用户操作成功',0);
ELSE
INSERT INTO HANDLELOG values (TO_NUMBER(to_cha(SYSDATE,'yyyymmddhh)),'Other','AAAA-01-0002','修改用户基本信息操作成功【'||:new.ACCOUNT||'】',0);
END IF;
END IF;
END;
INTO Result
FROM DUAL;
update了lastday的值,不执行任何操作
其它update操作,插入新记录到另一个表--已解决
update了lastday的值,不执行任何操作
=====================
逻辑上他们可以同时发生,如果你逻辑理清了,
用:new.lastday <> :old.lastday判断。