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 Marge Hohly

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 Marge Hohly

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 Marge Hohly

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; Marge Hohly

6. u Marge Hohly

7. d Marge Hohly

8. d Marge Hohly

9. l Marge Hohly

10. P Marge Hohly

11. i Marge Hohly

12. P Marge Hohly

13. P Marge Hohly

14. P Marge Hohly

15. P Marge Hohly

16. P Marge Hohly

17. P Marge Hohly

18. P Marge Hohly

19. P Marge Hohly

20. c Marge Hohly

21. w Marge Hohly

22. p Marge Hohly

23. M Marge Hohly

24. W Marge Hohly

25. l Marge Hohly

26. u Marge Hohly

27. p Marge Hohly

28. o Marge Hohly

29. p Marge Hohly

30. p Marge Hohly

31. l Marge Hohly

32. l Marge Hohly

33. p Marge Hohly

34. p Marge Hohly

35. p Marge Hohly