NetNews Usenet Archive 1993 #3/NN_1993_3.iso/spool/comp/database/informix/3019

Internet Message Format | 1993-01-21 | 13.8 KB

Path: sparky!uunet!olivea!gossip.pyramid.com!pyramid!infmx!cheetah!billd
From: [e-mail] (William Daul)
Newsgroups: comp.databases.informix
Subject: Re: Informix triggers
Message-ID: <billd.727603393@cheetah>
Date: 21 Jan 93 08:03:13 GMT
References: <[e-mail]> <[e-mail]>
Sender: [e-mail] (Usenet News)
Organization: Informix Software, Inc.
Lines: 333


                   TechInfo # 4035

Short Description:
Informix Triggers

Long Description:
From: Informix Times magazine
=============================


Informix Triggers a New Release

Informix now offers version 5.01 of its database servers, INFORMIX-OnLine and
INFORMIX-SE, providing an additional area of functionality-triggers.

Triggers are database mechanisms that automatically invoke or "trigger" a
specified set of SQL statements or stored procedures whenever a particular event
occurs within a database table.

 Over the past year or so, "triggers" has become a catch phrase in the database
community, specifically as developers working with systems from other RDBMS
vendors have relied on triggers primarily to enforce referential integrity
rules. Since the 5.0 release of its database server products in early 1992,
Informix has provided for referential integrity through ANSI-compliant
declarative integrity constraints, enabling developers to take referential
integrity for granted and focus on the other aspects of their database design.

With triggers, database developers can automatically and universally enforce
business rules or conditions every time a table is modified. When used in this
way, triggers are an important tool in the continuing struggle against developer
backlogs, providing multiple advantages. By enforcing business rules across
applications automatically and without additional coding, triggers help
companies turn out highly consistent and functional applications faster to meet
their business objectives. With triggers in their arsenals, developers can
eradicate incongruities in their databases because the trigger will be
consistent for any given table across all applications. Triggers also provide
flexibility-if later on a developer wishes to add other conditions to a table,
it need only be done once and it will be enforced from then on every time a
table is modified.

Stored Procedures

Triggers are associated with a table and a triggering event. Inserts, deletes
and updates are triggering events. When a triggering event occurs, the triggered
action can be a series of SQL statements or a stored procedure. A stored
procedure is an application procedure stored in the database. Stored procedures
can include SQL statements and program statements that are used to define
variables, assign and compare values, and control the flow of execution within
the stored procedure. Stored procedures have unique names and in addition to
being invoked by a trigger, they may also be executed by an application program
with a call to the database server.

The trigger or application program may pass parameters to the stored procedure
and the stored procedure does its work and may return values to the program.
Generally people end up creating entire libraries filled with stored procedures
common to more than one application program.

Understanding the value of stored procedures, Informix has provided the
capability to utilize them since the 5.0 database server release. Any new
triggers integrated into existing applications can be used to invoke any new or






existing stored procedures. Stored procedures are valuable in several ways.

1) Consistent Application Logic

First, since stored procedures reside in the database and not in the
applications, they are only written once and they may be triggered or called by
any application program. The stored procedure written for one application will
be consistent across all applications eliminating the possibility of error that
recoding for every application would introduce while saving programmer time.

2) Performance

Another valuable aspect of a stored procedure is that the SQL statements within
it will be parsed and optimized when the stored procedure is created, making
these procedures more efficient than if this had to take place at run time. Upon
execution of the stored procedure, the database server only needs to make sure
that the user passes the security checks and that all the database objects that
are referenced by the stored procedure currently exist in the database.

The requests from stored procedures are executed within the database server
process and not the application process. So the SQL statements in the stored
procedure do not generate messages between the application and the database
server. If the logic of the stored procedure were to reside in the application
program, each SQL request would generate messages between the application and
the server, creating additional message traffic. So stored procedures are
especially beneficial in a networked environment because they reduce the number
of messages that must travel across the network.

3) Security

Stored procedures offer another means of implementing flexible security measures
by allowing you to define limited access to your database. Stored procedures are
subject to the same security checks as any other parts of the database such as
tables or views. So granting a user access to a stored procedure limits that
user to only those operations performed in the stored procedure. It does not
grant the user the ability to access the tables or views from outside of the
stored procedure. Nor do you need to grant a user of a stored procedure the
ability to read or modify the database. Stored procedures allow you to raise the
security from the data level to the procedure level, eliminating the possibility
of a user writing their own routines to access the database.

Triggering Events

A trigger can invoke an SQL statement or a stored procedure whenever certain
triggering events occur against the database. These events include: an insert, a
delete or an update. Informix allows one insert, one delete, and multiple update
triggers for each table in the customer's 5.0 database. Each trigger is
completely user-defined and can be attached to any table. So rather than the
application calling the stored procedure, when any user attempts a modification
for example, the previously-defined trigger will invoke the stored procedure. It
may help to visualize this functionality by thinking of a trigger as an actual
attachment to a table. Anytime someone wants to modify a table which has a
trigger defined for that type of modification, it "triggers" the stored
procedure.

Oftentimes the procedure the trigger initiates is designed to ensure that a
logical relationship within the database has not been violated by the
modification. Since the procedure is automatically initiated rather than called
by the application program, no user can bypass the policy.







Integrity Constraints

