=====
CS 328 - Week 6 Lecture 1 - 2024-02-19
=====

=====
TODAY WE WILL
=====
*   announcements
*   intro PL/SQL triggers
*   prep for next class

*   should be starting Homework 5!
    *   at-least-first attempts at problems due
        by 11:59 pm on Sunday, February 25

    *   SEE BELOW though -- final versions due
        by 11:59 pm on MONDAY, February 26!

=====
REMINDER:
==== ****** ====
NEW EXAM 1 DATE - WEDNESDAY, FEBRUARY 28 <===========
==== ****** ====

*   Exam 1 review - moving to Wednesday, February 21
*   zyBooks Chapters 1, 2 deadline - moving to Friday, February 23
*   Exam 1 - moving to Wednesday, February 28

=====
UPCOMING SCHEDULE
=====
*   today: intro to triggers (not on Exam 1,
    BUT PL/SQL will be...)

*   Wednesday, Feb. 21 - review for Exam 1

*   Friday, Feb. 23 - lab execise practicing triggers

    11:59 pm Friday, Feb 23 - deadline for credit for
        zyBooks Chapters 1, 2 activities

*   Sunday, Feb. 25 - attempts at Homework 5 are due

*   Monday, Feb. 26 - start discussing CSS (not on Exam 1)

    11:59 pm Monday, Feb. 26 - any final improved versions
        of problems from Homeworks 1-5 are DUE

    12:01 am Tuesday, Feb. 27 - selected EXAMPLE SOLUTIONS
        for Homeworks 1-5 should be reachable on Canvas

*   Wednesday, Feb. 28 - Exam 1

*   Friday, Mar. 1 - lab exercise playing with CSS

*   (and Homework 6 comes out the weekend of Mar 2-3)

=====
triggers!
=====
*   trigger - a subroutine one does not call directly --
    instead it is executed (or triggered) when a particular
    action occurs,

    here, a particular action on a particular database table

*   why?
    *   can help enforce database integrity

    *   can help have more-customized domain integrity

    *   (can maybe protect your data more especially
        when potential applications written by potentially
	many programmers will be using it)

*   a trigger CAN check for stuff BEFORE a desired action,
    and PREVENT it if it deems it unaccaptable;

    a trigger CAN perform additional needed/desired actions
    AFTER an action, to make sure they get done

=====
BASIC PL/SQL trigger syntax:

*   remember: [ ] - optional
              { a | b | c} - choose ONE of a or b or c

CREATE OR REPLACE TRIGGER desired_trig
    {BEFORE | AFTER}
    {DELETE | INSERT | UPDATE [OF column_name1 [, column_name2 ...]]
        [OR {DELETE | INSERT | UPDATE [OF column_name1 [, column_name2 ...]]}}
    ON desired_table
    [FOR EACH ROW
        [WHEN (bool_expr)]]
[DECLARE
    local_var var_type;
    local_var var_type;
    ...]
BEGIN

[EXCEPTION
   WHEN ... THEN
       statements;
   ...]
END;
/
show errors

*   FOR EACH ROW - means to fire the trigger
    once for each individual row affected by the specified
    triggering action

*   the Oracle DBMS seems to be very concerned about
    preventing "circular" trigger firings --

    for example, you are not allowed to perform a
    SELECT on the table the trigger is for...!

    you'll get THIS error message:

    ORA-04091: table ST10.EMPL is mutating, trigger/function may not see it

    *   HOWEVER, frequently, this is OK, because
        there IS a way to grab some useful info about
	the triggering action;

*   for an insert,

    :new.attrib_name

    has as its value the value of attrib_name in the
    row being inserted

*   for a delete,

    :old.attrib_name

    has as its value the value of attrib_name in the
    row being deleted

*   for an update,

    :old.attrib_name

    has as its value the value of attrib_name in the
    row being updated BEFORE the update,

    :new.attrib_name

    has as its value the value of attrib_name in the
    row being updated AFTER the update
    
*   how can a BEFORE trigger prevent thre triggering action?

    it can call the procedure:

    raise_application_error(desired_err_num, desired_err_msg);

    *   desired_err_num - a negative integer in the range -20000 .. -20999

        desireds_err_msg - a character string up to 2048 bytes long

=====
CHECKED AFTER CLASS - yep, in CURRENT Oracle versions,
    you CAN have multiple triggers on the SAME table!
=====
*   but you have to keep em STRAIGHT,

    and the ORDER of their FIRING/executing will be UNDETERMINED
    unless you include a FOLLOWS clause...
=====

BUT -- **if** you would PREFER,
*   there IS syntax for
    writing a single trigger for different actions on the same table:

    create or replace trigger my_new_trigger
        BEFORE update OR delete OR insert
	ON desired_table
	FOR EACH ROW

    *   inside this trigger,
        you can use a boolean expression

	INSERTING - will be true if this was triggered on an insert
        UPDATING - will be true if this was triggered on an update
	DELETING - will be true if this was triggered on a delete

   if inserting then
       ....
   elsif updating then
       ....
   elsif deleting then
       ...,
   end if;