350 likes | 497 Views
Database Programming. Sections 17– database transactions and controlling User Access. Review. DDL - defines a table and changes to the table DML - manipulates the data in a table DCL - controls who can use the table. Transactions.
E N D
Database Programming Sections 17– database transactions and controlling User Access
Review • DDL - defines a table and changes to the table • DML - manipulates the data in a table • DCL - controls who can use the table Marge Hohly
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 Marge Hohly
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 Marge Hohly
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; Marge Hohly
u Marge Hohly
d Marge Hohly
d Marge Hohly
l Marge Hohly
P Marge Hohly
i Marge Hohly
P Marge Hohly
P Marge Hohly
P Marge Hohly
P Marge Hohly
P Marge Hohly
P Marge Hohly
P Marge Hohly
P Marge Hohly
c Marge Hohly
w Marge Hohly
p Marge Hohly
M Marge Hohly
W Marge Hohly
l Marge Hohly
u Marge Hohly
p Marge Hohly
o Marge Hohly
p Marge Hohly
p Marge Hohly
l Marge Hohly
l Marge Hohly
p Marge Hohly
p Marge Hohly
p Marge Hohly