Chapter 7 Database

Triggers cannot be fired manually.

manual

- includes variables (prefaced by @)

- can be used to hold temporary or interim values - value returned from a function call, - counter in a loop

- System-stored Procedures

- created by SQL Server for admin and mgmt

Trigger:

- Often implemented as a prebuilt SQL procedure, - a trigger is automatically invoked to perform some updating maintenance job - SQL statements that cause triggers to fire: - INSERT - UPDATE - DELETE - when fired before one of the above operations: INSTEAD OF trigger - INSTEAD OF INSERT - INSTEAD OF UPDATE - (not available for tables with ON UPDATE cascading referential integrity constraints) - INSTEAD OF DELETE - (not available for tables with ON DELETE cascading referential integrity constraints) - can be used to update referential integrity constraints - can be used to provide warning messages - can be used to enforce complex constraints that may be too difficult to be enforced at the time of database design

Using Triggers:

- Validating Business Constraints Ex: No student should have the same training by more than one mentor - Implementing Dynamic Application Logic Ex: If a student is enrolled in two clubs concurrently, he or she will get a $10 discount on the membership fee for the second club. - Keeping Referential Integrities Ex: a trigger is used to prevent the delete operation from deleting the last duty in each duty category. After the deletion, the trigger checks if this type of duty still exists in the table. If not, it will display a message and roll back the transaction.

Stored Procedure:

- a prebuilt procedure or group of procedures - invoked by calling its name and passing a set of parameters into and out of it - Using one can improve performance because there is no need to compile a procedure before using it - Often, a pre-built procedure is optimized for best performance - Can improve database programmers' productivity

- Remote-stored Procedures

- can be executed on a remote SQL Server database

SQL Server 2000 Programming extensions

- includes variables (prefaced by @) - includes flow-control structures - Built-in Functions - User-defined Functions - System-stored Procedures - Local-stored Procedures - Temporary-stored Procedures - Extended-stored Procedures - Remote-stored Procedures

- Built-in Functions

- rowset functions - aggregate (like count, max, min, sum, avg) - scalar (returns a single value, like round)

- User-defined Functions

- scalar (return a single value) - table (or table-values) ... return a table

- Local-stored Procedures

- stored on local disks - created/used to conduct user-defined tasks

- Temporary-stored Procedures

- temporary local procedures - deleted when database server is shut down or when the database connection is terminated

- Extended-stored Procedures

- written in other programming languages (Ex: C) - compiled as Dynamic Link Library (DLL) files - can be dynamically loaded and executed from within the SQL Server

SQL Procedure:

A list of multiple SQL statements contained in a program.

- includes flow-control structures

BEGIN ... END BREAK CONTINUE GOTO label IF ... ELSE RETURN WAITFOR WHILE