CREATE TABLE TEMP(X NUMBER, Y NUMBER, Z NUMBER DEFAULT 5);
Write a trigger that fires ONLY under the following conditions:
UPDATE when Y changes from NULL to a NOT NULL value.
INSERT when X is between 1 and 10.
CREATE OR REPLACE TRIGGER temp_aiur AFTER INSERT OR UPDATE OF Y ON TEMP FOR EACH ROW WHEN (OLD.Y IS NULL and NEW.Y IS NOT NULL OR NEW.X BETWEEN 1 AND 10) BEGIN CASE WHEN inserting THEN dbms_output.put_line('X := ':new.x); WHEN updating THEN dbms_output.put_line ('Y is reset from NULL'); END CASE; END;
Write a trigger to print the current values in a row being deleted.
CREATE OR REPLACE TRIGGER temp_adr AFTER DELETE ON TEMP FOR EACH ROW BEGIN dbms_output.put_line (:old.x' ':old.y' ':old.z); END;
Write all six possible row level triggers: BEFORE and AFTER ROW for INSERT, UPDATE, and DELETE.
Trigger Type
Trigger Template Code
BEFORE INSERT
CREATE OR REPLACE TRIGGER temp_bir BEFORE INSERT ON TEMP FOR EACH ROW BEGIN dbms_output.put_line('executing temp_bir'); END;
AFTER INSERT
CREATE OR REPLACE TRIGGER temp_air AFTER INSERT ON TEMP FOR EACH ROW BEGIN dbms_output.put_line('executing temp_air'); END;
BEFORE UPDATE (see note)
CREATE OR REPLACE TRIGGER temp_bur BEFORE UPDATE ON TEMP FOR EACH ROW BEGIN dbms_output.put_line('executing temp_bur'); END;
AFTER UPDATE (see note)
CREATE OR REPLACE TRIGGER temp_aur AFTER UPDATE ON TEMP FOR EACH ROW BEGIN dbms_output.put_line('executing temp_aur'); END;
BEFORE DELETE
CREATE OR REPLACE TRIGGER temp_bdr BEFORE DELETE ON TEMP FOR EACH ROW BEGIN dbms_output.put_line('executing temp_bdr'); END;
AFTER DELETE
CREATE OR REPLACE TRIGGER temp_adr AFTER DELETE ON TEMP FOR EACH ROW BEGIN dbms_output.put_line('executing temp_adr'); END;
Note. The OF COLUMN_NAME clause is optional on UPDATE row and UPDATE statement level triggers (statement level triggers are covered in Chapter 7). The WHEN (Boolean expression) is optional with all ROW triggers.
Write two triggers that accomplish the same as the prior six triggers.
CREATE OR REPLACE TRIGGER temp_biudr BEFORE INSERT OR UPDATE OR DELETE ON TEMP FOR EACH ROW BEGIN CASE WHEN inserting THEN dbms_output.put_line('inserting before'); WHEN updating THEN dbms_output.put_line('updating before'); WHEN deleting THEN dbms_output.put_line('deleting before'); END CASE; END; CREATE OR REPLACE TRIGGER temp_aiudr AFTER INSERT OR UPDATE OR DELETE ON TEMP FOR EACH ROW BEGIN CASE WHEN inserting THEN dbms_output.put_line('inserting after'); WHEN updating THEN dbms_output.put_line('updating after'); WHEN deleting THEN dbms_output.put_line('deleting after'); END CASE; END;