Andrea,

What I would do is start by breaking the trigger into several pieces.
What I've done below is create a procedure that generates version
numbers, an insert trigger that checks the validity of the operation and
one, an update trigger that checks validity, an insert/update trigger
that performs the operation, and a delete trigger.

The procedure includes the insert into ElementVersion and the insert
into or update of Element. As a general rule, a trigger on an operation
that causes an update of another table is an invitation for deadlocks -
which may be what you're seeing if your problem happens when several
users are trying to change the view.

When you say "infinite loop", is the server using CPU time, or is it
just not returning?

Regards,


Ann


CREATE PROCEDURE GENERATE_VERSIONS (code integer, description varchar(30))
RETURNS (VersionID BIGINT, versionNumber BIGINT)
AS
versionNumber = NULL;
versionID = gen_id ("ElementVersionID", 1);

select max ("Version")
from "ElementVersion"
where "Code" = code
into :versionNumber;

if (versionNumber is null)
then
versionNumber = 0;
else
versionNumber = "versionNumber" +1;

insert into "ElementVersion"
("ID", "Code", "Version", "Description", "Author", "Timestamp")
values (:versionID, :Code, :versionNumber, Description,
current_user, current_timestamp);

if (:versionNumber = 0)
then
insert Into "Element"
("Code", "CurrentVersion", "StateID", "LastUpdateAuthor",
"LastUpdateTimestamp")
values (:Code, :versionNumber, 1, current_user,
current_timestamp);
else
update "Element"
set "CurrentVersion" = :versionNumber,
"LastUpdateAuthor" = current_user,
"LastUpdateTimestamp" = current_timestamp
where "Code" = :Code;

END^


CREATE TRIGGER "IngredientViewEditingI0" FOR "IngredientView" ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN

if (current_role <> 'IngredientOperator'
and current_role <> 'Administrator'
and current_user <> 'SYSDBA')
then
exception "PermissionDenied";

if (new."CurrentVersion" is not null or
new."LastUpdateAuthor" is not null or
new."LastUpdateTimestamp" is not null or
new."StateID" is not null)
then
exception "ReadOnlyFieldModified";

if (exists (select * from "Element" where "Code" = new."Code"))
then
exception "AlreadyExistingElement";

END^


CREATE TRIGGER "IngredientViewEditingIU1" FOR "IngredientView" ACTIVE
BEFORE INSERT OR UPDATE POSITION 1
AS
declare variable "versionID" BIGINT;
declare variable "versionNumber" BIGINT;
BEGIN

/* Generate ID */
EXECUTE PROCEDURE GENERATE_VERSIONS (new."Code", new."Description")
RETURNING_VALUES (:"versionID", :"VersionNumber");

insert into "Ingredient"
("ID", "TypeID", "Acq", "Peq", "Insat", "Me", "Face",
"Fohe", "Cost")
values (:"versionID", new."TypeID", new."Acq", new."Peq",
new."Insat", new."Me", new."Face", new."Fohe",
new."Cost");

END^


CREATE TRIGGER "IngredientViewEditingU0" FOR "IngredientView" ACTIVE
BEFORE UPDATE POSITION 0
AS
BEGIN

execute procedure "CheckPrivilege" (old."Code", 2);

if (new."Code" <> old."Code" or
new."CurrentVersion" <> old."CurrentVersion" or
new."LastUpdateAuthor" <> old."LastUpdateAuthor" or
new."LastUpdateTimestamp" <> old."LastUpdateTimestamp" or
new."StateID" <> old."StateID")
then
exception "ReadOnlyFieldModified";

if (not exists (select * from "Element" where "Code" = old."Code"))
then
exception "NotExistingElement";
END^


CREATE TRIGGER "IngredientViewEditingD0" FOR "IngredientView" ACTIVE
BEFORE DELETE POSITION 0
AS
BEGIN

execute procedure "CheckPrivilege" (old."Code", 3);

if (not exists (select * from "Element" where "Code" = old."Code"))
then
exception "NotExistingElement";

update "Element" set "StateID" = 0 where "Code" = old."Code";

END^