Hi,

Problem lokalized and solved. This view is upadatable and in triggers
sections for (inserting/updating/deleting) are not needed. Sufficient is
only new.rokewi=2014 or old.rokewi=2014 regardles from trigger state
(insert/update/delete).



Hi,

Firebird 1.5.6 CS tested on Linux and Windows 2008R2.

During deletin record from view I got an error: Unsuccessful execution
coused by a system error that precludes successful execution of subsequent
statements. internal gds consistency check (record length incostistent
(178)).
Error apears from time to time. Sometimes I can delete 10-15 records.
Sometimes only 2-3. After backup - restore nothing changed.


Tested in IBExpert and my application wrriten in Delphi with IBO

Table:

CREATE TABLE STANY (
ROKEWI DOM_INTEGER /* DOM_INTEGER = INTEGER */,
KONTO DOM_KONTO_KS NOT NULL COLLATE PXW_PLK /* DOM_KONTO_KS =
VARCHAR(25) */,
NAZWA DOM_NAZWA_KS NOT NULL COLLATE PXW_PLK /* DOM_NAZWA_KS =
VARCHAR(50) */,
BIL DOM_SMALLINT DEFAULT 0 NOT NULL /* DOM_SMALLINT =
SMALLINT */,
GRUPA DOM_GRUPA_KS COLLATE PXW_PLK /* DOM_GRUPA_KS =
VARCHAR(30) */,
BO_WN DOM_KWOTA_KS DEFAULT 0.0 /* DOM_KWOTA_KS = NUMERIC(16,2)
DEFAULT 0.0 */,
BO_MA DOM_KWOTA_KS DEFAULT 0.0 /* DOM_KWOTA_KS = NUMERIC(16,2)
DEFAULT 0.0 */,
ON_WN DOM_KWOTA_KS DEFAULT 0.0 /* DOM_KWOTA_KS = NUMERIC(16,2)
DEFAULT 0.0 */,
ON_MA DOM_KWOTA_KS DEFAULT 0.0 /* DOM_KWOTA_KS = NUMERIC(16,2)
DEFAULT 0.0 */,
OM_WN1 DOM_KWOTA_KS DEFAULT 0.0 /* DOM_KWOTA_KS = NUMERIC(16,2)
DEFAULT 0.0 */,
OM_MA1 DOM_KWOTA_KS DEFAULT 0.0 /* DOM_KWOTA_KS = NUMERIC(16,2)
DEFAULT 0.0 */,
OM_WN2 DOM_KWOTA_KS DEFAULT 0.0 /* DOM_KWOTA_KS = NUMERIC(16,2)
DEFAULT 0.0 */,
OM_MA2 DOM_KWOTA_KS DEFAULT 0.0 /* DOM_KWOTA_KS = NUMERIC(16,2)
DEFAULT 0.0 */,
OM_WN3 DOM_KWOTA_KS DEFAULT 0.0 /* DOM_KWOTA_KS = NUMERIC(16,2)
DEFAULT 0.0 */,
OM_MA3 DOM_KWOTA_KS DEFAULT 0.0 /* DOM_KWOTA_KS = NUMERIC(16,2)
DEFAULT 0.0 */,
OM_WN4 DOM_KWOTA_KS DEFAULT 0.0 /* DOM_KWOTA_KS = NUMERIC(16,2)
DEFAULT 0.0 */,
OM_MA4 DOM_KWOTA_KS DEFAULT 0.0 /* DOM_KWOTA_KS = NUMERIC(16,2)
DEFAULT 0.0 */,
OM_WN5 DOM_KWOTA_KS DEFAULT 0.0 /* DOM_KWOTA_KS = NUMERIC(16,2)
DEFAULT 0.0 */,
OM_MA5 DOM_KWOTA_KS DEFAULT 0.0 /* DOM_KWOTA_KS = NUMERIC(16,2)
DEFAULT 0.0 */,
OM_WN6 DOM_KWOTA_KS DEFAULT 0.0 /* DOM_KWOTA_KS = NUMERIC(16,2)
DEFAULT 0.0 */,
OM_MA6 DOM_KWOTA_KS DEFAULT 0.0 /* DOM_KWOTA_KS = NUMERIC(16,2)
DEFAULT 0.0 */,
OM_WN7 DOM_KWOTA_KS DEFAULT 0.0 /* DOM_KWOTA_KS = NUMERIC(16,2)
DEFAULT 0.0 */,
OM_MA7 DOM_KWOTA_KS DEFAULT 0.0 /* DOM_KWOTA_KS = NUMERIC(16,2)
DEFAULT 0.0 */,
OM_WN8 DOM_KWOTA_KS DEFAULT 0.0 /* DOM_KWOTA_KS = NUMERIC(16,2)
DEFAULT 0.0 */,
OM_MA8 DOM_KWOTA_KS DEFAULT 0.0 /* DOM_KWOTA_KS = NUMERIC(16,2)
DEFAULT 0.0 */,
OM_WN9 DOM_KWOTA_KS DEFAULT 0.0 /* DOM_KWOTA_KS = NUMERIC(16,2)
DEFAULT 0.0 */,
OM_MA9 DOM_KWOTA_KS DEFAULT 0.0 /* DOM_KWOTA_KS = NUMERIC(16,2)
DEFAULT 0.0 */,
OM_WN10 DOM_KWOTA_KS DEFAULT 0.0 /* DOM_KWOTA_KS = NUMERIC(16,2)
DEFAULT 0.0 */,
OM_MA10 DOM_KWOTA_KS DEFAULT 0.0 /* DOM_KWOTA_KS = NUMERIC(16,2)
DEFAULT 0.0 */,
OM_WN11 DOM_KWOTA_KS DEFAULT 0.0 /* DOM_KWOTA_KS = NUMERIC(16,2)
DEFAULT 0.0 */,
OM_MA11 DOM_KWOTA_KS DEFAULT 0.0 /* DOM_KWOTA_KS = NUMERIC(16,2)
DEFAULT 0.0 */,
OM_WN12 DOM_KWOTA_KS DEFAULT 0.0 /* DOM_KWOTA_KS = NUMERIC(16,2)
DEFAULT 0.0 */,
OM_MA12 DOM_KWOTA_KS DEFAULT 0.0 /* DOM_KWOTA_KS = NUMERIC(16,2)
DEFAULT 0.0 */,
NR_REJ DOM_INTEGER DEFAULT 0 /* DOM_INTEGER = INTEGER */,
STR_REJ DOM_INTEGER DEFAULT 0 /* DOM_INTEGER = INTEGER */,
ZAKUP DOM_SMALLINT DEFAULT 0 /* DOM_SMALLINT = SMALLINT */,
STRONA DOM_STRONA_STANY_KS COLLATE PXW_PLK /*
DOM_STRONA_STANY_KS = VARCHAR(1) */,
KON_STO DOM_KONTO_KS COLLATE PXW_PLK /* DOM_KONTO_KS =
VARCHAR(25) */,
STR_STO DOM_INTEGER DEFAULT 0 /* DOM_INTEGER = INTEGER */,
OPIS DOM_BLOB_TXT /* DOM_BLOB_TXT = BLOB SUB_TYPE 1 SEGMENT
SIZE 80 */,
RODZAJ DOM_VARCHAR_40 COLLATE PXW_PLK /* DOM_VARCHAR_40 =
VARCHAR(40) */,
PLAN_WN DOM_KWOTA_KS DEFAULT 0.0 /* DOM_KWOTA_KS = NUMERIC(16,2)
DEFAULT 0.0 */,
PLAN_MA DOM_KWOTA_KS DEFAULT 0.0 /* DOM_KWOTA_KS = NUMERIC(16,2)
DEFAULT 0.0 */,
WALUTOWE DOM_SMALLINT DEFAULT 0 /* DOM_SMALLINT = SMALLINT */,
WALUTA DOM_WALUTA COLLATE PXW_PLK /* DOM_WALUTA = VARCHAR(3) */,
BO_WNW DOM_KWOTA_KS DEFAULT 0.0 /* DOM_KWOTA_KS = NUMERIC(16,2)
DEFAULT 0.0 */,
BO_MAW DOM_KWOTA_KS DEFAULT 0.0 /* DOM_KWOTA_KS = NUMERIC(16,2)
DEFAULT 0.0 */,
BILANS DOM_VARCHAR_10 COLLATE PXW_PLK /* DOM_VARCHAR_10 =
VARCHAR(10) */,
RZIS DOM_VARCHAR_10 COLLATE PXW_PLK /* DOM_VARCHAR_10 =
VARCHAR(10) */,
PLANN DOM_INTEGER DEFAULT 0 /* DOM_INTEGER = INTEGER */,
PLANR DOM_INTEGER DEFAULT 0 /* DOM_INTEGER = INTEGER */,
PLAN1 DOM_KWOTA_KS DEFAULT 0.0 /* DOM_KWOTA_KS = NUMERIC(16,2)
DEFAULT 0.0 */,
PLAN2 DOM_KWOTA_KS DEFAULT 0.0 /* DOM_KWOTA_KS = NUMERIC(16,2)
DEFAULT 0.0 */,
PLAN3 DOM_KWOTA_KS DEFAULT 0.0 /* DOM_KWOTA_KS = NUMERIC(16,2)
DEFAULT 0.0 */,
PLAN4 DOM_KWOTA_KS DEFAULT 0.0 /* DOM_KWOTA_KS = NUMERIC(16,2)
DEFAULT 0.0 */,
PLAN5 DOM_KWOTA_KS DEFAULT 0.0 /* DOM_KWOTA_KS = NUMERIC(16,2)
DEFAULT 0.0 */,
PLAN6 DOM_KWOTA_KS DEFAULT 0.0 /* DOM_KWOTA_KS = NUMERIC(16,2)
DEFAULT 0.0 */,
PLAN7 DOM_KWOTA_KS DEFAULT 0.0 /* DOM_KWOTA_KS = NUMERIC(16,2)
DEFAULT 0.0 */,
PLAN8 DOM_KWOTA_KS DEFAULT 0.0 /* DOM_KWOTA_KS = NUMERIC(16,2)
DEFAULT 0.0 */,
PLAN9 DOM_KWOTA_KS DEFAULT 0.0 /* DOM_KWOTA_KS = NUMERIC(16,2)
DEFAULT 0.0 */,
PLAN10 DOM_KWOTA_KS DEFAULT 0.0 /* DOM_KWOTA_KS = NUMERIC(16,2)
DEFAULT 0.0 */,
PLAN11 DOM_KWOTA_KS DEFAULT 0.0 /* DOM_KWOTA_KS = NUMERIC(16,2)
DEFAULT 0.0 */,
PLAN12 DOM_KWOTA_KS DEFAULT 0.0 /* DOM_KWOTA_KS = NUMERIC(16,2)
DEFAULT 0.0 */,
PLAN_BO DOM_KWOTA_KS DEFAULT 0.0 /* DOM_KWOTA_KS = NUMERIC(16,2)
DEFAULT 0.0 */,
IDCDN DOM_BIGINT /* DOM_BIGINT = BIGINT */,
NUMERCDN DOM_VARCHAR_50 COLLATE PXW_PLK /* DOM_VARCHAR_50 =
VARCHAR(50) */,
OKRESCDN DOM_INTEGER /* DOM_INTEGER = INTEGER */,
ROZRACHUNKOWE DOM_SMALLINT /* DOM_SMALLINT = SMALLINT */
);

