Advanced PowerBuilder

An Introduction to Triggers

If you know about triggers, you can skip this section. In case you don't, I think it will give you a good starting point about triggers.

In relational database terms, a trigger is nothing but one or more SQL statements put together and are fired on a specific event. For example, in SQL Server you can write three triggers for a table. One trigger for INSERT operation, one for UPDATE and the other one is for DELETE.

The following example assumes that a BEGIN TRANSACTION is being used and there are no nested transactions and there are no errors in the whole process.

For example, if you write a trigger for INSERT operation on trans table, the trigger is fired every time you issue INSERT statement and all the SQL statements written in the trigger will execute. In Sybase SQL Server, as soon as you issue an INSERT statement, the changes required for the INSERT statement in the trans table are logged (written to the syslogs table). Then the trigger written for the INSERT operation executes and upon completion, the control of execution flow goes to the next statement after that INSERT statement. The changes will be written to the database (actual table trans) when the control reaches COMMIT statement. If execution flow encounters ROLLBACK instead of COMMIT, then no changes are done to the trans table.

You may want to validate the changes that are being done. For example, if the transaction is being taking place on a holiday, you may want to reject that transaction. How do you access the data that is being inserted? Well, the implementation differs in each database. In Sybase SQL Server, two read-only magic tables inserted and deleted are made available in the trigger code. These tables are not available anywhere else other than in the trigger code. In the trigger written for the INSERT operation, you can query 'inserted' table and get the data that is being inserted and do your checking. For the above example sake, let's assume the transaction date is a holiday and you want to reject that transaction. Then you can either ROLLBACK TRIGGER OR ROLLBACK TRANSACTION. The statement you use depends on the SQL Server version and other business logic.

In Sybase SQL Server, a trigger is fired once per statement irrespective of the number of rows effected by that statement. If you are using a SELECT … INTO… statement or INSERT...SELECT statement and if it inserts 10,000 rows or if it inserts zero rows, the trigger is going to fire only once. You need to either use a CURSOR or temp table or SET ROWCOUNT 1 or another technique to read each row from the inserted row and apply business rules.

However, triggers in Adaptive Server Anywhere works in a different way. Basically, there are two types of triggers available for each type of operation, i.e., INSERT, DELETE and UPDATE. First type is row level trigger and the second one is statement or table level trigger. In case of row level trigger, you can write trigger to fire before the actual operation and/or after the actual operation. For example, you can write a 'before' INSERT trigger and a 'after' INSERT trigger. The following is the CREATE TRIGGER statement syntax

CREATE TRIGGER trigger-name trigger-time trigger-event [, trigger-event,..] ... [ ORDER integer ] ON table-name ... [ REFERENCING [ OLD AS old-name ] [ NEW AS new-name ] ] [ REMOTE AS remote-name ] ] ... [ FOR EACH { ROW | STATEMENT } ] ... [ WHEN ( search-condition ) ] ... [ IF UPDATE ( column-name ) THEN ... [ { AND | OR } UPDATE ( column-name ) ] ... ] ... compound-statement ... [ ELSEIF UPDATE ( column-name ) THEN ... [ { AND | OR } UPDATE ( column-name ) ] ... ... compound-statement ... END IF ] ]

When you write a row level trigger you can access the data that is being changed using an alias to OLD and NEW. In the following update trigger example, the old data is being accessed from old_data.

Create trigger tran_upd after update on trans referencing old as old_data, referencing new as new_data …

That means if you update tran_qty from 100 to 200, old_data.tran_qty contains 100 and new_data.tran_qty contains 200. In row level triggers the aliases 'old_data' and 'new_data' are available as a record -- which is not the case in the statement level triggers -- that means, you can't give SELECT * from old_tran in the row-level trigger.

... [ REFERENCING [ OLD AS old-name ] [ NEW AS new-name ] ]

However, if you write the same trigger for statement (table) level, the trigger will fire only once irrespective of the number of rows effected by that operation. In this case, within the trigger code you need to use FOR EACH STATEMENT and old_data from the above example is referred as a table like in Sybase SQL Server, that means you can give SELECT statements on that table. Please note that FOR EACH STATEMENT syntax is not allowed in row-level triggers, only FOR EACH ROW syntax is allowed.

The trigger mechanism in other databases may be different than from the databases explained above. I think the above explanation will give you a good starting point in case you are not familiar with relational databases.

Home	Previous Lesson: UPDATE Trigger On trans Table Next Lesson: DropDown/Child DataWindows