Oracle trigger inserting or updating
CREATE [ OR REPLACE ] TRIGGER trigger_name AFTER INSERT or UPDATE or DELETE ON table_name [ FOR EACH ROW ] DECLARE -- variable declarations BEGIN -- trigger code EXCEPTION WHEN ...
-- exception handling END; OR REPLACE: It is an optional parameter.
CASE WHEN INSERTING THEN -- Include any code specific for when the trigger is fired from an INSERT. WHEN UPDATING THEN -- Include any code specific for when the trigger is fired from an UPDATE. WHEN DELETING THEN -- Include any code specific for when the trigger is fired from a DELETE. DML triggers have four basic timing points for a single table. With the exception of Compound Triggers, the triggers for the individual timing points are self contained and can't automatically share state or variable information.This is because compound trigger in oracle 11g has a declarative section where one can declare variable to be used within trigger.This common state is established at the start of triggering statement and is destroyed after completion of trigger (regardless of trigger being in error or not).Two popular reasons to use compound trigger are: CREATE OR REPLACE TRIGGER compound_trigger_name FOR [INSERT|DELETE]UPDATE [OF column] ON table COMPOUND TRIGGER -- Declarative Section (optional) -- Variables declared here have firing-statement duration.--Executed before DML statement BEFORE STATEMENT IS BEGIN NULL; END BEFORE STATEMENT; --Executed before each row change- : NEW, : OLD are available BEFORE EACH ROW IS BEGIN NULL; END BEFORE EACH ROW; --Executed aftereach row change- : NEW, : OLD are available AFTER EACH ROW IS BEGIN NULL; END AFTER EACH ROW; --Executed after DML statement AFTER STATEMENT IS BEGIN NULL; END AFTER STATEMENT; END compound_trigger_name; Hopefully this example with make things more clear. SALARY IS NULL) ) THEN v_index := v_index 1; v_emp_changes(v_index).upd_dt := SYSDATE; v_emp_changes(v_index).upd_by := SYS_CONTEXT ('USERENV', 'SESSION_USER'); v_emp_changes(v_index).emp_id := : NEW.emp_id; v_emp_changes(v_index).field := 'SALARY'; v_emp_changes(v_index).from_value := to_char(: OLD.