Triggers

1
Lecture 5

• Triggers
• Embedded SQL

2
Overview

• Limitations of Relational Data Model for
  performing Information Processing
• Database Triggers in SQL
• Using Database Triggers for Information
  Processing within DBMS
• Restrictions for Database Triggers
• Embedded SQL
• JDBC

3
Limitations of Relational Data Model

• Data Bases vs. Information Systems
• DBMS manages data regardless of its usage.
• IS processes information with respect to its
  usage.
• Data Model vs. System Architecture
• Data model does not give interpretation in terms
  of the application domain
• e.g. relational model, hierarchical model, set
  model.
• IS architecture is developed so that the data can
  be interpreted as information about a particular
  applied domain
• e.g. HR information, financial information, sales
  information.

4
ECA

• Event occurs in the data base
• e.g. insertion, deletion, update.
• Condition is checked
• e.g. Is batch complete? Has student passed?
• Actions are executed if condition is satisfied
• e.g. send batch to supplier, congratulate student.

5
Extending Information Processing Capabilities
of DBMS using Triggers

• Processing of database content, performed by the
  DBMS engine itself, not by the application client
• execution of the trigger (Event)
• Initiated by a specified condition, depending on
  the type of the trigger
• firing of the trigger (Condition)
• All data actions performed by the trigger execute
  within the same transaction in which the trigger
  fires, but in a separate session (Action)
• Triggers are checked for different privileges as
  necessary for the processed data.
• Cannot contain transaction control statements
  (COMMIT, SAVEPOINT, ROLLBACK not allowed).

6
Database Triggers in SQL

• Not specified in SQL-92, but standardised in SQL3
  (SQL1999)
• Available in most commercial DBMS (Oracle, IBM
  DB2, MS SQL server) and some public domain DBMS
  (Postgres)
• but not in smaller desktop (Oracle Lite) or
  public domain DBMS (MySQL).
• Some vendor DBMS permit native extensions to SQL
  for specifying the triggers
• e.g. PL/SQL in Oracle, Transact SQL in MS SQL
  Server.
• Some DBMS also allow use of general purpose
  programming languages instead of SQL
• e.g. C/C in Poet, JAVA in Oracle, C/VB in SQL
  Server.
• Some DBMS extend the triggers beyond tables
• for example also to views as in Oracle.

7
Types of SQL Triggers

• How many times should the trigger body execute
  when the triggering event takes place?
• Per statement The trigger executes once for the
  triggering event. This is the default.
• For each row The trigger executes once FOR EACH
  ROW affected by the triggering event.
• When can the trigger be fired?
• Relative to the execution of an SQL DML statement
  (BEFORE or AFTER or INSTEAD OF it)
• Exactly in a situation depending on specific
  system resources (e.g. signal from the system
  clock, expiring timer, exhausting memory).

8
Statement and Row Triggers
Example 1 Monitoring Statement Events
SQLgt INSERT INTO dept (deptno, dname, loc) 2
VALUES (50, 'EDUCATION', 'NEW YORK')
Execute only once even if multiple rows affected
Execute for each row of the table affected by the
event
9
Firing Sequence of DatabaseTriggers on a Single
Row
DEPT table
DEPTNO 10 20 30 40
DNAME ACCOUNTING RESEARCH SALES OPERATIONS
LOC NEW YORK DALLAS CHICAGO BOSTON
BEFORE row trigger
AFTER row trigger
10
Firing Sequence of Database Triggers on Multiple
Rows
EMP table
EMPNO 7839 7698 7788
ENAME KING BLAKE SMITH
DEPTNO 30 30 30
11
Syntax for creating triggers in SQL

• Trigger name - unique within the data base
  schema.
• Timing - depends on the order of controlled
  events (before or after or instead of).
• Triggering event the event which fires the
  trigger (E).
• Filtering condition - checked when the triggering
  event occurs (C).
• Target - table (or view) against which the
  trigger is fired they should both be created
  within the same schema.
• Trigger Parameters - parameters used to denote
  the record columns preceded by colon
• OLD , NEW for old and new values respectively.
• Trigger action - SQL statements, executed when
  the trigger fires surrounded by BEGIN ... END
  (A).

12
Syntax for Creating Statement Triggers

• CREATE OR REPLACE TRIGGER trigger_name
• timing event1 OR event2 OR event3
• ON table_name
• BEGIN
• SQL or PL/SQL statements
• END

The trigger body consisting of SQL statements
will be executed only once according to the
prescribed timing, when the event1 (event2,
event3) occurs against the monitored table in
question table_name.
13
Example Registering Operations

