At 11:39 AM 16/12/2003 -0500, you wrote:

>Hello all,
>
>I am using the following trigger to update a value in the "Orders" table
>and the "Stations" table in my database. I know that it appears not to
>be multi user safe, but rules in the client app prevent any two person
>from being logged onto a particular "Station" which provides the
>"StationID" field below. The problem is that the trigger does not seem
>to be firing.

Triggers always fire. The problem is, if you don't code for the
possibility of bad input, you won't know whether they will do something or not.

>CREATE TRIGGER INSERTEXTORDERID FOR ORDERS
>ACTIVE BEFORE INSERT POSITION 0
>AS
> DECLARE VARIABLE UseID SmallInt;
> DECLARE VARIABLE LastExtOrderID Integer;

/* at this point both variables are null */

>BEGIN
> Begin
> SELECT UseExtOrderID, ExtOrderID
> FROM STATIONS
> WHERE STationID = NEW.StationID

/*Won't find anything if StationID is invalid, won't except either */

> INTO :UseID, :LastExtOrderID;

/* variables remain null if row was not found */

> If (:UseID = 1) THEN

/* Take the colon off the variable reference: use the colon only when the
variable is being used in a DSQL statement */

> begin
> New.ExtOrderID = :LastExtOrderID + 1;

/* Again, remove colon. But, even if UseID is 1, if variable is null then
adding 1 to it still makes it null. Of course, nothing will happen if
UseID isn't 1 */

> UPDATE Stations SET Stations.ExtOrderID =
>
>
> Stations.ExtOrderID + 1 WHERE Stations.StationID = New.StationID;

/* same story as above - nothing will happen if new.StationID isn't present
in Stations */

> end
> end
>
>END

CREATE EXCEPTION INVALID_INPUT 'Invalid input';
CREATE EXCEPTION INVALID_STATION 'Invalid station data';
COMMIT;

CREATE TRIGGER INSERTEXTORDERID FOR ORDERS
ACTIVE BEFORE INSERT POSITION 0
AS
DECLARE VARIABLE UseID SmallInt;
DECLARE VARIABLE LastExtOrderID Integer;
BEGIN
if (not exists (
SELECT 1 FROM STATIONS
WHERE STationID = NEW.StationID) then
exception INVALID_INPUT;
else
begin
SELECT UseExtOrderID, ExtOrderID
FROM STATIONS
WHERE STationID = NEW.StationID
INTO :UseID, :LastExtOrderID;
if ((UseID is null) or (LastExtOrderID is null)) then
exception INVALID_STATION;
else
begin

If (UseID = 1) THEN
begin
New.ExtOrderID = LastExtOrderID + 1;
UPDATE Stations
SET Stations.ExtOrderID = Stations.ExtOrderID + 1
WHERE Stations.StationID = New.StationID;
end
end
end
END

/heLen