创建行级触发器使用了for each now,列标识符:OLD.COLUMN_NAME和:NEW.COLUMN_NAME。
1、:NEW新值标识符,新的记录值;
:OLD为原值标识符,保留原来的记录值
2、注意:
<1>这两个列值变量只有在使用了“FOR EACH ROW”关键字时,即为行触发器才有的。
<2>UDDATE语句中:NEW和:OLD都存在;
<3>INSERT语句中,只有:NEW存在,:OLD不存在;
<4>DELETE语句中,只存在:OLD,不存在:NEW。
实例1:
CREATE OR REPLACE TRIGGER MyTestTrigger
AFTER INSERT OR UPDATE OR DELETE ON TableName
FOR EACH ROW
DECLARE_error exception;
BEGIN
CASE
WHEN inserting THEN
-- Omit part of the code
:NEW.hire_date := TRUNC(:NEW.hire_date);
:NEW.salary := ROUND(:NEW.salary);
:NEW.department := UPPER(:NEW.department); WHEN updating THEN -- Omit part of the code WHEN deleting THEN -- Omit part of the code END CASE; EXCEPTION WHEN _error THEN dbms_output.put_line('Error during execution');
END;
实例2:
create or replace trigger update_depart_trigger
after update on t_depart for each row
begin update t_user u set u.departid = :new.departid
where u.departid = :old.departid;
end update_depart_trigger;