• SQLgt CREATE TRIGGER increase_salary_trg
• 2 BEFORE UPDATE OF sal
• 3 ON emp
• 4 BEGIN
• 5 INSERT INTO sal_hist(increased, changedOn)
• 6 VALUES ('YES', SYSDATE)
• 7 END
• 8 /

Trigger name increase_salary_trg Timing BEFOR
E executing the statement Triggering
event UPDATE of sal column Target emp
table Trigger action INSERT values INTO
sal_hist table
14
Syntax for Creating Row Triggers

• CREATE OR REPLACE TRIGGER trigger_name
• timing event1 OR event2 OR event3
• ON table_name
• REFERENCING OLD AS old NEW AS new
• FOR EACH ROW
• WHEN condition
• BEGIN
• SQL or PL/SQL statements
• END

The trigger body consisting of SQL statements
will be executed once for each row affected by
event1 (event2, event3) in the table named
table_name, subject to the additional condition.
15
Example Calculating Derived Columns
SQLgtCREATE OR REPLACE TRIGGER derive_commission_tr
g 2 BEFORE UPDATE OF sal ON emp 3 FOR EACH
ROW 4 WHEN (new.job LIKE 'SALES') 5 BEGIN
6 new.comm old.comm (new.sal/old.sal)
7 END 8 /
Trigger name derive_commission_trg Timing
BEFORE executing the statement Triggering
event UPDATE of sal column Filtering condition
job LIKE 'SALES' Target emp table Trigger
parameters old, new Trigger action calculate
the new commission
to be updated
Note no (colon ) before new in WHEN
16
Trigger Execution order

• 1. Execute all BEFORE STATEMENT triggers.
• 2. Disable temporarily all integrity constraints
  on the table.
• 3. Loop for each row in the table
• 3.1 Execute all BEFORE ROW triggers.
• 3.2 Execute the SQL statement against the row and
  perform integrity constraint checking of the
  data.
• 3.3 Execute all AFTER ROW triggers.
• 4. Complete deferred integrity constraint
  checking against the table.
• 5. Execute all AFTER STATEMENT triggers.

17
Controlling Triggers using SQL

• Disable or Re-enable a data base trigger
• Disable or Re-enable all triggers for a table
• Remove a trigger from the data base

ALTER TRIGGER trigger_name DISABLE ENABLE
ALTER TABLE table_name DISABLE ENABLE ALL
TRIGGERS
DROP TRIGGER trigger_name
18
Using Database Triggers for Information Processing

• Auditing Table Operations
• Each time a table is accessed, auditing
  information is recorded against it.
• Tracking Record Value Changes
• Each time a record value is changed the previous
  value is recorded.
• Protecting Database Referential Integrity if a
  foreign key points to changing records
• Referential integrity must be maintained.
• Maintenance of Semantic Integrity
• e.g. when the factory is closed, all employees
  become unemployed.
• Storing Derived Data
• e.g. the number of items in the trolley should
  correspond to the current session selection.
• Security Access Control
• e.g. checking user privileges when accessing
  sensitive information.

19
Auditing Table Operations
USER_NAME SCOTT SCOTT JONES
TABLE_NAME EMP EMP EMP
COLUMN_NAME SAL
INS 1 0
UPD 1 1 0
DEL 1 0
continuation
MAX_INS 5 5
MAX_UPD 5 5 0
MAX_DEL 5 0
20
Example Counting Statement Execution
SQLgt CREATE OR REPLACE TRIGGER audit_emp 2
AFTER DELETE ON emp 3 FOR EACH ROW 4 BEGIN
5 UPDATE audit_table SET del del 1 6
WHERE user_name USER 7 AND table_name
'EMP' 7 END 8 /
Whenever an employee record is deleted from the
database, the counter in an audit table
registering the number of deleted rows for the
current user in system variable USER is
incremented.
21
Example Tracing Record Value Changes
USER_NAME EGRAVINA NGREENBE
ID 7950 7844
OLD_LAST_NAME NULL MAGEE
NEW_LAST_NAME HUTTON TURNER
TIMESTAMP 12-SEP-04 10-AUG-04
continuation
OLD_TITLE NULL CLERK
NEW_SALARY 3500 1100
OLD_SALARY NULL 1100
NEW_TITLE ANALYST SALESMAN
22
Example Recording Changes
SQLgtCREATE OR REPLACE TRIGGER audit_emp_values
2 AFTER DELETE OR UPDATE ON emp 3 FOR EACH ROW
4 BEGIN 5 INSERT INTO audit_emp_values
(user_name, 6 timestamp, id, old_last_name,
new_last_name, 7 old_title, new_title,
old_salary, new_salary) 8 VALUES (USER,
SYSDATE, old.empno, old.ename, 9
new.ename, old.job, new.job, 10 old.sal,
new.sal) 11 END 12 /
Whenever some details for an employee are deleted
or updated, both the previous and new details are
recorded in an audit table to allow tracing the
history of changes. An insert operation cannot be
recorded with this trigger as old.empno has no
value.
23
Example Protecting Referential Integrity

