SQL Functions and Triggers: A Comprehensive Guide, Slides of Introduction to Database Management Systems

Download SQL Functions and Triggers: A Comprehensive Guide and more Slides Introduction to Database Management Systems in PDF only on Docsity!

Database Management Systems

1

Objectives

• Why would you need to use procedural code when SQL

is so powerful?

• How do you use data triggers to make changes

automatically?

• How does the DBMS ensure related changes are made

together?

• How do you handle multiple users changing the same

data at the same time?

• How are internal key values generated and used in

updates?

• What is the purpose of database cursors?

2

User-Defined Function

4

CREATE FUNCTION EstimateCosts

(ListPrice Currency, ItemCategory VarChar)

RETURNS Currency

BEGIN

IF (ItemCategory = ‘Clothing’) THEN

RETURN ListPrice * 0.

ELSE

RETURN ListPrice * 0.

END IF

END

Function to Perform Conditional

Update

5

CREATE FUNCTION IncreaseSalary

(EmpID INTEGER, Amt CURRENCY)

RETURNS CURRENCY

BEGIN

IF (Amt > 50000) THEN

RETURN -1 -- error flag

END

UPDATE Employee SET Salary = Salary + Amt

WHERE EmployeeID = EmpID;

RETURN Amt

END

Data Trigger Events

• Oracle additions:
• Tables ALTER, CREATE, DROP
• User LOGOFF, LOGON
• Database SERVERERROR, SHUTDOWN, STARTUP

7

INSERT

DELETE

UPDATE

BEFORE AFTER

Statement v. Row Triggers

8

UPDATE Employee

SET Salary = Salary + 10000

WHERE EmployeeID=

OR EmployeeID=

SQL

time

Before Update On table

After Update On table

Before Update Row 442

After Update Row 442

Update Row 442

… other rows

Triggers for overall table

Triggers for each row

Canceling Data Changes in Triggers

10

CREATE TRIGGER TestDeletePresident

BEFORE DELETE ON Employee

REFERENCING OLD ROW AS oldrow

FOR EACH ROW

WHEN (oldrow.Title = ‘President’)

SIGNAL _CANNOT_DELETE_PRES;

Cascading Triggers

11

Sale(SaleID, SaleDate, …)

OrderItem(OrderID, ItemID, Quantity, …)

Order(OrderID, OrderDate, …)

Inventory(ItemID, QOH, …)

SaleItem(SaleID, ItemID, Quantity, …)

AFTER INSERT UPDATE Inventory SET QOH = QOH – newrow.Quantity

AFTER UPDATE

WHEN newrow.QOH < newrow.Reorder INSERT {new order} INSERT {new OrderItem}

• Some transactions result in multipleTransactions

changes.

• These changes must all be completed successfully, or the group must fail.
• Protection for hardware and communication failures.
• example: bank customer transfers money from savings account to checking account. - Decrease savings balance - Increase checking balance - Problem if one transaction and machine crashes.
• Possibly: give users a chance to reverse/undo a transaction.
• Performance gain by executing transactions as a block.

13

Savings Accounts

Inez: 5340.

Checking Accounts

Inez: 1424.

Transaction

1. Subtract $1000 from savings. (machine crashes)
2. Add $1000 to Checking. (money disappears)

Transaction Steps

Steps Savings Balance Checking Balance

1. Start 5,340.92 1,424.
2. Subtract 1,000 4,340.92 1,424.
3. Add 1,000 4,340.92 2,424.

Problem arises if transaction is not completed

1. Subtract 1,000 4,340.92 1,424.
2. Machine crashes 1,000 is gone

14

SQL Transaction Code

16

CREATE FUNCTION TransferMoney(Amount Currency, AccountFrom Number, AccountTo Number) RETURNS NUMBER curBalance Currency; BEGIN DECLARE HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; Return -2; -- flag for completion error END; START TRANSACTION; -- optional SELECT CurrentBalance INTO curBalance FROM Accounts WHERE (AccountID = AccountFrom); IF (curBalance < Amount) THEN RETURN -1; -- flag for insufficient funds END IF UPDATE Accounts SET CurrentBalance = CurrentBalance – Amount WHERE AccountID = AccountFrom; UPDATE Accounts SET CurrentBalance = CurrentBalance + Amount WHERE AccountID = AccountTo; COMMIT; RETURN 0; -- flag for success END; Docsity.com

SAVEPOINT

17

START TRANSACTION;

SELECT …

UPDATE …

SAVEPOINT StartOptional; UPDATE … UPDATE … If error THEN ROLLBACK TO SAVEPOINT StartOptional; END IF COMMIT;

time

start Required elements

SAVEPOINT

StartOptional Risky steps

commit

Partial rollback

Concurrent Access Steps

Receive Payment Balance Place New Order

1. Read balance 800
2. Subtract Pmt. -
3. Save balance 600

3. Read balance 800
4. Add order 150
5. Write balance 950

19

Pessimistic Locks: Serialization

• One answer to concurrent access is to prevent it.
• When a transaction needs to alter data, it places a SERIALIZABLE lock

on the data used, so no other transactions can even read the data

until the first transaction is completed.

20

ID Balance Jones $ $

Customers

1. Read balance 800

2. Subtract pmt -

3. Save new bal. 600

4. Read balance Receive error message that it is locked.

Receive Payment Place New Order

SET TRANSACTION SERIALIZABLE, READ WRITE