CREATE INDEX STANY_IDX1 ON STANY (IDCDN);
CREATE INDEX STANY_IDX2 ON STANY (OKRESCDN, NUMERCDN);
CREATE INDEX STANY_NAZWA ON STANY (ROKEWI, NAZWA);
CREATE UNIQUE INDEX STANY_PK ON STANY (ROKEWI, KONTO);

View:

CREATE VIEW ST14(
KONTO,
NAZWA,
BIL,
GRUPA,
BO_WN,
BO_MA,
ON_WN,
ON_MA,
OM_WN1,
OM_MA1,
OM_WN2,
OM_MA2,
OM_WN3,
OM_MA3,
OM_WN4,
OM_MA4,
OM_WN5,
OM_MA5,
OM_WN6,
OM_MA6,
OM_WN7,
OM_MA7,
OM_WN8,
OM_MA8,
OM_WN9,
OM_MA9,
OM_WN10,
OM_MA10,
OM_WN11,
OM_MA11,
OM_WN12,
OM_MA12,
NR_REJ,
STR_REJ,
ZAKUP,
STRONA,
KON_STO,
STR_STO,
OPIS,
RODZAJ,
PLAN_WN,
PLAN_MA,
WALUTOWE,
WALUTA,
BO_WNW,
BO_MAW,
BILANS,
RZIS,
PLANN,
PLANR,
PLAN1,
PLAN2,
PLAN3,
PLAN4,
PLAN5,
PLAN6,
PLAN7,
PLAN8,
PLAN9,
PLAN10,
PLAN11,
PLAN12,
PLAN_BO,
ROKEWI)
AS
select
KONTO, NAZWA, BIL, GRUPA, BO_WN, BO_MA, ON_WN, ON_MA, OM_WN1, OM_MA1,
OM_WN2, OM_MA2, OM_WN3, OM_MA3, OM_WN4, OM_MA4, OM_WN5
, OM_MA5, OM_WN6, OM_MA6, OM_WN7, OM_MA7, OM_WN8, OM_MA8, OM_WN9, OM_MA9,
OM_WN10, OM_MA10, OM_WN11, OM_MA11, OM_WN12, OM_MA12
, NR_REJ, STR_REJ, ZAKUP, STRONA, KON_STO, STR_STO, OPIS, RODZAJ, PLAN_WN,
PLAN_MA, WALUTOWE, WALUTA, BO_WNW, BO_MAW, BILANS, RZIS
, PLANN, PLANR, PLAN1, PLAN2, PLAN3, PLAN4, PLAN5, PLAN6, PLAN7, PLAN8,
PLAN9, PLAN10, PLAN11, PLAN12, PLAN_BO, ROKEWI
from STANY WHERE ROKEWI=2014
;