So why hasn't INFORMIX-OnLine had triggers before now? The notable use of
triggers in the industry has been to achieve referential integrity-some database
vendors provide no other means to that end. Informix chose a different approach.
Instead of utilizing triggers to implement integrity constraints, Informix opted
instead to fully implement the ANSI SQL89 standard for integrity constraints. By
strictly following the guidelines of ANSI SQL89's integrity enhancement
specification, Informix was able to fully implement integrity constraints
without the use of triggers. In fact, although one could use triggers within
OnLine 5.01 to implement referential integrity constraints, it would be a wasted
trigger. It is far more efficient and the process is much less susceptible to
error to create referential integrity constraints via a schema declaration when
the database is designed as opposed to creating referential integrity later on
using triggers.

The two most common types of integrity constraints in a database are entity
integrity and referential integrity. Entity integrity encompasses default and
permissible values as well as uniqueness. In other words, it enforces
restrictions on what values may exist in a specific data column.

Referential integrity enforces a master/detail (sometimes called a parent-child)
relationship between records in a table. Referential integrity will not allow
detail (or child) records to exist without a corresponding master (or parent)
record. So any attempt to delete a master record would fail, due to referential
integrity, if detail records existed for the same order.

Other RDBMSs use triggers or rules (trigger-like mechanisms) to enforce
integrity. Not only does this violate the ANSI standard, but it unnecessarily
invokes a stored procedure adding to system overhead. The database server
itself, when designed using ANSI SQL89, can handle integrity constraints in a
more straightforward, less taxing way. INFORMIX-OnLine does not need the
developer to properly code a stored procedure to enforce integrity constraints.
Informix enforces integrity automatically via the specification of integrity
constraints for the database server when the database was defined.

Why Triggers Now

If Informix implements integrity constraints using declarative integrity,
meaning it is defined as a part of the table, why bother implementing triggers
now? There are many other useful operations that can be accomplished using
triggers including creating audit trails, implementing business rules, or
automatically imputing data.

Audit Trails

If you would like to track activity within a database, triggers can be extremely
helpful. You could easily set up a trigger which not only logs all updates to a
table, but also stores the user's name and the time of the update. Database
activity which requires a log of that activity can be implemented using triggers
(see example, Creating an Audit Trail).

Business Rules

What constitutes a business rule? Perhaps an organization enforces limits and
conditions upon what can and cannot be updated in a database (see example,
Enforcing Business Rules). Certainly accounting, governmental, corporate, or
departmental regulations could all fall into this category and be implemented
with triggers. For example, in an accounting environment a business rule may
exist that restricts a customer from placing an order if any invoices to that






customer are over 90 days past due. In this case, you could set up a trigger
through which any attempt to write to the order table would first check a
dunning table.

Another example of a business rule: a former employee will not be able to file a
COBRA claim beyond 90 days after termination unless a COBRA extension has been
filed. In this case a trigger could be set up to search a table containing COBRA
extensions filed if the 90-day limit is superseded.

Automatically Writing Back to the Database

You may use a trigger to perform calculations, and, if you want, write that new
data back into the database, creating new values as you go. In other words, data
which is not immediately available from the triggering statement may be derived
by the stored procedure that is triggered (see example, Writing Back to the
Database with Triggers). For example, if you were recording occurrences of
seismic events by region and wanted to automatically summarize a number of
regions into zones, you could create a trigger which adds several regions and
records the result in a column called zone.

Cascading Triggers

Triggers can also cascade-the action of one trigger can set off another one.
Cascades may be set up to trigger up to a maximum of 61 triggers in a series,
including the initial trigger. Trigger one could set off triggers two through
sixty. A system catalog table called "systriggers" is available, allowing you to
query to find out what triggers exist for a particular table.

Tracing Triggers

If you find that the triggered action is not behaving as expected, you can
monitor its execution by placing it inside a stored procedure and using the
trace capabilities of Stored Procedure Language (SPL). You can trace the
procedure statements and variables. The trace output reveals the values of
procedure variables, procedure arguments, return values, and error codes helping
you to decided why your results were not what you expected.

Triggers' Greatest Value

In summary, a trigger calls an SQL statement or a stored procedure, a
pre-defined activity that you have stored in the database. Its greatest value
lies in the fact that since you've already stored it, you don't have to compile
it, you don't have to parse it,  the database server doesn't need to check its
syntax, you don't have to optimize it, and you don't have to figure out how to
access the data. All the database server has to do is execute it and then return
back values from it.

Release 5.01 is now available on Sun Microsystems computers. Availability on
other platforms will be announced as they are released; please check
InformixLink or call the office nearest you for information about your specific
platform.

 - - - - -

Informix has provided for referential integrity through ANSI-compliant
declarative integrity constraints, enabling developers to take referential
integrity for granted and focus on the other aspects of their database design.

The notable use of triggers in the industry has been to achieve referential
integrity-some database vendors provide no other means to that end. Informix






chose a different approach.

By strictly following ANSI SQL89's integrity enhancement specification, Informix
was able to fully implement integrity constraints without the use of triggers.



























































--
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
 William Daul             Advanced Support      INFORMIX SOFTWARE INC.
 4100 Bohannon Dr.        [phone] - wk
 Menlo Park, CA. 94025    uunet!infmx!billd  or  [e-mail]