• SQLgtCREATE OR REPLACE TRIGGER cascade_updates
• 2 AFTER UPDATE OF deptno ON dept
• 3 FOR EACH ROW
• 4 BEGIN
• 5 UPDATE emp
• 6 SET emp.deptno new.deptno
• 7 WHERE emp.deptno old.deptno
• 8 END
• 9 /

Whenever the department number changes, all
employee records for this department will
automatically be changed as well, so that the
employees will continue to work for the same
department.
24
Restrictions for Database Triggers

• Problem impossible to determine certain values
  during execution of a sequence of operations
  belonging to one and the same transaction.
• Mutating tables contain rows which change their
  values after certain operation and which are used
  again before the current transaction commits.
• Preventing table mutation
• Should not contain rows which are constrained by
  rows from other changing tables.
• Should not contain rows which are updated and
  read in one and the same operation.
• Should not contain rows which are updated and
  read via other operations during the same
  transaction.

25
Example Mutating Table
SQLgt CREATE OR REPLACE TRIGGER emp_count 2
AFTER DELETE ON emp 3 FOR EACH ROW 4
DECLARE 5 num INTEGER 6 BEGIN 7
SELECT COUNT() INTO num FROM emp 8
DBMS_OUTPUT.PUT_LINE(' There are now ' num
' employees.') 9 END 10 /
SQLgt DELETE FROM emp 2 WHERE deptno
30 ERROR at line 1 ORA-04091 table CGMA2.EMP
is mutating, trigger/ function may not see it
Under the bar is code entered in SQLPlus which
triggers cascade_updates in this case. Triggers
are not executed directly.
26
Example Mutating Table (fixed)
SQLgt CREATE OR REPLACE TRIGGER emp_count 2
AFTER DELETE ON emp 3 FOR EACH ROW 4
DECLARE 5 num INTEGER 6 BEGIN 7
SELECT COUNT() INTO num FROM emp 8
DBMS_OUTPUT.PUT_LINE(' There are now ' num
' employees.') 9 END 10 /
Now the trigger becomes a statement trigger and
the EMP table is no longer a mutating table.
SQLgt DELETE FROM emp WHERE deptno 30 There
are now 13 employees. There are now 8
employees. 6 rows deleted.
27
Rules for Good Practice

• Rule 1 Do not change data in the primary key,
  foreign key, or unique key columns of any table.
• Rule 2 Do not update records in the same table
  you read during the same transaction.
• Rule 3 Do not aggregate over the same table you
  are updating.
• Rule 4 Do not read data from a table which is
  updated during the same transaction.
• Rule 5 Do not use Data Control Language
  statements in triggers.

28
Additional Literature on Triggers

• P. Atzeni, S. Ceri, S.Paraboschi and R. Torlone.
  Database Systems, Chapter 12 Active Databases.
  McGraw-Hill (1999)
• Oracle Database Server Documentation. Oracle9i
  Database Concepts, Chapter 17 Triggers.
• Oracle Database Server Documentation. Oracle9i
  Application Developer's Guide Fundaments,
  Chapter 15 Using Triggers.

29
Embedded SQL JDBC

• Coupling Modes between Data Base and
• Programming Languages
• Extending the data base language with programming
  constructs (e.g. PL/SQL).
• Extending programming languages with data base
  constructs.
• Persistent programming languages, data base
  programming languages.
• Embedding a data base programming language into a
  programming language Embedded SQL.
• Data base access from the programming language
  with specialised constructs.

30
Embedded SQL

• Can embed SQL into various languages such as
  PASCAL, C, C and send command lines to data
  base
• Problems with SQL Embedding
• Type systems do not fit
• Set-oriented vs. individual (scalar) variables
• Practical Solution
• Mapping of record/attributes to data types of the
  host language
• Iterative processing of the result set by a
  cursor
• Effects on the Host Language
• Structure of the host language remains unchanged
• Every SQL statement can be embedded
• simply prefixed by EXEC SQL
• How to communicate between application program
  and data base?

