Triggers

1
Triggers
2
PL/SQL reminder

• We presented PL/SQL- a Procedural extension to
  the SQL language.
• We reviewed the structure of an anonymous PL/SQL
  block

DECLARE (optional) / Variable
declaration / BEGIN (mandatory)
/ Block action/ EXCEPTION (optional)
/ Exception handling / END
(optional) /
3
Example from last week

• DECLARE
• e_number1 EXCEPTION
• cnt NUMBER
• BEGIN
• select count()
• into cnt
• from number_table
• IF cnt 1 THEN RAISE e_number1
• ELSE dbms_output.put_line(cnt)
• END IF
• EXCEPTION
• WHEN e_number1 THEN
• dbms_output.put_line('Count 1')
• end

4
PL/SQL reminder-cont.

• We also showed the structures of procedures and
  functions

create or replace procedure num_logged (person
IN mylog.whoTYPE, num OUT mylog.logon_numTYPE)
IS BEGIN select logon_num into num
from mylog where who person END /
5
Triggers- introduction

• A trigger is an action which the Database should
  perform when some DB event has occurred.
• For example (in pseudocode)
• TriggerA
• For any row that is inserted into table
  Sailors
• if agegt30 -gt insert this row into
  oldSailors
• else-gt insert this row into youngSailors

6
Trigger introduction cont.

• The code within the trigger, called the trigger
  body, is made up of PL/SQL blocks
• The firing of a trigger is transparent to the
  user.
• There are many optional triggering events, but we
  will focus on update, delete, and insert.
• Triggers can be used to check for data integrity,
  but should be used so only if it is not possible
  through other means.

7
Types of triggers

• Row level triggers The code in the trigger is
  executed once for every row updated.
• Statement level triggers (Default) The code in
  the trigger is performed once per statement.
• For example if the triggering event was an
  update which updates 100 rows, a row-level
  trigger will execute 100 times, and a statement
  level trigger will execute once.

8
Types of triggers-cont.

• 1.BEFORE triggers The trigger fires immediately
  BEFORE the triggering event executes.
• 2.AFTER triggers The trigger fires immediately
  AFTER the triggering event executes.
• 3.INSTEAD OF triggers The trigger fires INSTEAD
  of the triggering event.
• We can reference the old and new values.
• If we want to change rows which will be inserted,
  we have to use a BEFORE trigger and change the
  new values. Using an AFTER trigger will not
  allow the change.

9
Example (pseudocode)

• Create Before-Trigger
• For every string inserted into sailorName, turn
  it into upper case before insertion

10
Trigger syntax
CREATE or REPLACE TRIGGER trig_name BEFORE
AFTER INSTEAD OF DELETE INSERT UPDATE
of column1, column2, or DELETE INSERT
UPDATE of columnA, columnB, on
table_name FOR EACH ROW WHEN (condition)
PL/SQL block
Trigger timing
Triggering event
Further restricts when trigger is fired
11
Backing Up Data
create table sailors( sid number, sname
VARCHAR2(30), rating number check(rating lt
10), age number )
create table sailors_backup( who
varchar2(30), when_chngd date, sid
number, old_rating number,
new_rating number )
12
sailors(sid,sname,rating,age)sailors_backup(who,
when_chngd,sid, old_rating,new_rating)
CREATE or REPLACE TRIGGER backup_trig AFTER
UPDATE of Rating on Sailors FOR EACH ROW WHEN
(old.rating lt new.rating) BEGIN INSERT INTO
sailors_backup VALUES (USER, SYSDATE,
old.sid, old.rating, new.rating) END /

• Q Why AFTER Trigger?
• A Because in that case, the firing of the
  trigger occurs only when the inserted data
  complies with the table integrity (check..)

13
Ensuring Upper Case
CREATE or REPLACE TRIGGER sname_trig BEFORE
INSERT or UPDATE of sname on Sailors FOR EACH
ROW BEGIN new.sname UPPER(new.sname) END
/

