Page 86 - DCAP508_DATABASE_ADMINISTRATION

Database Administration

Notes
Example: Let us consider that in your database there is a table into which you record data
regarding your employees salaries, and that you are nor required to provide your employees
increments of more than twenty percent at a time. You can handle this action with a trigger.
Figure below show you how to create a trigger by means of the Enterprise Manager console
(you can run the same code from the Query Analyzer):

If you are updating the record from the Query Analyzer window the suitable message will be
printed; otherwise, you can select to raise an error to a calling application or just mutely log this
error into a table. This Transact-SQL code uses the thought of transaction. It is important to
recognize now that the operation (INSERT in my sample) and the trigger include a single block
that either succeeds or fails as a whole; ROLL-BACK TRANSACTION rolls back the whole
transaction, nullifying the update the data in the Employees table will remain as it was before
the update. SQL Server also introduces two virtual tables, INSERTED and DELETED, for use with
triggers. When data is about to be customized by insertion, updating, or deletion there is no
assurance that the operation will be accomplished: Some business logic executed as constraints
or triggers may avert this. SQL Server creates INSERTED and DELETED automatically whenever
a particular action appears. The DELETED table comprises rows as they were prior to the
alteration, and the INSERTED table includes modified rows, as does the base table itself. The
number of rows in each table is comparable exactly the number of rows affected by the T-SQL
statement.

5.11 Summary

Designing a database requires an understanding of the business functions you want to
model. It also requires an understanding of the database concepts and features that you
want to use to represent those business functions.

80 LOVELY PROFESSIONAL UNIVERSITY