31
Development of Embedded SQL application
Must have a connection established in order to run
32
Establishing a Connection
Application with embedded SQL data base
connection must be established explicitly. EXEC
SQL CONNECT username IDENTIFIED BY
passwd username and passwd are host variables
of the types CHAR or VARCHAR2 Strings are not
allowed! Not supported by the SQL
environment Equivalent EXEC SQL CONNECT
uid where uid has a value of the form
"name/passwd". NOTE JAVA has problems connecting
if the sid of the database has an underscore in
the name, e.g. info_db
33
Host Variables (calling program)

• Needed for communication between the data base
  and the application program
• Output variables for communication of values from
  the database to the application program Data
  Base Application
• Input variables for the communication of values
  from the application program to the
  database.Application Data Base
• Data types of the data base and programming
  language must be compatible.

34
Handling multiple row return

• Queries to the data base may return multiple rows
• these will be lost if not handled correctly.
• Cursors
• Analogous to PL/SQL
• Required for processing any result set that may
  contain more than one row
• Cursor operations
• DECLARE ltcnamegt CURSOR FOR ltSQLstatementgt
• OPEN ltcnamegt
• FETCH ltcnamegt INTO ltvarlistgt
• CLOSE ltcnamegt
• Error Situations
• Cursor not declared or not opened.
• No (further) data has been found (incorrect EXIT
  clause).
• Cursor has been closed, and not reopened.

