Triggers and Cascading Update Demo

• This is a demo
  • We will pick up some ideas
  • Some syntax
  • And some other parts.
  • But there will be gaps
  • Which we will come back and fill.
• Consider the following database
  • JOB (JobName, LowRange, HighRange)
  • PERSON(Name, JobName, Salary)
  • PHONE(ID,Name,PhoneNumber);
  • ERROR_TABLE (ID, Time, Message)
  • 
  • It is required that JOB.LowRange ≤ JOB.HighRange
  • If a job changes everyone in that position is allowed to pick a new job.
  • If a job is eliminated, everyone in the company with that job is eliminated as well.
  • If an employee is eliminated, their phone information should be eliminated as well.
  • The ERROR_TABLE is used to log errors
• There is a trigger, TestRangeTrigger
  • It is associated with the JOB table.
  • Every insert is checked for valid range.
  • If an invalid range is encountered
    • The range is fixed
    • And an error is logged.
  • Several other options are presented.
    • Fail and insert nothing
    • Return an error message (But not in MySQL < 5.5)
• Trigger implementation details
  • Reference
  • delimiter x
    • When writing stored code, the ; is needed by the DBMS
    • delimeter char changes the delimiter used by the mysql client.

      delimiter #
      /* code need ; to terminate statements */
      delimiter ;
      	     

  • local variables
    • DECLARE id vartype
    • DECLARE msg VARCHAR(200);
    • scope and id limits later
    • SET id = expression
  • IF in MYSQL

    • IF condition THEN
          statements
      ELSEIF condition THEN
          statements
      ELSE
          statements
      END IF
      	       

    • Reference
  • Access to new and old values in the table.
    • Operates on each row changed by the query.
    • In INSERT triggers NEW.ColumnName refers to the value of ColumnName in the new row. (No OLD)
    • In DELETE triggers OLD.ColumnName refers to the value of ColumName in the existing row, but it is read only.
    • In an UPDATE you have both.
    • In BEFORE you can change NEW, but OLD is read only.
    • In after, you can change NEW, but it is already in the table.
    • BEFORE - auto_increment fields are 0.
  • Restrictions
    • Cannot call procedures that return data to the client.
    • But you can return values through parameters.
    • Cannot begin/end transactions
    • Cannot perform output (SELECT for example)
    • But we can step through tables with cursors (Later)
  • Errors:
    • Note: Cancelled meains no further action is taken, and the table is not modified (ROLLBACK)
    • BEFORE triggers are called.
    • If it fails the transaction is cancelled
    • INSERT/UPDATE/DELETE is called
    • If it fails the transaction is cancelled
    • AFTER triggers are called
    • If it fails ROLLBACK
    • The transaction is complete and all data is stored.
  • Some notes:
    • There is no good way to return an error from a trigger in 5.1
    • Signals were implemented in 5.5 so there is a better way with the new mysql.
    • A common "Hack" is to force an error
• Constraint modification
  • Really nothing new here after last time,
  • Just some examples we can play with.
• Look at ~dbennett/313/ch6/cascade.sql