At 12:27 PM 29/03/2003 +0000, you wrote:

>I have a doubt about this trigger. I think that this example is
>wrong. If the intend is prevent the application to store a value, the
>trigger must be generated before the update occur. Or not? AFAIK, an
>after update event is generated after the update transaction is
>commited. I'm right? If not, when I must use the before udpate?
>
>
>SET TERM !! ;
>CREATE TRIGGER SAVE_SALARY_CHANGE FOR EMPLOYEE
>AFTER UPDATE AS
>DECLARE VARIABLE PCNT_RAISE;
>BEGIN
>PCNT_RAISE = (NEW.SALARY - OLD.SALARY) * 100 / OLD.SALARY;
>IF (OLD.SALARY <> NEW.SALARY)
>THEN
>IF (PCNT_RAISE > 50)
>THEN EXCEPTION RAISE_TOO_HIGH;
>ELSE
>BEGIN
>INSERT INTO SALARY_HISTORY (EMP_NO, CHANGE_DATE,
>UPDATER_ID, OLD_SALARY, PERCENT_CHANGE)
>VALUES (OLD.EMP_NO, 'NOW', USER, OLD.SALARY,
>PCNT_RAISE);
>END
>END !!
>SET TERM ; !!

After Update is normally the "right" time to perform changes on other
tables, that result from the new values in the triggered table. The 'NEW'
and 'OLD' values are still available, until the work is committed. And -
NO - the After Update occurs during the transaction, i.e. after the new
record version has been created but before this new version is committed to
the database.

The timing of this trigger will work OK, as it is. It will abort the whole
lot if the percentage is too high, or update the employee record and write
a history record if it is OK.

A couple of points:

1. You must declare a type for a variable.
2. Be careful with the scales of your values and make certain that
pcnt_raise is a number of a predictable scale.

The sample database is imperfect in many ways. :-|

heLen