35
Example
int main() EXEC SQL BEGIN DECLARE SECTION char
cityName25 / output host
var / int cityEinw /
output host var / char landID "D"
/ input host var / short ind1, ind2
/ indicator var / char uid
"/" EXEC SQL END DECLARE SECTION EXEC SQL
CONNECT uid
/connect to DB / EXEC SQL DECLARE
StadtCursor CURSOR FOR SELECT Name,
Einwohner FROM Stadt WHERE Code landID EXEC
SQL OPEN StadtCursor / open
cursor / printf("Stadt Einwohner\n") while
(1) EXEC SQL FETCH StadtCursor INTO
cityNameind1 , cityEinw INDICATOR
ind2 if(ind1 ! -1 ind2 ! -1) printf("s
d \n", cityName, cityEinw) EXEC SQL CLOSE
StadtCursor /close
cursor /
All SQL commands are preceded by EXEC
Dont panic! You are not expected to
understand the code Illustration only!
36
PL/SQL - blocking of code

• Oracle ProC/C precompiler supports PL/SQL
  blocks
• can be used in place of an SQL statement
• reduce communication overhead between client and
  server.
• Frame for communication EXEC SQL
  EXECUTE DECLARE ... BEGIN ... END END-EXEC
  
• This reduces the need for EXEC commands at the
  start of each line.

37
Transactions

• Application programming is regarded as a closed
  transaction, if it is not divided by COMMIT (or
  ROLLBACK) commands (it happens in isolation and
  as one block of code).
• In Oracle, after leaving a program, COMMIT is
  executed automatically.
• COMMIT is executed automatically before each DDL
  statement.
• The data base connection is shut down by either
• EXEC SQL COMMIT RELEASE or
• EXEC SQL ROLLBACK RELEASE
• Transaction handling is covered later in the
  module.

38
Additional .

• Savepoints
• Transactions can be divided by savepoints.
• Syntax EXEC SQL SAVEPOINT ltnamegt
• ROLLBACK to an earlier savepoint deletes all
  savepoints in between.
• Exception Handling Mechanism
• When you embed code you must handle errors
• otherwise the program could crash or hang up.
• The WHENEVER statement is the main one used.

39
WHENEVER statement

• Specifies actions to be executed automatically by
  the DBMS in case of an error
• EXEC SQL WHENEVER ltconditiongt ltactiongt
• ltconditiongt may be
• SQLWARNING the most recent statement caused a
  warning different from no data found. This
  corresponds to sqlcode gt 0, but ltgt 1403.
• SQLERROR the most recent statement caused a
  serious error. This corresponds to sqlcode lt 0.
• NOT FOUND SELECT INTO or FETCH did not return
  any more answer rows. This corresponds to
  sqlcode 1403.

40
WHENEVER statement (cont.)

• ltactiongt tells the DBMS what to do should an
  error be encountered during execution, e.g.
• CONTINUE the program continues with the
  subsequent statement.
• DO ltproc_namegt invoke a procedure (error
  handling)DO BREAK for exiting a loop.
• GOTO ltlabelgt jump to the given label.
• STOP rollback and exit from the program.

41
JAVA and Data Bases

• JAVA is platform-independent
• If a Java Virtual Machine is available, Java
  programs can be executed.
• APIs (Application Programming Interfaces) are
  collections of classes and interfaces that
  provide a certain functionality
• avoids recoding of common code.
• JDBC (Java DataBase Connectivity) is an API for
  data base access
• Interface for (remote) access to a database from
  Java programs.
• Applications can be programmed independently of
  the underlying DBMS
• increased flexibility.
• Translates the ODBC idea to Java established
  protocol
• Common base is the X/Open SQL CLI (Call Level
  Interface) Standard
• conforming to standards improves reliability.

42
JDBC Architecture
43
JDBC API

• Flexible
• Application can be programmed independently from
  the underlying DBMS.
• De facto portability only in the SQL-2 standard
  (not stored procedures, object-relational
  features).
• Low-level
• Statements are submitted as strings.
• In contrast to Embedded SQL, program variables
  are not allowed in SQL commands.
• Under development
• Embedded SQL for JAVA
• Direct mapping of tables and rows to JAVA
  classes.

44
JDBC functionality

• Establishing a connection to the data
  base(DriverManager, Connection).
• Submission of SQL statements to the database
  (statement and subclasses)
• Processing of the result set(ResultSet)

45
JDBC establishing a connection

• Invocation of the DriverManager
• Connection ltnamegt
• DriverManager.getConnection
• (ltjdbc-urlgt, ltuser-idgt, ltpasswdgt)
• Data base is uniquely identified by the JDBC-URL
• jdbcltsubprotocolgtltsubnamegt
• ltsubprotocolgt identifies the driver and access
  mechanism
• ltsubnamegt identifies the data base

46

• jdbcoracleltdriver-namegt
• _at_ltIP-Address DB ServergtltPortgtltSIDgt
• e.g.
• String url
• 'jdbcoraclethin_at_132.230.150.111521o901'
• Connection conn
• DriverManager.getConnection(url,'jdbc_1','jdbc_1')
  
• Returns an opened connection instance, conn.
• To close the connection conn.close()

47
Submitting SQL statements

• Statement objects created by invocation of
  methods of an existing connection
• Statement simple SQL statement without
  parameters.
• PreparedStatement precompiled query, query with
  parameters.
• CallableStatement invocation of a stored PL/SQL
  procedure.

48
JDBC Data Types

• JDBC stands in between Java (object types) and
  SQL (predefined types).
• java.sql.types defines generic SQL types which
  are used by JDBC
• If the data types are not supported by SQL-2 then
  the program cannot use the returned value.

49
Handling result sets (multiple row return)

• Information about columns of the result set
• ResultSetMetaData ltnamegt
  ltresult-setgt.getMetaData()
• creates a ResultSetMetaData object that contains
  information about the result set

50
Prepared Statements

• PreparedStatement ltnamegt ltconnectiongt.prepareSta
  tement(ltstringgt)
• SQL statement ltstringgt is precompiled.
• thus, the statement is contained in the state of
  the object
• more efficient than (simple) Statement if it has
  to be executed several times.
• Depending on ltstringgt, one of the
  (parameterless!) methods
• ltprepared-statementgt.executeQuery()
• ltprepared-statementgt.executeUpdate()
• ltprepared-statementgt.execute()
• is applicable.

51
Prepared statements parameters

• Input parameters are represented by ?
• PreparedStatement pstmt
• conn.prepareStatement
• ("SELECT Population
• FROM Country
• WHERE Code ?")
• ?-parameters are assigned to values by
• ltprepared-statementgt.setlttypegt(ltposgt,ltvaluegt)
• before a PreparedStatement is submitted.
• lttypegt Java data type,
• ltposgt position of the parameter to be set,
• ltvaluegt value.

pstmt .setString (1, "D" ) ResultSet rset
pstmt .ExecuteQuery () ... pstmt .setString (1,
"CH" ) ResultSet rset pstmt .ExecuteQuery ()
52
Further SQL/Oracle tools

• Dynamic SQL
• SQL statements are generated in in PL/SQL at
  runtime as strings, and are then submitted to the
  data base.
• ORACLE 8i
• built-in Java Virtual Machine, access to the file
  system
• i internet XML-interface, Web-Application-Serve
  r etc.
• ORACLE-Web Server/Internet Application Server
  (9i)
• HTML pages can be generated depending on the data
  base contents.
• In the most recent packages and extensions (IAS,
  Internet File System Server) the difference
  between the database and the operating system
  diminishes.