• Q Why BEFORE Trigger?
• A You cannot update inserted values after
  insertion

14
Instead Of Trigger
create view sailors_reserves as select
sailors.sname, reserves.bid from sailors,
reserves where sailors.sid reserves.sid
CREATE or REPLACE TRIGGER view_trig INSTEAD OF
INSERT on sailors_reserves FOR EACH
ROW BEGIN INSERT INTO sailors values(new.sname)
INSERT INTO reserves values(new.bid) END /
15
Statement Trigger
CREATE or REPLACE TRIGGER no_work_on_shabbat_trig
BEFORE INSERT or DELETE or UPDATE on
reserves DECLARE shabbat_exception
EXCEPTION BEGIN if (TO_CHAR(sysdate,'DY')'SAT
') then raise shabbat_exception end
if END /
16
Another example

• create or replace trigger trig2
• after update of rating on sailors
• for each row
• DECLARE
• diff numberabs((old.rating)-(new.rating))
• BEGIN
• If ((old.rating)gt(new.rating)) then
  dbms_output.put_line('The rating of
  'old.sname' has dropped by 'diff)
• elsif ((old.rating)lt(new.rating)) then
  dbms_output.put_line('The rating of
  'old.sname' has been raised by 'diff)
• else dbms_output.put_line('The rating of
  'old.sname' has remained the same')
• end if
• END
• /

17
Trigger Compilation Errors

• As with procedures and functions, when creating a
  Trigger with errors, you will get the message
• Warning Trigger created with compilation
  errors.
• To view the errors, type
• SHOW ERRORS TRIGGER myTrigger
• To drop a trigger write
• drop trigger myTrig
• To disable/enable a trigger write
• alter trigger myTrig disable/enable

18
Additional Types of Triggers

• Can also define triggers for
• logging in and off
• create/drop table events
• system errors
• etc.

19
JDBCTM"Java Database Connectivity"
20
Useful JDBC Links

• Getting Started Guide
• http//java.sun.com/j2se/1.5.0/docs/guide/jdbc/get
  start/GettingStartedTOC.fm.html
• java.sql Package API
• http//java.sun.com/j2se/1.5.0/docs/api/java/sql/p
  ackage-summary.html

21
Introduction to JDBC

• JDBC allows for convenient database access from
  Java applications
• Data is transferred from relations to objects and
  vice-versa
• databases optimized for searching/indexing
• objects optimized for engineering/flexibility

22
Why Access a Database from within a Program?

• As we saw last week, some queries cant be
  computed in SQL.
• PL/SQL includes more programming tools than SQL
• However, sometimes using PL/SQL will not be
  suitable
• If we require object-oriented programming
• If accessing the database is a small part of a
  large Java application
• If we would like to access other DBMSes than
  Oracle
• Etc.
• Why not keep all the data in Java objects?
• Separation of concerns DBMSes concentrate on
  data storage and access programs concentrate on
  algorithms, networking, etc.

23
Six Steps

• Load the driver
• Establish the Connection
• Create a Statement object
• Execute a query
• Process the result
• Close the connection

24
Packages to Import

• In order to connect to the Oracle database from
  java, import the following packages
• java.sql. (usually enough)
• javax.sql. (for advanced features, such as
  scrollable result sets)

25
JDBC Architecture (1)
Driver Manager
Application
Driver
DBMS

• DriverManager is provided by Java Software as
  part of the Java 2 Platform.
• Drivers are provided by DBMS vendors.

26
JDBC Architecture (2)

• The application creates a driver instance and
  registers it with the DriverManager.
• The DriverManager tells the driver to connect to
  the DB
• The DriverManager keeps track of registered
  driver instances and their connections to DBs.
• The Driver talks to a particular DB through the
  connection

27
Connecting

• Initializing a driver new
  oracle.jdbc.driver.OracleDriver()
• Registering it with the DriverManager
  DriverManager.registerDriver()
• Getting a connection
• DriverManager.getConnection(URL)
• Note
• Stages 12 may be combined with
  Class.forName(oracle.jdbc.driver.OracleDriver")
• In Stage 3, the Manager tries all drivers and
  assigns a connection to the first driver that
  succeeds.

28
Connecting to the Database
String path "jdbcoraclethin" String host
"sol4" String port "1521" String db
"stud" String login sqlUser" String password
passwd String url path login "/"
password "_at_" host "" port ""
db Class.forName("oracle.jdbc.driver.OracleDrive
r") Connection con DriverManager.getConnectio
n(url)
29
Statements
prepared because it already includes the query
string

• 1. Statement createStatement()
• returns a new Statement object
• 2. PreparedStatement prepareStatement(String)
• returns a new PreparedStatement object
• Both are used to send SQL commands to the DB
• Both are created via the connection object

30
Statement query methods

• executeQuery(String query) for queries that
  return a single ResultSet object (typically
  select)
• executeUpdate(String query) for INSERT, UPDATE,
  DELETE, and SQL DDL directives

31
Compilation

• When executing an SQL statement via JDBC, it is
  not checked for errors until it is run (Not
  checked during compilation)

32
executeQuery
No semi-colon()
String queryStr "SELECT FROM Sailors "
"WHERE Name 'joe smith'" Statement stmt
con.createStatement() ResultSet rs
stmt.executeQuery(queryStr)

• The executeQuery method returns a ResultSet
  object representing the query result.

33
executeUpdate
String deleteStr DELETE FROM Sailors "
"WHERE sid 15" Statement stmt
con.createStatement() int delnum
stmt.executeUpdate(deleteStr)
No semi-colon()

• executeUpdate returns the number of rows modified

34
PreparedStatement motivation

• Suppose we would like to run the query
• SELECT FROM Emp
• where namemoshe
• But we would like to run this for all employees
  (separately), not only moshe
• Could we create a variable instead of moshe
  which would get a different name every time??..

35
Prepared Statements

• Prepared Statements are used for queries that are
  executed many times with possibly different
  contents.
• A PreparedStatement object includes the query and
  is prepared for execution (precompiled).
• Question marks can be inserted as variables.
• -setString(i, value)
• -setInt(i, value)

The i-th question mark is set to the given value.
36
PreparedStatement.executeQuery()
String queryStr "SELECT FROM Sailors "
"WHERE Name ? and Rating lt
? PreparedStatement pstmt con.prepareStateme
nt(queryStr) pstmt.setString(1,
Joe) pstmt.setInt(2, 8) ResultSet rs
pstmt.executeQuery()
Value to insert
1st question mark
37
PreparedStatement.executeUpdate()
String deleteStr DELETE FROM Boats "
"WHERE Name ? and Color ?
PreparedStatement pstmt con.prepareStatement
(deleteStr) pstmt.setString(1,
Fluffy) pstmt.setString(2, "red") int delnum
pstmt.executeUpdate()
38

• Will this work?

PreparedStatement pstmt con.prepareStatemen
t(select from ?) pstmt.setString(1,
"Sailors")
No! We may put ? only instead of values
39
ResultSet (1)

• A ResultSet is an object which contains the
  result of a query - a table.
• At most one ResultSet per Statement can be open
  at the same time(!!).
• A ResultSet maintains a cursor pointing to its
  current row of data.
• The 'next' method moves the cursor to the next
  row
• As of JDBC 2.0, scrollable ResultSets are
  available, which also include previous,
  first, last, etc..

40
ResultSet (2)

• resultSet methods work on the current row.
• The cursor is positioned before the first row
  upon creation.

41
ResultSet (3)

• Statement stmt
• con.createStatement()
• ResultSet rs
• stmt.executeQuery (
• "SELECT FROM Table1")
• while (rs.next())
• //something

42
ResultSet methods

• Getting the value in some column (for the current
  row)
• getString(int columnNum)
• getString(String columnName)
• getInt(int columnNum)
• getInt(String columnName)
• Etc
• To check if NULL was returned, you have to use
  wasNull() on the ResultSet after getting the
  value.

String s rs.getString(column1")
43
Example revisited

• Statement stmt con.createStatement()
• ResultSet rs stmt.executeQuery("SELECT a, b, c
  FROM Table1")
• // retrieve and print the values for the current
  row
• while (rs.next())
• int i rs.getInt("a")
• String s rs.getString("b")
• float f rs.getFloat("c")
• System.out.println("ROW " i " " s " "
  f)

44
ResultSetMetaData
An object created by the ResultSet which holds
information about its columns
ResultSetMetaData rsmd rs.getMetaData() int
numcols rsmd.getColumnCount() for (int i 1
i lt numcols i) if (i gt 1)
System.out.print(",") System.out.print(rsmd.getC
olumnLabel(i))
45
Mapping SQL and Java Types

• SQL and Java data types are not identical
• There are significant variations between the SQL
  types supported by different database products
• JDBC defines a set of generic SQL type
  identifiers in the class java.sql.Types
• The driver is responsible for mapping between the
  DB SQL types and JDBC SQL types

46
Some JDBC SQL Types

• CHAR
• DATE
• BOOLEAN
• INTEGER
• TINYINT
• VARCHAR
• FLOAT

• DOUBLE
• BIGINT
• BINARY
• CLOB
• BLOB
• ARRAY
• OTHER

47
JDBC to Java Type Mapping

• Each getXXX() and setXXX() method is allowed to
  get/set columns of certain JDBC types
• For example getDate() is allowed to access
  columns of types CHAR, VARCHAR, LONGVARCHAR,
  DATE, and TIMESTAMP
• getDate() is the recommended method to access
  DATE columns

48
Printing Query Output Result Set (2)
while (rs.next()) for (int i 1 i lt
numcols i) if (i gt 1) System.out.print(",")
System.out.print(rs.getString(i)) System.
out.println("")

• getString() is allowed to access all simple JDBC
  types

49
Cleaning Up After Yourself

• Remember to close the Connections, Statements,
  PreparedStatements and ResultSets

con.close() stmt.close() pstmt.close() rs.close
()
50
Dealing With Exceptions
catch (SQLException e) //human readable
message about the exception System.out.println(e.
getMessage()) //String describing the reason of
the exception System.out.println(e.getSQLState())
//driver-dependent code for the
exception System.out.println(e.getErrorCode())
51
Transactions in JDBC
52
Transactions

• Transaction 2 or more statements which must all
  succeed (or all fail) together
• If one fails, the system must reverse all
  previous actions
• Aim dont leave DB in inconsistent state halfway
  through a transaction
• COMMIT complete transaction
• ROLLBACK abort

53
Example

• Suppose we want to transfer money from bank
  account 13 to account 72

PreparedStatement pstmt con.prepareStatement(U
PDATE BankAccount SET amount amount
? WHERE accountId ?) pstmt.setInt(1,-100)
pstmt.setInt(2, 13) pstmt.executeUpdate() pst
mt.setInt(1, 100) pstmt.setInt(2,
72) pstmt.executeUpdate()
What happens if this update fails?
54
Transaction Management

• The connection has a state called AutoCommit mode
• if AutoCommit is true, then every statement is
  automatically committed
• if AutoCommit is false, then every statement is
  added to an ongoing transaction
• Default true

55
AutoCommit
con.setAutoCommit(boolean val)

• If you set AutoCommit to false, you must
  explicitly commit or rollback the transaction
  using Connection.commit() and Connection.rollback(
  )

56
Example
con.setAutoCommit(false) try
PreparedStatement pstmt con.prepareStatement(u
pdate BankAccount set amount amount
? where accountId ?)
pstmt.setInt(1,-100) pstmt.setInt(2, 13)
pstmt.executeUpdate() pstmt.setInt(1, 100)
pstmt.setInt(2, 72) pstmt.executeUpdate()
con.commit() catch (SQLException e)
con.rollback()