6.5 Example Row Triggers


Consider the following table:

 
 CREATE TABLE TEMP(X NUMBER, Y NUMBER, Z NUMBER DEFAULT 5); 
  1. 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; 
  2. 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; 
  3. 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.

  4. 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; 


Programming Oracle Triggers and Stored Procedures
Programming Oracle Triggers and Stored Procedures (3rd Edition) (Prentice Hall PTR Oracle Series)
ISBN: 0130850330
EAN: 2147483647
Year: 2003
Pages: 111
Authors: Kevin Owens

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net