SalesPrice is null) THEN
v_TransactionFound:= TRUE;
UPDATE Transaction SET
SalesPrice = v_Price,
CustomerID = v_CustomerID,
PurchaseDate = SYSDATE
WHERE CURRENT OF TransactionCursor;
END IF;
158 DATABASE [CHAP. 8
EXIT WHEN v_TransactionFound;
END LOOP;
IF( v_TransactionFound = FALSE ) THEN
v_Return:= 'No valid Transaction record exists.';
ROLLBACK;
RETURN;
END IF;
COMMIT;
v_Return:= 'success';
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_Return:= 'Exception: No data found';
ROLLBACK;
WHEN TOO_MANY_ROWS THEN
v_Return:= 'Exception: Too many rows found';
ROLLBACK;
WHEN OTHERS THEN
v_Return:= ( 'Exception: ' || SQLERRM );
ROLLBACK;
END;
You probably recognize some SQL statements in this procedure, and you also see statements that are
nothing like the SQL discussed in this chapter. PL/SQL is a much more complex language than SQL. Other
vendors have their own equivalent procedural language extensions to SQL, too. In the case of Microsoft, for
example, the language is called Transact-SQL. We will show an example of Transact-SQL in the next section
about triggers.
TRIGGERS
A trigger is a special type of stored procedure that gets executed when some data condition changes in
the database. Triggers are used to enforce rules in the database.
Pages:
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436