October 1994 Newsletter

Location: Tysons Westpark Hotel, Route 7, McLean, Virginia

Date: Thursday, December 15th, from 8:00 am to 5:00 pm.

Invitations are being sent to over 3,500 individuals. We specially welcome participants from the NASPA users group and other user groups in the area. Last year we had over 230 participants, 16 speakers and 10 exhibitors. Participants said they learned more practical information in the forum then in any other event. Currently we are planning the following sessions and exhibits.

Participation is open to everyone. There is a $30 registration fee. A final schedule and reminder will be faxed or mailed to all registrants. Please contact John Petruzzi, Membership Director, to register, at 703-490-4598 or send in the attached form.

Our user group has grown to over 500 names on our mailing list this year. We are incorporated as a non-profit organization. Our objective is to share information, learn about new products, provide technical input, discuss problems, share solutions, and network with other Informix users. In order to cover our expenses and support continued growth, the board of directors decided to implement membership dues in 1994. The membership dues are $20.00. Membership will allow you to receive all our newsletters and a discount on our forum next year. For more information about membership, please call our Membership Director, John Petruzzi at 703-490-4598

Triggers allow you to specify actions at the time an insert, update, or delete statement is run on a table. Triggered actions do not occur until you actually perform the insert, update, or delete statement. This means that while triggers can be used for data validation, if you are using INFORMIX-4GL, you will still need to use 4GL code to let your users know they have entered invalid data onto the screen. We will discuss data entry in the chapters on INFORMIX-4GL.

A trigger may call a stored procedure. The opposite is also true, a stored procedure may call a trigger. Like stored procedures, triggers are stored with the database engine. This will cause your engine to grow slightly as you add triggers.

The syntax used to create a trigger is as follows:

create trigger <trigger name>
	{ 
	insert on <table name>
		{ referencing new [ as ] <temporary table name>
			<trigger action with foreach> |
			<trigger action>
		} |
	delete on <table name>
		{ referencing old [ as ] <temporary table name>
			<trigger action with foreach> |
			<trigger action>
		} |
	update [ of <column name> [, ...] ] on <table name>
		{ referencing { new [ as ]<temporary table name>|
					old [ as ]	<temporary table name>
				} [...]
			<trigger action with foreach> |
			<trigger action>
		 }
	  }

A trigger is designed to automatically execute when an insert, update, or delete statement is run. A create trigger involves either an insert, update, or delete on a single table.

If a trigger is on an update, you may optionally specify columns that will trigger an action. If you specify columns, only one trigger is allowed per column. Only one trigger can be called directly when a column is updated.

The referencing keyword allows you to specify a temporary table name to refer to the columns in the table before they have been updated or deleted or after they have been updated or inserted. If you are using the referencing keyword, you must also use a for each row clause as one of your actions.

A trigger action is defined as follows:

	{ before
		[ when ( <Boolean condition> ) ]
		( 	{ <insert statement> |
			<delete statement> |
			<update statement> |
			<execute procedure statement>
			} [, ...]
		)
		[, ...]     |
	for each row
		[ when ( <Boolean condition> ) ]
		( 	{ <insert statement> |
			<delete statement> |
			<update statement> |
			<execute procedure statement>
			} [, ...]
		 )
		[, ...]     |
	after
		[ when ( <Boolean condition> ) ]
		( 	{ <insert statement> |
			<delete statement> |
			<update statement> |
			<execute procedure statement>
			} [, ...]
		)
		[, ...]
	} [...]

While the above syntax does not indicate this, you may use only a single before, for each row, and after statement. You may use one of each, and they must be in that order.

The before clause performs the trigger action a single time before the triggering statement is executed.

The for each row clause performs the trigger action for each row processed after the triggering statement has executed.

The after clause performs the trigger action a single time after the triggering statement has finished executing.

The trigger action may be an insert statement, a delete statement, an update statement, an execute procedure statement, or any combination of these statements.

If you are using a before or after clause, you may not refer to the information in the triggering table. There is one exception to this: if the triggering statement is an update statement and the trigger action is an update statement and neither update statement uses the same columns.

Examples

The following are examples of triggers. The first example implements a cascading delete. This means that when a deletion is made from one table, any rows in tables that are dependent on the deleted record should also be deleted. In this case, whenever a toy_order is deleted, any items associated with that order should also be deleted.

-- This trigger deletes any toy_order_item rows associated with
each
-- deleted toy_order.
create trigger Delete_Order
	delete on toy_order
		referencing old as old_order
		 for each row
			(delete from toy_order_item
			where order_number = old_order.order_number)

This next trigger occurs whenever a new toy_store is added.

-- This trigger calls a stored procedure to generate a catalog
mailing
-- to new client toy stores, whenever a toy_store is added.
create trigger Add_Store
	insert on toy_order
		referencing new as new_store
		for each row
		(execute procedure Mail_Catalog (new_store.store_number))

The final trigger is an update trigger that uses both a for each
row and an after trigger action.

-- This trigger inserts information to a log table every time an
order
-- item is updated.  After all rows have been updated, a stored
-- procedure is called.
create trigger Update_Order_Item
	 update on toy_order_item
		 referencing old as old_order_item
			new as new_order_item
		for each row
			(insert into order_item_log values
				(old_order_item.order_number,
old_order_item.toy_code,
				new_order_item.toy_code,    
new_order_item.quantity,
				new_order_item.unit_price))
		after
			(execute procedure Check_Log ())

Looking at existing triggers

The following SQL statement will allow you to view all triggers you have entered in your database.

	select trigname, seqno, data
	from systrigbody, systriggers
	where systrigbody.datakey = "D" and
	systrigbody.trigid  = systriggers.trigid
	order by trigname, seqno

Changing and deleting existing stored procedures

The only way to change a trigger is to delete it and then recreate it. To delete a trigger, use the following syntax:

	drop trigger <trigger name>

Example:                drop trigger my_trigger

Summary

Triggers allow you to specify actions which occur at the time of an insert, update, or delete. Triggers have numerous possible applications, especially when combined with the use of Informix stored procedures. We have looked at the syntax here for creating, viewing, changing, and deleting triggers. The rest is up to you. You are limited only by your own imagination!

Note: This article is an exerpt from a new book called: Programming INFORMIX SQL/4GL: A Step by Step Approach (ISBN: 0-13-149394-9) being released by Prentice Hall at the end of September. To order: Email: orders@prenhall.com or Phone: (515) 284-6761 or (515) 284-6751.