Published on January 17, 2008
Trigger Writingfor Fun and Profit: Trigger Writing for Fun and Profit Presented by: Larry Holder Database Administrator The University of Tennessee at Martin March 21, 2007 Course ID: 2 Session Rules of Etiquette: Session Rules of Etiquette Please set your cell phone or pager on “stun”. If you must leave the session early, please do so as discreetly as possible, and don’t let the door hit you on the way out. Please avoid side conversation during the session, unless you are discussing what a fantastic job I’m doing up here... Thanks a Gigabyte! Introduction: Introduction This session provides an introduction to writing database triggers, along with several real-world examples that can enhance your support and functionality of Banner. What’s a Trigger?: What’s a Trigger? What’s a Trigger?: What’s a Trigger? We ain’t talkin’ about Roy’s noble steed... Ok, so... what’s a trigger?: Ok, so... what’s a trigger? A PL/SQL object, akin to a Procedure. Most often, a cool, calm & calculated reaction to an event, such as insert, update, delete, or even select, on a table. Sometimes, a reaction to a database event, such as a user logging onto the database. What can I do with it?: What can I do with it? Modify a field before it is inserted or updated. Insert, update, or delete data in other tables. Perform additional error-checking, and prevent an unwanted insert, update, or delete from occurring. Notify someone by email about an event. What schema owns the custom triggers?: What schema owns the custom triggers? We chose to let SATURN own our custom triggers associated with SATURN-owned tables; likewise for GENERAL, WTAILOR, etc. Be sure the owner of the trigger is granted rights on any tables it references or modifies that are owned by another schema. What naming convention is used?: What naming convention is used? UTM_ST_SARADAP_PRE_IU_ROW A pre-insert / update trigger, at the row level, on the table SARADAP. “UTM” is our prefix, and “ST” is standard for “Student Trigger”. Remember 30-character object name max within Oracle. Add numeric suffix if a tie-breaker is needed (multiple triggers on same table are allowed). Special variables: Special variables :old.spraddr_street_line1 meaningless for INSERT, will yield NULL :new.spraddr_street_line1 Special variables: Special variables INSERTING, UPDATING, DELETING Useful if trigger handles more than one mode, such as both INSERT and UPDATE, and your logic needs to know which occurred. IF INSERTING THEN do this; ELSE do that; END IF; Special variables: Special variables USER - the Oracle userid of who is connected For self-service, keep in mind that the userid is likely something like WWW_USER. SYSDATE - the current date and time DATABASE_NAME Useful to differentiate between production and test: IF substr(DATABASE_NAME,1,4) = 'PROD' Examples of Before/After & Row/Statement: Examples of Before/After & Row/Statement create or replace trigger trigger_name BEFORE INSERT or UPDATE on table_name for each ROW create or replace trigger trigger_name AFTER DELETE on table_name statement level if no "for each row" Firing order: 1. Before Statement (once only) 2. Before Row (once per each affected row) 3. The actual DML statement 4. After Row (once per each affected row) 5. After Statement (once only) Example: Modifying fields "before": Example: Modifying fields "before" create or replace trigger utm_st_sarpers_pre_in_up_row BEFORE INSERT or UPDATE on SARPERS for each ROW BEGIN IF :new.sarpers_last_name IS NOT NULL THEN IF :new.sarpers_last_name = UPPER(:new.sarpers_last_name) OR :new.sarpers_last_name = LOWER(:new.sarpers_last_name) THEN :new.sarpers_prefix := INITCAP(:new.sarpers_prefix); :new.sarpers_first_name := INITCAP(:new.sarpers_first_name); :new.sarpers_middle_name1 := INITCAP(:new.sarpers_middle_name1); :new.sarpers_middle_name2 := INITCAP(:new.sarpers_middle_name2); :new.sarpers_last_name := INITCAP(:new.sarpers_last_name); :new.sarpers_suffix := INITCAP(:new.sarpers_suffix); :new.sarpers_nickname := INITCAP(:new.sarpers_nickname); :new.sarpers_combined_name := INITCAP(:new.sarpers_combined_name); :new.sarpers_former_name := INITCAP(:new.sarpers_former_name); END IF; END IF; END; / Example: Writing to an additional table: Example: Writing to an additional table create or replace trigger utm_st_sarctrl_post_ins_row AFTER INSERT on SARCTRL for each ROW DECLARE PRAGMA AUTONOMOUS_TRANSACTION; BEGIN insert into saraatt values (:new.sarctrl_pidm, :new.sarctrl_term_code_entry, :new.sarctrl_appl_no_saradap, 'WAPP', sysdate); COMMIT; EXCEPTION WHEN OTHERS THEN NULL; COMMIT; END; / Note the use, in this example, of autonomous transaction, if you wish or need to commit an action independently of the transaction causing the trigger. Otherwise, a trigger cannot include a commit. Example: Additional error checking: Example: Additional error checking create or replace trigger utm_st_spriden_pre_insert_row BEFORE INSERT on SPRIDEN for each ROW BEGIN IF :new.spriden_change_ind IS NULL THEN IF (SUBSTR(:new.spriden_id,1,3) = '960' OR SUBSTR(:new.spriden_id,1,1) = '-') OR (SUBSTR(:new.spriden_id,1,1) > '9' AND :new.spriden_entity_ind = 'C') OR (LENGTH(:new.spriden_id) <= 7 AND :new.spriden_entity_ind = 'C') THEN NULL; ELSE RAISE_APPLICATION_ERROR (-20501, 'UTM ERROR *** ADDING THIS ID VALUE IS BLOCKED'); END IF; END IF; END; / Example: Sending an email notification: Example: Sending an email notification create or replace trigger utm_st_sortest_post_del_row after DELETE on SORTEST for each ROW w_conn UTL_SMTP.connection; w_crlf varchar2(2) := CHR(13) || CHR(10); w_mailhost varchar2(30) := 'your_email_server'; w_mesg varchar2(4000); w_from varchar2(30) := 'your_from_address'; w_to varchar2(30) := 'your_recipient_address'; w_test_email varchar2(30) := 'your_reciepient_for_non_production'; w_stu_id varchar2(9); w_stu_name varchar2(100); BEGIN BEGIN IF SUBSTR(DATABASE_NAME,1,4) != 'PROD' THEN w_to := w_test_email; END IF; select spriden_id, spriden_first_name || ' ' || spriden_last_name into w_stu_id, w_stu_name from spriden where spriden_pidm = :old.sortest_pidm and spriden_change_ind is null; continued . . . Example: Sending an email... (continued): Example: Sending an email... (continued) w_mesg := 'Date: ' || TO_CHAR(SYSDATE,'DD Mon YY HH24:MI:SS') || w_crlf || 'From: ' || w_from || w_crlf || 'Subject: ' || 'Deletion from SORTEST (SOATEST)' || w_crlf || 'To: ' || w_to || w_crlf || w_crlf || user || ' deleted an entry for ' || w_stu_name || ' (' || w_stu_id || ') of' || ': code = ' || :old.sortest_tesc_code || ', date = ' || to_char(:old.sortest_test_date,'MM-DD-YYYY') || ', score = ' || :old.sortest_test_score || w_crlf || w_crlf || ' ' || w_crlf; w_conn := UTL_SMTP.open_connection(w_mailhost, 25); UTL_SMTP.helo(w_conn, w_mailhost); UTL_SMTP.mail(w_conn, w_from); UTL_SMTP.rcpt(w_conn, w_to); UTL_SMTP.data(w_conn, w_mesg); UTL_SMTP.quit(w_conn); EXCEPTION WHEN OTHERS THEN NULL; END; END; / Disabling and enabling a trigger: Disabling and enabling a trigger alter trigger [owner.]name DISABLE; alter trigger [owner.]name ENABLE; Automatically enabled when initially created. select * from dba_triggers where trigger_name like 'UTM%'; Mutating trigger example: Mutating trigger example BEGIN w_pidm := :new.spbpers_pidm; w_new_ssn := :new.spbpers_ssn; IF :new.spbpers_ssn IS NOT NULL THEN OPEN check_for_dup; FETCH check_for_dup INTO w_flag; CLOSE check_for_dup; IF w_flag = 'Y' THEN RAISE_APPLICATION_ERROR(-20501, '*** SSN already in use ***'); END IF; END IF; END utm_junk1; / create or replace trigger utm_junk1 BEFORE insert or update of spbpers_ssn on SPBPERS for each ROW DECLARE w_pidm spbpers.spbpers_pidm%type; w_new_ssn spbpers.spbpers_ssn%type; w_flag char(1) := NULL; CURSOR check_for_dup IS select distinct 'Y' from spbpers where spbpers_ssn = w_new_ssn and spbpers_pidm != w_pidm; Results (mutation) ...: Results (mutation) ... ORA-04091: table SATURN.SPBPERS is mutating, trigger/function may not see it ORA-06512: at "SATURN.UTM_JUNK1", line xx ORA-06512: at "SATURN.UTM_JUNK1", line xx ORA-04088: error during execution of trigger 'SATURN.UTM_JUNK1' Solution, part 1 of 3 (package): Solution, part 1 of 3 (package) create or replace package utm_st_spbpers_pkg as TYPE t_pidms IS TABLE OF spbpers.spbpers_pidm%TYPE INDEX BY BINARY_INTEGER; TYPE t_new_ssns IS TABLE OF spbpers.spbpers_ssn%TYPE INDEX BY BINARY_INTEGER; pidms t_pidms; new_ssns t_new_ssns; num_entries BINARY_INTEGER := 0; END utm_st_spbpers_pkg; / Solution, part 2 of 3 (before / row): Solution, part 2 of 3 (before / row) create or replace trigger utm_st_spbpers_pre_iu_row BEFORE insert or update of spbpers_ssn on SPBPERS for each ROW BEGIN utm_st_spbpers_pkg.num_entries := utm_st_spbpers_pkg.num_entries + 1; utm_st_spbpers_pkg.pidms (utm_st_spbpers_pkg.num_entries) := :new.spbpers_pidm; utm_st_spbpers_pkg.new_ssns (utm_st_spbpers_pkg.num_entries) := :new.spbpers_ssn; END utm_st_spbpers_pre_iu_row; Solution, part 3 of 3 (after / statement): Solution, part 3 of 3 (after / statement) create or replace trigger utm_st_spbpers_post_iu_stmt AFTER insert or update of spbpers_ssn on SPBPERS DECLARE w_num_entries utm_st_spbpers_pkg.num_entries%type; w_pidm spbpers.spbpers_pidm%type; w_new_ssn spbpers.spbpers_ssn%type; w_flag char(1) := NULL; w_error char(1) := 'N'; CURSOR check_for_dup IS select distinct 'Y' from spbpers where spbpers_ssn = w_new_ssn and spbpers_pidm != w_pidm; continued... Slide25: BEGIN w_num_entries := utm_st_spbpers_pkg.num_entries; FOR z_index IN 1..w_num_entries LOOP w_pidm := utm_st_spbpers_pkg.pidms (z_index); w_new_ssn := utm_st_spbpers_pkg.new_ssns (z_index); IF w_new_ssn IS NOT NULL THEN OPEN check_for_dup; FETCH check_for_dup INTO w_flag; CLOSE check_for_dup; IF w_flag = 'Y' THEN w_error := 'Y'; END IF; END IF; END LOOP; continued... Slide26: IF w_error = 'Y' THEN utm_st_spbpers_pkg.num_entries := 0; RAISE_APPLICATION_ERROR(-20501, '*** SSN ALREADY IN USE ***'); END IF; utm_st_spbpers_pkg.num_entries := 0; END utm_st_spbpers_post_iu_stmt; / New Results (no mutation) ...: New Results (no mutation) ... ORA-20501: *** SSN ALREADY IN USE *** ORA-06512: at "SATURN.UTM_SPBPERS_POST_IU_STMT", line xx ORA-04088: error during execution of trigger 'SATURN.UTM_SPBPERS_POST_IU_STMT' This is expected This is the application error that we intentionally raised Banner 7 "BTW...": Banner 7 "BTW..." I've found that the new API logic of Banner 7 has changed a few things... for example, the column SGBSTDN_LEVL_CODE is still NULL when initially inserted; a subsequent Update populates it. I changed several POST-INSERT triggers to POST-INSERT/UPDATE and added the following logic at the beginning, to handle the initial setting of the column regardless of whether done by Insert or Update... IF (INSERTING AND :new.sgbstdn_levl_code IS NULL) OR (UPDATING AND :old.sgbstdn_levl_code IS NOT NULL) THEN GOTO skip_everything; END IF; ... <<skip_everything>> NULL; Helpful Debugging Tip: Helpful Debugging Tip Consider creating a table to hold debug info, which you can write to from any trigger that you are testing... to show you values in :old and :new, for example, plus anything else you care to review. I used this to debug the Post-Insert triggers mentioned on the previous slide... Create Table Trigger_Debug (trigger_name varchar2(30) not null, datetime date not null, codepoint varchar2(30) not null, info1 varchar2(100), info2 varchar2(100), info3 varchar2(100); Create Public Synonym Trigger_Debug for Trigger_Debug; Grant All on Trigger_Debug to Public; w_debug char(1) := 'Y'; turn this on or off as needed IF w_debug = 'Y' THEN INSERT INTO TRIGGER_DEBUG VALUES ('MY_TRIGGER',SYSDATE,'BEGIN', :OLD.SGBSTDN_LEVL_CODE, :NEW.SGBSTDN_LEVL_CODE, NULL); Summary: Summary Triggers are a great way to extend the functionality of Banner without introducing “baseline modifications”. Rome wasn’t built in a day. Start out simply, and add more at your own pace. Questions & Answers: Questions & Answers Ok, it’s your turn... Thank You!: Thank You! Larry Holder [email protected] www.utm.edu/staff/lholder/dba Please complete the online class evaluation form Course ID: 2 SunGard, the SunGard logo, Banner, Campus Pipeline, Luminis, PowerCAMPUS, Matrix, and Plus are trademarks or registered trademarks of SunGard Data Systems Inc. or its subsidiaries in the U.S. and other countries. Third-party names and marks referenced herein are trademarks or registered trademarks of their respective owners. General presentation Copyright © 2006 Larry Holder (of The University of Tennessee at Martin). Portions © 2006 SunGard. All rights reserved. Photo of Roy Rogers and Trigger used by permission of the Roy Rogers-Dale Evans Museum, Branson, MO. No animals were harmed in the production of this presentation. No artificial ingredients added.