oracle中before,Oracle中Before和After触发器的例子

做一个表的insert的trigger，目的是只修改插入行的字段。

CREATE OR REPLACE TRIGGER TR_RME_SLOT

BEFORE  INSERT ON RME_SLOT

FOR EACH ROW

BEGIN

IF (:NEW.POSITION >= 0 AND :NEW.POSITION <10) THEN

:NEW.SLOT_NAME := '0'||TO_CHAR(:NEW.POSITION);

ELSE

:NEW.SLOT_NAME := TO_CHAR(:NEW.POSITION);

END IF;

END;

在插入以前就需要修改插入行；在trigger实现中并不需要用到update语句

同时，如果要在trigger中实现对本表记录的修改，则需要这样写：

CREATE OR REPLACE TRIGGER TR_RME_SLOT

AFTER  INSERT ON RME_SLOT

FOR EACH ROW

DECLARE

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

IF (:NEW.POSITION >= 0 AND :NEW.POSITION <10) THEN

UPDATE RME_SLOT  SET SLOT_NAME = '0'||TO_CHAR(:NEW.POSITION) WHERE SLOT_ID=:NEW.SLOT_ID;

ELSE

UPDATE RME_SLOT  SET SLOT_NAME = TO_CHAR(:NEW.POSITION) WHERE SLOT_ID=:NEW.SLOT_ID;

END IF;

COMMIT;

END;

注意多了一段DECLARE，同时在trigger结束时需要COMMIT

CREATE OR REPLACE TRIGGER tr_AfterUpdate

AFTER INSERT OR UPDATE

ON TEST_TRIGGER

FOR EACH ROW

DECLARE

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

UPDATE TEST_TRIGGER

SET T_FACT = T_SALARY*(1-T_TAX)-T_EAT-T_HOUSE

WHERE T_NAME = :NEW.T_NAME;

COMMIT;

EXCEPTION

WHEN OTHERS THEN

-- Consider logging the error and then re-raise

RAISE;

END tr_testUpdate;