Hi all,
I'm facing a quite serious problem. Please tell me whether I'm doing
something wrong or I've found a bug.

I have a View, "IngredientView", which selects data from "Element",
"ElementVersion" and "Ingredient".
I've made it update through a trigger, "IngredientViewEditing" which
fires on insert/update/delete.

Insertion and deletion works well, even if I'm deleting more than one
row. Update statements works as well, but only if I'm updating a single
row. If i try to update two or more row, the engine seems to enter in an
infinite loop.

Both 1.5.1 and 1.5.2 versions of Firebird don't work.

The trigger code is attached. Please tell me if I'm doing something wrong.

Regards,
Andrea

--
Andrea Sansottera
UGIdotNET [Italian] http://www.ugidotnet.org
My weblog [Italian] http://blogs.ugidotnet.org/andrew/


----------

CREATE TRIGGER "IngredientViewEditing" FOR "IngredientView" ACTIVE
BEFORE INSERT OR UPDATE OR DELETE POSITION 0
AS
declare variable "versionID" BIGINT;
declare variable "versionNumber" BIGINT;
BEGIN


if (inserting) then
if (current_role <> 'IngredientOperator' and current_role <> 'Administrator' and current_user <> 'SYSDBA') then
exception "PermissionDenied";
if (updating) then
execute procedure "CheckPrivilege" (old."Code", 2);
if (deleting) then
execute procedure "CheckPrivilege" (old."Code", 3);




if (inserting) then
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 (updating) then
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 (inserting) then
begin
if (exists (select * from "Element" where "Code" = new."Code")) then
exception "AlreadyExistingElement";
end


if (updating or deleting) then
begin
if (not exists (select * from "Element" where "Code" = old."Code")) then
exception "NotExistingElement";
end



if (inserting or updating) then
begin
/* Generates ID */
"versionID" = gen_id ("ElementVersionID", 1);

select max ("Version") from "ElementVersion"
where "Code" = new."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", new."Code", :"versionNumber", new."Description", current_user, current_timestamp);

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

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


if (deleting) then
begin
update "Element" set "StateID" = 0 where "Code" = old."Code";
end

END

[Non-text portions of this message have been removed]