=====
CS 328 - Week 6 Lecture 1 - 2025-02-24
=====
=====
TODAY WE WILL:
=====
* announcements
* DATA TIER - continuing intro to PL/SQL
* intro to PL/SQL triggers
* prep for next class
=====
* should be working on Homework 5
* at-least-first-attempts due by 11:59 pm on Friday, February 28
* should be finishing the activities in zyBooks chapters 1-2
=====
UPCOMING SCHEDULE
=====
* today: intro to PL/SQL triggers
* Wednesday, February 26 - review for Exam 1 (clicker questions included!)
* Thursday, February 27/Friday, February 28
* CS Candidate TEACHING TALK at start of Thursday's lab -
*** BSS 204 ***
* 2 clicker-questions-worth of bonus clicker points
if attend AND sign sign-in sheet at BEGINNING of talk
* then move to BSS 313 for clicker questions followed by
short-ish lab exercise on PL/SQL triggers for remainder of
Thursday's lab, due 11:59 pm on Thursday
* (Friday's lab - normal time and place, lab exercise due at end of lab)
* 11:59 pm Friday, February 28
* at-least-first-attempts at Homework 5 problems due
* deadline for credit for zyBooks Chapters 1, 2 activities
* Monday, March 3 - start discussing CSS (not on Exam 1)
* 11:59 pm Monday, March 3
* any final improved versions of problems from
Homeworks 1-5 are DUE, so that...
* 12:01 am Tuesday, March 4
* selected EXAMPLE SOLUTIONS for Homeworks 1-5
can be made reachable on Canvas, for Exam 1 study use
* Wednesday, March 5 - Exam 1
* Thursday, March 6/Friday, March 7
* (Thursday's lab - normal time and place, lab exercise due at end of lab)
* CS Candidate TEACHING TALK at start of Friday's lab
*** BSS 204 ***
* 2 clicker-questions-worth of bonus clicker points
if attend AND sign sign-in sheet at BEGINNING of talk
* then move to BSS 313 for clicker questions followed by
short-ish lab exercise writing an external CSS style sheet
for remainder of Friday's lab, due 11:59 pm on Friday
* (Homework 6 comes out the weekend of March 8-9)
* Monday, March 10
* CS Candidate TEACHING TALK at start of lecture - SH 108
* 2 clicker-questions-worth of bonus clicker points
if attend AND sign sign-in sheet at BEGINNING of talk
* then regular lecture on more CSS
(which will be supplemented by a recorded, posted lecture)
* Wednesday, March 12 - more discuss of CSS
* (WILL be labs on Thursday, March 13/Friday, March 14!)
* (Homework 6's at-least-first-attempts will be due 11:59 pm
Friday, March 14)
* (Homework 7 comes out the weekend of March 15/16,
but will have at-least-first-attempts due
by 11:59 pm Friday, March ***28***
because of Spring Break March 17-21)
PLEASE contact me if you have any questions about the above!
=====
=====
PL/SQL triggers
=====
* trigger: a subroutine you don't directly call --
instead, it gets executed, or "fired", or "triggered",
when the appropriate action happens on the database
* here, an appropriate action is a specified action
on a specified database table
* why?
* can be useful for enforcing more-sophisticated data
integrity requirements;
* can allow enforcement of more-customized domain integrity
requirements
* for example, they can do more than a create table's
check clause can
* can check that certain requirements are met before
an action is allowed to proceed
* can make sure certain updates are made to other
tables after an action has been done
* (so, might help protect your data
when multiple 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:
=====
* basic trigger header syntax (there ARE more optional clauses!):
{ } - choose ONE of the choices within (separated by |)
[ ] - these are optional
CREATE OR REPLACE TRIGGER desired_trigger_name
{BEFORE | AFTER}
{DELETE | INSERT | UPDATE}
ON desired_table_name
[FOR EACH ROW]
[DECLARE
local_declarations_here]
BEGIN
statements;
[EXCEPTION
WHEN ...]
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;
=====
* sometimes a trigger needs more info about the action
that triggered it;
there is syntax for grabbing attribute values from
the action
* NOTE: trigger NEEEEEEDS to include FOR EACH ROW for this syntax to work!
* for a trigger on an insert:
:new.desired_attrib_name
...is the value of that attribute in the row being inserted
* for a trigger on a delete:
:old.desired_attrib_name
...is the value of that attribute in the row being deleted
* for a trigger on an update:
:old.desired_attrib_name
...is the value of that attribute in the row BEFORE it is updated
:new.desired_attrib_name
...is the value of that attribute in the row AFTER it is updated
* these take most (but not all!) of the sting out of the fact
that a trigger CAN. NOT. QUERY itself...
* that is, a trigger can contain a select on a table
that is NOT the table it is triggered on
(but NOT on the table the trigger is triggered on)
=====
raise_application_error
=====
* 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
desired_err_msg - a character string up to 2048 bytes long
=====
* in current Oracle versions, you CAN have multiple triggers on the
SAME table
* BUT -- you need to be careful, because if you have such multiple
triggers on the same action(s) for the same table,
the ORDER of the FIRING/execution might be difficult to determine
UNLESS you include a FOLLOWs clause
* you CAN also write ONE trigger that handles more than one possible
action -- for example, this trigger would fire on either
an update OR a delete OR an insert on desired_table:
create or replace trigger my_new_trigger
BEFORE UPDATE OR DELETE OR INSERT
on desired_table
...
* 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 DELETING then
...
elsif UPDATING then
...
else
...
end if;
* (and you can limit a trigger to only fire on an action on a
particular column,
or for each row meeting some specified boolean condition;
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)]]
...see the posted triggers reference on the course PL/SQL references
page if you are interested)