Re: Informix triggers

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 thestored 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