Triggers by cuiliqing

VIEWS: 6 PAGES: 4

									                                         CS 170 Class Notes - Triggers

A procedure that is executed automatically by the DBMS if a certain state/condition occurs in a database.

Useful for monitoring the state of data in a database (allows only valid changes to be made).

Examples.

         1. If an application has been accepted, update the enrollment in the college.
         2. A student with NULL SAT score cannot be admitted anywhere.

A trigger definition specifies: an event, a condition and an action.

The event validates the condition and triggers the action.

Event= a change in the database: INSERT, UPDATE or DELETE.
        In general it could also be a temporal events: trigger an action each day at 5:00 pm.

         Ex of events: Insert a new application (tuple) into Apply. Or update an existing one.

         The trigger action can be executed either BEFORE or AFTER or INSTEAD OF the event.

         Ex. of BEFORE trigger. Before updating an application status make sure that the student has
                                  a not NULL SAT.

         Ex. of AFTER trigger.    After accepting a student (i.e. updating the Apply relation) update
                                    the enrollment in that college.

        INSTEAD OF is only used with views.

Condition = A boolean condition; if it evaluates to true the trigger action is executed, otherwise
            not.
          = The condition is optional. If no condition is specified, then the trigger action is executed
           whenever the event is encountered.

Action= one or more SQL statements. The action is written in PL/SQL (a procedural language of
        Oracle).

         Ex. Update College table.


How triggers differ from assertions, or checks?

    -   More general than assertions. Used not only to maintain database consistency and integrity, but also to
        alert users of unusual events or to gather statistics.
    -   Triggers are invoked by an event that is specified by the programmer.
    -   Assertions or checks are invoked by the system, whenever they might be violated.
    -   Triggers are more difficult to manage. Example: recursive triggers.

Triggers in Oracle.

There is a standard trigger definition and usage given in SQL99, but each database has its own implementation
of triggers and its restrictions.
Example 1: Set the date of a new application equal to the system date.

                CREATE OR REPLACE TRIGGER Apply_before_insert
                BEFORE INSERT ON Apply
                FOR EACH ROW
                WHEN ( new.apply_date IS NULL)
                DECLARE
                  v_apply_date Apply.apply_date%TYPE;
                BEGIN
                  :NEW.apply_date:=SYSDATE;
                END;
                .
                RUN;

CREATE or REPLACE TRIGGER - creates a new trigger or replaces an old definition of the same
                                    trigger.
                                    Apply_before_insert – the name of the trigger
BEFORE INSERT ON Apply        - defines the event when the trigger should be executed
FOR EACH ROW                      - execute the trigger action for each row affected by the event
                                   The alternative is to execute the trigger once per event.
DECLARE                      - a PL/SQL block
      ....                   - the declaration part.
      v_apply_date Apply.apply_date%TYPE; - declare the variable v_apply_date as having the
                                                  same type as the apply_date column from Apply
                                                  table.
BEGIN
      ...
      :NEW.apply_date:=SYSDATE; - assign to each new inserted tuple in Apply the apply_date
                                          = to the system date.
                                        NEW refers to newly inserted tuples, to newly updates
                                        tuples.
                                        OLD refers to old tuples before updating, or before deleting.
END;
.
RUN;                         - executes the CREATE TRIGGER statements.

To see compilation errors type after the CREATE TRIGGER statement is executed:

> SHOW ERRORS

Test trigger:

        INSERT INTO Apply(sid, cid, major, decision) values(41, 273, 'CS', 'R');

        SELECT *
        FROM Apply
        WHERE sid = 41

Example 2. Update the enroll attribute in College whenever the decision attribute in Apply is
           changed from ‘R’ to ‘A’ or a new tuple is inserted in Apply with the decision value =
           ‘A’.

         CREATE OR REPLACE TRIGGER Update_Enroll
         AFTER INSERT or UPDATE ON Apply
         FOR EACH ROW
         WHEN (new.decision=’A’)
          BEGIN
               IF UPDATING THEN
                      IF :OLD.decision = ’R’ THEN
                             UPDATE College
                             SET enroll=enroll+1
                             WHERE cid=:new.cid;
                      END IF;
               ELSIF INSERTING THEN
                      UPDATE College
                      SET enroll=enroll+1
                      WHERE cid=:new.cid;
               END IF;
          END;
          .
          RUN;

Test the trigger:

         SELECT *
         FROM College
         WHERE cid = 126

         UPDATE Apply
         SET decision =’A’
         WHERE cid = 126

         SELECT *
         FROM College
         WHERE cid = 126

Important Oracle constraint: The action cannot change the relation that triggers the action.

Trigger 3: If the SAT of a student is NULL don't admit that student. Checked before an insert or an update in
Appy.

CREATE OR REPLACE TRIGGER check_sat
BEFORE INSERT OR UPDATE ON APPLY
FOR EACH ROW
WHEN (new.decision = 'A')
DECLARE
  v_SAT Student.sat%type;
BEGIN
  SELECT SAT into v_SAT
  FROM Student
  WHERE sid = :new.sid;

  if (v_SAT IS NULL) then
        :new.decision :='R';
  end if;
END;
/

Check trigger 3

insert into apply(sid, cid, major, decision) values(31, 228, 'CS', 'A');
select *
from apply
where sid = 31

Trigger 4

Create a trigger that does not allow a student with a GPA<3.0 to apply to colleges with rank < 40.

CREATE OR REPLACE TRIGGER CHECK_GPA
BEFORE INSERT OR UPDATE ON Apply
FOR EACH ROW
DECLARE
      v_gpa Student.gpa%type;
      v_rank College.rank%type;
      v_exception exception;
BEGIN
      select gpa into v_gpa
      from student
      where sid = :new.sid;

         select rank into v_rank
         from college
         where cid = :new.cid;

      if (v_gpa<3.0 AND v_rank < 40) then
               RAISE v_exception;
      end if;
EXCEPTION
      when v_exception then
               RAISE_APPLICATION_ERROR(-20001, 'Cannot do this');
END;
/

Check trigger 4

insert into apply(sid, cid, major, decision) values(21,206,'CS', 'R');

								
To top