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 TypeTrigger 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;
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.133.134.151