Blended PL/SQL 4 (final study guide)

Trigger Exceptions

Trigger is always part of the transaction statement - To block a transaction with trigger from completion, RAISE_APPLICATION_ERROR exception is utilized.

Trigger Exceptions Example

Trigger to prevent update of dob attribute value in the staff table create or replace trigger staff_dob before update of dob on staff for each row begin raise_application_error(-20000,'cannot change date of birth'); end; Trigger test: update staff set dob = to_date('1/12/85','mm/dd/yy') where staff_no = 'SA210';

Trigger Maintenance

Triggers can be disabled so that they still exist in the database, but do not fire when the triggering event occurs. ALTER TRIGGER trigger_name [ENABLE | DISABLE]; - drop triggers: -> DROP TRIGGER trigger_name;

Database Triggers

are like standard PL/SQL block programs that execute (fired) automatically in response to database events - The specific event is associated with either a table, a view, a schema, or the database, and is one of the following: 1. A database manipulation (DML) statement on table (delete, insert, or update). 2. A database definition (DDL) statement (create, alter, or drop). 3. A database operation (servererror, logon, logoff, startup, or shutdown).

Mutating Table

is a common error (ORA-4091) when triggers are not managed properly. - It is caused by row level triggers - is a table which while being modified by an INSERT, UPDATE, or DELETE statement encounters a SELECT or UPDATE statement in the body of the trigger that references the same table. - Note: - Trigger can call a procedure, but now the external (called) procedure will not be able to query individual rows of the trigger table. - example of mutating table trigger create or replace trigger staff_read_mutate after update of dob on staff for each row declare last_name_in staff.last_name%type; begin select last_name into last_name_in from staff where staff_no = 'SA200'; dbms_output.put_line(last_name_in); end; Trigger test: update staff set dob = to_date('1/12/85','mm/dd/yy') where staff_no = 'SA210';

Business Rules through Triggers Example

- Business Rule: Do not rent an apartment if there are any complaints pending on it. - trigger: - Update trigger on the apartment table that is fired every time a change is made from 'V' to 'R' in the value of the apt_status attribute. - The trigger checks to see if there are any complaints for the apartment. If any complaint is pending, then the update is cancelled. create or replace trigger apartment_bu before update on apartment for each row when (old.apt_status='V') declare cursor complaint_cursor is select complaint_no, status from complaints where apt_no=:old.apt_no; complaint_row complaint_cursor%rowtype; error1 exception; begin for complaint_row in complaint_cursor loop if (complaint_row.status is null) or (complaint_row.status = 'P') then raise error1; end if; end loop; exception when no_data_found then dbms_output.put_line('Update Ok'); when error1 then raise_application_error(-20100,'Update Cancelled'); end; Trigger Test: update apartment set apt_status = 'R' where apt_no = 103; update apartment set apt_status = 'R' where apt_no = 200;

DML Trigger Definition

- The trigger event is associated with the INSERT, UPDATE, or DELETE operations performed on the trigger table - Trigger is fired (or executed) when the trigger event occurs. - Possible to specify all three events in one trigger definition 1. before (timing) - levels: for each row, for each statement 2. DB trigger event statements - insert, update, delete statements 3. after (timing) - levels: for each row, for each statement - Trigger timing refers to the time when the execution of the trigger will occur. - The BEFORE timing implies that the trigger will be fired prior to the completion of the database event - The AFTER timing implies that the trigger will be fired after the actual database event. - Trigger levels refer to the scope of the trigger - row level - statement level - A row level trigger will fire once for each table row, while a statement level trigger will fire only once.

DML Statement Triggers

- Triggers cannot accept input parameters, commit or rollback statements. - When a trigger fires, all operations performed become part of the transaction. - trigger use: - Enforce referential integrity - Enforce business rules beyond constraints - Maintain security rules - Collect or audit information on table access - trigger definition includes: - specification of trigger event associated with database table - trigger timing - trigger levels

Row Trigger Example (after)

A trigger to fire after the insert takes place create or replace trigger staff_insert_after after insert on staff For each row begin dbms_output.put_line('after insert of '||:new.first_name||' '||:new.last_name); end; Trigger test: insert into staff (staff_no, first_name, last_name) values ('SA410','Jane','Doe');

Row Trigger Example (before)

A trigger to fire before the insert takes place create or replace trigger staff_insert_before before insert on staff for each row begin dbms_output.put_line('before insert of ' ||:new.first_name||' '||:new.last_name); end; Trigger test: insert into staff (staff_no, first_name, last_name) values ('SA400','John','Doe');

Row Trigger Example (during update)

A trigger to fire during update create or replace trigger staff_update_before before update on staff for each row begin dbms_output.put_line('before updating some staff '||:old.last_name||:new.dob); end; Trigger test: update staff set dob = sysdate;

Statement Trigger Example (during update)

A trigger to fire during update. create or replace trigger staff_update_before before update on staff begin dbms_output.put_line('before updating some staff '); end; Trigger test: update staff set dob = sysdate;

DML Trigger syntax

CREATE [OR REPLACE] TRIGGER trigger-name [BEFORE | AFTER] -> timing [INSERT | DELETE | UPDATE [OF attribute1 [,attribute2[, ...] ] ] ] -> event ON tablename [ REFERENCING {OLD [AS] old-name [NEW [AS] new-name] | NEW [AS] new-name [OLD [AS] old-name] } ] [FOR EACH ROW] -> level [WHEN (condition)] DECLARE ... BEGIN ... pl/sql statements ... END; - Note: when database refreshed, as table is dropped the triggers attached with the tables are also dropped. - OLD and NEW Reference (only for ROW level triggers) - OLD => Reference to an attribute value before the triggering event - :OLD.attribute (for Delete, Update) -> unit reference - NEW => Reference to an attribute value after the triggering event - :NEW.attribute (for Insert, Update) -> unit reference

Mutating Table Alternative

Since it is possible to reference any attribute value within a row of the trigger table using the OLD or NEW prefix, avoid a SELECT statement in the trigger. - The OLD prefix allows access to row values BEFORE the trigger is fired, while the NEW prefix allows access to row values AFTER the trigger is fired. - mutating alternative example: create or replace trigger staff_read_mutate after update of dob on staff for each row declare last_name_in staff.last_name%type; begin --select last_name --into last_name_in --from staff --where staff_no = 'SA200'; dbms_output.put_line(:old.last_name); -> referencing OLD value end; Trigger test: update staff set dob = to_date('1/12/85','mm/dd/yy') where staff_no = 'SA210'; - Any modification of an attribute value within a row can be done through an assignment statement instead of an UPDATE statement. create or replace trigger staff_update_mutate before update of dob on staff for each row begin --update staff --set salary = 25000 --where staff_no = 'SA200'; :new.salary := 25000; dbms_output.put_line('Update performed'); dbms_output.put_line('Old Salary '||:old.salary); dbms_output.put_line('New Salary '||:new.salary); end; Trigger test: update staff set dob = to_date('1/12/85','mm/dd/yy') where staff_no = 'SA200';