Yea Yeah Yeah

Only available on StudyMode
  • Topic: Database transaction, Database, SQL
  • Pages : 3 (386 words )
  • Download(s) : 54
  • Published : January 10, 2013
Open Document
Text Preview
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
o DBMS
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

AFTER INSERT ON invdetail FOR EACH ROW
BEGIN
UPDATE invoice
SET invamt = invamt + (:new.invqty * :new.unitprice)
WHERE invno = :new.invno;
UPDATE item
SET qtyonhand = qtyonhand - :new.invqty
WHERE itemno = :new.itemno;
END;
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......
tracking img