Outline

1
Outline

• Data creation and destruction
• Inserting into a table
• Deleting from a table
• Modifying values in a table
• Other commonly used features
• Views
• Transactions and triggers
• Summary

2
Transactions

• A transaction identifies an elementary unit of
  work carried out by an application, to which we
  wish to allocate particular characteristics of
  reliability and isolation.
• A system that makes available mechanisms for the
  definition and execution of transactions is
  called a transaction processing system.
• Transactions are initiated with any SQL statement
  that modifies the database.

3
Transactions

• A transaction can be defined syntactically each
  transaction, irrespective of the language in
  which it is written, is enclosed whthin two
  commands
• begin transaction
• end transaction
• Within the transaction code, two particular
  instructions can appear
• commit work
• rollback work

4
Transaction example
An example of transaction is given in the
following code begin transaction X x 10 Y
y 10 Commit work end transaction
5
Active databases

• An active database system is a DBMS that supports
  an integrated subsystem for the definition and
  management of production rules.
• The rules follow the event condition action
  paradigm each rule reacts to some events,
  evaluates a condition and, based on the truth
  value of the condition, might carry out an
  action.
• The execution of the rules happens under the
  control of an autonomous subsystem, known as the
  rule engine, which keeps track of the events that
  have occurred and schedules the rules for
  execution.
• One of the active rules called triggers.

6
Triggers (on tables)

• The creation of triggers is part of the DDL.
• Maintain data integrity
• Associated with a table
• Event-condition-action
• Wait for a table event
• On event, evaluate condition
• If condition is true, execute action
• Table-level vs. row-level

insertion deletion update
before after
X
7
Level of Trigger

• Table-level trigger
• Works with entire table
• Action evaluated with respect to before image or
  after image, prior to commit
• Row-level trigger
• Works with entire table and a buffer of changes
• New - tuples that were inserted
• Old - tuples that were deleted
• Update is insertion deletion
• Trigger iterates through buffer of changes
• Action evaluated with respect to before image or
  after image, prior to the work being committed
  

8
Table-level Insertion Event

• The Person table associates Names and Cities.
• INSERT INTO Person(Name, City) VALUES (Jill,
  Cork)
• Before insertion, SELECT COUNT() will result in
  3
• After insertion, SELECT COUNT() will result in 4
  

Before
After
City
Name
City
Name
Joe
Dublin
Joe
Dublin
Susan
Cork
Susan
Cork
Cork
Juan
Cork
Juan
Jill
Cork
9
Table-level Deletion Event

• The Person table associates Names and Cities.
• DELETE FROM Person WHERE City Cork
• SELECT COUNT() FROM Person
• 3 in before image, 1 in after image

Before
After
City
Name
City
Name
Joe
Dublin
Joe
Dublin
Susan
Cork
Cork
Juan
10
Table-level Update Event

• The Person table associates Names and Cities.
• UPDATE Person SET City Dublin WHERE City
  Cork
• SELECT Name FROM Person WHERE CityDublin
• 3 in before image, 3 in after image

Before
After
City
Name
City
Name
Joe
Dublin
Joe
Dublin
Susan
Dublin
Susan
Cork
Dublin
Juan
Cork
Juan
11
Oracle Table-level Trigger Body

• PL/SQL program
• Example At most 100 people can live in Dublin
• After update/insert into Person
• DECLARE Declare Variables
• C INTEGER
• BEGIN Body of Trigger
• SELECT COUNT(Name) INTO C
• FROM Person
• WHERE City Dublin
• IF (C gt 100) THEN
• RAISE_APPLICATION_ERROR(-20000, too many in
  Dublin)
• END IF
• END

12
Row-level Insertion Event

• The Person table associates Names and Cities.
• INSERT INTO Person(Name, City) VALUES (Jill,
  Cork)

Before
After
City
Name
City
Name
Joe
Dublin
Joe
Dublin
Susan
Cork
Susan
Cork
Cork
Juan
Cork
Juan
Jill
Cork
Old
New
Buffer
City
Name
Jill
Cork
13
Row-level Deletion Event

• The Person table associates Names and Cities.
• DELETE FROM Person WHERE City Cork

Before
After
City
Name
City
Name
Joe
Dublin
Joe
Dublin
Susan
Cork
Cork
Juan
Old
New
City
Name
Buffer
Susan
Cork
Cork
Juan
14
Row-level Update Event

• The Person table associates Names and Cities.
• UPDATE Person SET City Dublin WHERE City
  Cork

Before
After
City
Name
City
Name
Joe
Dublin
Joe
Dublin
Susan
Dublin
Susan
Cork
Dublin
Juan
Cork
Juan
Old
New
Buffer
City
Name
City
Name
Susan
Dublin
Susan
Cork
Dublin
Juan
Cork
Juan
15
Potential Applications

• Notification
• an active database may be used to monitor
• Enforce integrity constraints
• Business roles
• maintenance of derived data
• Maintain the derived attribute whenever
  individual tuples are changed

16
Trigger Gotchas

• Potentially infinite loop
• Trigger A On insertion into Person, insert into
  Population
• Trigger B On insertion into Population, insert
  into Person
• Mutating tables
• Trigger A On insertion into Person, insert into
  Person!
• Disallowed!
• Trigger cannot make changes to table that trigger
  is defined on

17
Summary

• Schema definition
• CREATE TABLE
• CREATE VIEW
• ALTER
• DROP
• Queries
• SELECT

18
Summary, cont.

• Modifications
• INSERT
• DELETE
• UPDATE
• Transaction Management
• COMMIT
• ROLLBACK
• Active Database
• Trigger