Yea Yeah Yeah

Topics: Database transaction, Database, SQL Pages: 3 (386 words) Published: January 10, 2013
Additional SQL Concepts

Database Transactions
o logical unit of work in a database system
o consists of one or more database operations (insert, delete, update) which together reflect the data changes resulting from a “real-world transaction”
o properties of transactions
atomicity - the execution of the component operations of a transaction is an all-or-nothing affair; i.e., either all the component operations are executed, or none at all are consistency - a (correct) transaction transforms the database from one consistent state to another consistent state, without necessarily preserving consistency at all intermediate points isolation

durability - the effects of the updates of a committed transaction persists, even in the face of subsequent failures
transaction processing system (concurrency and recovery mechanisms) autocommit mode and isolation levels
SQL (transaction initiation, COMMIT, ROLLBACK, intermediate savepoints) Triggers
o database object which defines an action the database should take some related database event occurs o fired (automatically executed) when some DML statement is issued (either before or after the event) CREATE TRIGGER newinvdetail

UPDATE invoice
SET invamt = invamt + (:new.invqty * :new.unitprice)
WHERE invno = :new.invno;
SET qtyonhand = qtyonhand - :new.invqty
WHERE itemno = :new.itemno;
Stored Procedures and Functions
o database program modules that are stored and executed by the DBMS (persistent stored modules) o usefulness
stored module can be used by several applications
server processing can reduce data transfer and communication cost allows more complex types of derived data to be made available to users o written in a general purpose programming language (e.g. PL/SQL in Oracle, Transact-SQL in MS SQL) o typically provides for explicit usage of IN, OUT, INOUT parameters CREATE PROCEDURE procname (parameters) AS......
Continue Reading

Please join StudyMode to read the full document

You May Also Find These Documents Helpful

  • yeah nah Essay
  • Yeah Essay
  • Yeah Essay
  • Yeah Essay
  • Yeah Essay
  • Yeah Essay
  • YEAH Essay
  • Yeah Essay

Become a StudyMode Member

Sign Up - It's Free