Database Programming

1
Database Programming

• Sections 17 database transactions and
  controlling User Access

2
Review

• DDL - defines a table and changes to the table
• DML - manipulates the data in a table
• DCL - controls who can use the table

3
Transactions

• Allow users to make changes to data then decide
  whether to save or not the work.
• Database transactions bundle multiple steps into
  a logical unit.
• Transactions consist of one of the following
• DML statements which constitute one consistent
  change to the data. The DML processes include
  INSERT, UPDATE, DELETE and MERGE
• one DDL statement such as CREATE, ALTER, DROP,
  RENAME or TRUNCATE
• one DCL statement such as GRANT or REVOKE

4
Transactions

• COMMIT
• When user wants to save group of changes and make
  pending changes permanent
• ROLLBACK
• Discards changes made to database, all pending
  changes are discarded.
• SAVEPOINT
• Creates a marker in a transaction, which divides
  the transaction into smaller pieces.
• ROLLBACK TO SAVEPOINT
• Allows user to roll back the current transaction
  to a specified savepoint. Discards only changes
  after the SAVEPOINT

5
Example

• UPDATE d_cds
• SET cd_number 96
• WHERE title 'Graduation Songbook'
• SAVEPOINT one
• INSERT INTO d_cds(cd_number, title, producer,
  year)
• VALUES(100, 'Go For It', 'The Music Man', 2004)
  )
• UPDATE d_cds
• SET cd_number 101
• ROLLBACK TO SAVEPOINT one
• COMMIT

6
u
7
d
8
d
9
l
10
P
11
i

12
P
13
P
14
P
15
P
16
P
17
P
18
P
19
P
20
c
21
w
22
p
23
M
24
W
25
l
26
u
27
p
28
o
29
p
30
p
31
l
32
l
33
p
34
p
35
p