Database Reference
In-Depth Information
BY queries, and layer computations and built-in functions
so that computations can be used in WHERE clauses. Some
organizations use views to provide table aliases. Views also
can be used to assign different sets of processing permissions
to tables and to assign different sets of triggers as well. The
rules for determining whether a view can be updated are
both complicated and DBMS specific. Guidelines are shown
in Figure 7-23.
SQL statements can be embedded in program code in
functions, triggers, stored procedures, and application code.
To do so, there must be a way to associate SQL table col-
umns with program variables. Also, there is a paradigm mis-
match between SQL and programs. Most SQL statements
return sets of rows; an application expects to work on one
row at a time. To resolve this mismatch, the results of SQL
statements are processed as pseudofiles using a cursor. Web
database applications are a good example of SQL statements
embedding in application program code.
SQL/PSM is the portion of the SQL standard that pro-
vides for storing reusable modules of program code within
a database. SQL/PSM specifies that SQL statements will be
embedded in functions, triggers, and stored procedures in a
database. It also specifies SQL variables, cursors, control-of-
flow statements, and output procedures.
A trigger is a stored program that is executed by the
DBMS whenever a specified event occurs on a specified table
or view. In Oracle, triggers can be written in Java or in a
proprietary Oracle language called PL/SQL. In SQL Server,
triggers can be written in a propriety SQL Server language
called TRANSACT-SQL, or T-SQL, and in Microsoft CLR lan-
guages, such as Visual Basic.NET, C# .NET, and C++ .NET. With
MySQL, triggers can be written in MySQL's variant of SQL.
Possible triggers are BEFORE, INSTEAD OF, and AFTER.
Each type of trigger can be declared for insert, update, and
delete actions, so nine types of triggers are possible. Oracle
supports all nine trigger types, SQL Server supports only
INSTEAD OF and AFTER triggers, and MySQL supports
the BEFORE and AFTER triggers. When a trigger is fired,
the DBMS supplies old and new values for the update. New
values are provided for inserts and updates, and old values
are provided for updates and deletions. How these values are
provided to the trigger depends on the DBMS in use.
Triggers have many uses. This chapter discussed four:
setting default values, enforcing interrelation data con-
straints, updating views, and enforcing referential integrity
actions.
A stored procedure is a program that is stored within
the database and compiled when used. Stored procedures
can receive input parameters and return results. Unlike
triggers, their scope is database-wide; they can be used
by any process that has permission to run the stored
procedure.
Stored procedures can be called from programs writ-
ten in the same languages used for triggers. They also can
be called from DBMS SQL utilities. The advantages of using
stored procedures are summarized in Figure 7-31.
casual relationship
CHECK constraint
cursor
data control language (DCL)
data definition language (DDL)
data manipulation language (DML)
data model
database design
DEFAULT keyword
FOREIGN KEY constraint
IDENTITY({StartValue}, {Increment}) property
implementation
index
interrelation constraint
intrarelation constraint
Microsoft SQL Server 2012 Management Studio
NOT NULL constraint
NULL constraint
Oracle MySQL Workbench
Oracle SQL Developer
PRIMARY KEY constraint
Procedural Language/SQL (PL/SQL)
procedural programming language
pseudofile
SQL ADD clause
SQL ADD CONSTRAINT clause
SQL ALTER INDEX statement
SQL ALTER TABLE statement
SQL ALTER VIEW statement
SQL CREATE INDEX statement
SQL CREATE TABLE statement
SQL CREATE VIEW statement
SQL CREATE OR REPLACE VIEW syntax
SQL DELETE statement
SQL DROP COLUMN clause
SQL DROP CONSTRAINT clause
SQL DROP INDEX statement
SQL DROP TABLE statement
SQL INSERT statement
SQL MERGE statement
Search WWH ::
Custom Search