/******************************************************************************/
/**** Triggers
****/
/******************************************************************************/
/******************************************************************************/
/**** Triggers for updatable views
****/
/******************************************************************************/
CREATE TRIGGER ST14_BIUD FOR ST14
ACTIVE BEFORE INSERT OR UPDATE OR DELETE POSITION 0
AS
BEGIN
POST_EVENT 'DUMMY_EVENT';
END

/******************************************************************************/
ALTER TRIGGER ST14_BIUD
AS
declare variable tablica varchar(31);
declare variable rokewi integer;
begin
tablica='ST14';
rokewi=2014;
if (inserting or updating) then begin
UPDATE STANY
SET KONTO=NEW.KONTO, NAZWA = NEW.NAZWA, BIL = NEW.BIL, GRUPA =
NEW.GRUPA,
BO_WN = NEW.BO_WN, BO_MA = NEW.BO_MA,
ON_WN = NEW.ON_WN, ON_MA = NEW.ON_MA,
OM_WN1 = NEW.OM_WN1, OM_MA1 = NEW.OM_MA1,
OM_WN2 = NEW.OM_WN2, OM_MA2 = NEW.OM_MA2,
OM_WN3 = NEW.OM_WN3, OM_MA3 = NEW.OM_MA3,
OM_WN4 = NEW.OM_WN4, OM_MA4 = NEW.OM_MA4,
OM_WN5 = NEW.OM_WN5, OM_MA5 = NEW.OM_MA5,
OM_WN6 = NEW.OM_WN6, OM_MA6 = NEW.OM_MA6,
OM_WN7 = NEW.OM_WN7, OM_MA7 = NEW.OM_MA7,
OM_WN8 = NEW.OM_WN8, OM_MA8 = NEW.OM_MA8,
OM_WN9 = NEW.OM_WN9, OM_MA9 = NEW.OM_MA9,
OM_WN10 = NEW.OM_WN10, OM_MA10 = NEW.OM_MA10,
OM_WN11 = NEW.OM_WN11, OM_MA11 = NEW.OM_MA11,
OM_WN12 = NEW.OM_WN12, OM_MA12 = NEW.OM_MA12,
NR_REJ = NEW.NR_REJ, STR_REJ = NEW.STR_REJ,
ZAKUP = NEW.ZAKUP, STRONA = NEW.STRONA,
KON_STO = NEW.KON_STO, STR_STO = NEW.STR_STO,
RODZAJ = NEW.RODZAJ, PLAN_WN = NEW.PLAN_WN,
PLAN_MA = NEW.PLAN_MA, WALUTOWE = NEW.WALUTOWE,
WALUTA = NEW.WALUTA, BO_WNW = NEW.BO_WNW,
BO_MAW = NEW.BO_MAW, BILANS = NEW.BILANS,
RZIS = NEW.RZIS, PLANN = NEW.PLANN,
PLANR = NEW.PLANR, PLAN1 = NEW.PLAN1,
PLAN2 = NEW.PLAN2, PLAN3 = NEW.PLAN3,
PLAN4 = NEW.PLAN4, PLAN5 = NEW.PLAN5,
PLAN6 = NEW.PLAN6, PLAN7 = NEW.PLAN7,
PLAN8 = NEW.PLAN8, PLAN9 = NEW.PLAN9,
PLAN10 = NEW.PLAN10, PLAN11 = NEW.PLAN11,
PLAN12 = NEW.PLAN12, PLAN_BO = NEW.PLAN_BO
WHERE (ROKEWI = OLD.ROKEWI) AND (KONTO = OLD.KONTO);
if (ROW_COUNT=0) then
INSERT INTO STANY (ROKEWI, KONTO, NAZWA, BIL, GRUPA, BO_WN, BO_MA,
ON_WN, ON_MA
, OM_WN1, OM_MA1, OM_WN2, OM_MA2, OM_WN3, OM_MA3,
OM_WN4, OM_MA4, OM_WN5, OM_MA5, OM_WN6, OM_MA6
, OM_WN7, OM_MA7, OM_WN8, OM_MA8, OM_WN9, OM_MA9,
OM_WN10, OM_MA10, OM_WN11, OM_MA11, OM_WN12, OM_MA12
, NR_REJ, STR_REJ, ZAKUP, STRONA, KON_STO, STR_STO,
OPIS, RODZAJ, PLAN_WN, PLAN_MA, WALUTOWE, WALUTA
, BO_WNW, BO_MAW, BILANS, RZIS
, PLANN, PLANR, PLAN1, PLAN2, PLAN3, PLAN4, PLAN5, PLAN6
, PLAN7, PLAN8, PLAN9, PLAN10, PLAN11, PLAN12, PLAN_BO)
values(:ROKEWI, NEW.KONTO, NEW.NAZWA, NEW.BIL, NEW.GRUPA,
NEW.BO_WN, NEW.BO_MA, NEW.ON_WN, NEW.ON_MA
, NEW.OM_WN1, NEW.OM_MA1, NEW.OM_WN2, NEW.OM_MA2,
NEW.OM_WN3, NEW.OM_MA3, NEW.OM_WN4, NEW.OM_MA4, NEW.OM_WN5, NEW.OM_MA5,
NEW.OM_WN6, NEW.OM_MA6
, NEW.OM_WN7, NEW.OM_MA7, NEW.OM_WN8, NEW.OM_MA8,
NEW.OM_WN9, NEW.OM_MA9, NEW.OM_WN10, NEW.OM_MA10, NEW.OM_WN11, NEW.OM_MA11,
NEW.OM_WN12, NEW.OM_MA12
, NEW.NR_REJ, NEW.STR_REJ, NEW.ZAKUP, NEW.STRONA,
NEW.KON_STO, NEW.STR_STO
, NEW.OPIS, NEW.RODZAJ, NEW.PLAN_WN, NEW.PLAN_MA,
NEW.WALUTOWE, NEW.WALUTA
, NEW.BO_WNW, NEW.BO_MAW, NEW.BILANS, NEW.RZIS
, NEW.PLANN, NEW.PLANR, NEW.PLAN1, NEW.PLAN2, NEW.PLAN3,
NEW.PLAN4, NEW.PLAN5, NEW.PLAN6
, NEW.PLAN7, NEW.PLAN8, NEW.PLAN9, NEW.PLAN10,
NEW.PLAN11, NEW.PLAN12, NEW.PLAN_BO);
end
if (deleting) then delete from stany where rokewi=old.rokewi and
konto=old.konto;

end


Thanks for any help or hint
Jacek



------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links