description: CREATE TABLE REGTYPE (
CODE_REGTYPE INTEGER NOT NULL,
NAME VARCHAR(20),
MULTIRECORD SMALLINT
);

ALTER TABLE REGTYPE ADD CONSTRAINT PK_REGTYPE PRIMARY KEY (CODE_REGTYPE);

CREATE TABLE REG (
CODE_REG INTEGER NOT NULL,
CODE_REGTYPE INTEGER,
CODE_HORSE INTEGER
);

ALTER TABLE REG ADD CONSTRAINT PK_REG PRIMARY KEY (CODE_REG);

SET TERM ^ ;

/* Trigger: REG_BI0 */
CREATE OR ALTER TRIGGER REG_BI0 FOR REG
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (EXISTS(SELECT 1
FROM REG
INNER JOIN REGTYPE ON REG.CODE_REGTYPE = REGTYPE.CODE_REGTYPE
WHERE REG.CODE_HORSE = NEW.CODE_HORSE AND
REG.CODE_REGTYPE = NEW.CODE_REGTYPE AND
REGTYPE.MULTIRECORD = 0)) THEN
EXCEPTION E_DUPLICATE_REG;
END
^

SET TERM ; ^

unsuccessful metadata update.
CREATE OR ALTER TRIGGER REG_BI0 failed.
Dynamic SQL Error.
SQL error code = -204.
Ambiguous field name between table REG and table REG .

In Firebird 2.5.2 this trigger copile success.

=>

CREATE TABLE REGTYPE (
CODE_REGTYPE INTEGER NOT NULL,
NAME VARCHAR(20),
MULTIRECORD SMALLINT
);

ALTER TABLE REGTYPE ADD CONSTRAINT PK_REGTYPE PRIMARY KEY (CODE_REGTYPE);

CREATE TABLE REG (
CODE_REG INTEGER NOT NULL,
CODE_REGTYPE INTEGER,
CODE_HORSE INTEGER
);

ALTER TABLE REG ADD CONSTRAINT PK_REG PRIMARY KEY (CODE_REG);

SET TERM ^ ;

/* Trigger: REG_BI0 */
CREATE OR ALTER TRIGGER REG_BI0 FOR REG
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (EXISTS(SELECT 1
FROM REG
INNER JOIN REGTYPE ON REG.CODE_REGTYPE = REGTYPE.CODE_REGTYPE
WHERE REG.CODE_HORSE = NEW.CODE_HORSE AND
REG.CODE_REGTYPE = NEW.CODE_REGTYPE AND
REGTYPE.MULTIRECORD = 0)) THEN
EXCEPTION E_DUPLICATE_REG;
END
^

SET TERM ; ^

unsuccessful metadata update.
CREATE OR ALTER TRIGGER REG_BI0 failed.
Dynamic SQL Error.
SQL error code = -204.
Ambiguous field name between table REG and table REG .

In Firebird 2.5.2 this trigger compile success.

description: CREATE TABLE REGTYPE (
CODE_REGTYPE INTEGER NOT NULL,
NAME VARCHAR(20),
MULTIRECORD SMALLINT
);

ALTER TABLE REGTYPE ADD CONSTRAINT PK_REGTYPE PRIMARY KEY (CODE_REGTYPE);

CREATE TABLE REG (
CODE_REG INTEGER NOT NULL,
CODE_REGTYPE INTEGER,
CODE_HORSE INTEGER
);

ALTER TABLE REG ADD CONSTRAINT PK_REG PRIMARY KEY (CODE_REG);

SET TERM ^ ;

/* Trigger: REG_BI0 */
CREATE OR ALTER TRIGGER REG_BI0 FOR REG
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (EXISTS(SELECT 1
FROM REG
INNER JOIN REGTYPE ON REG.CODE_REGTYPE = REGTYPE.CODE_REGTYPE
WHERE REG.CODE_HORSE = NEW.CODE_HORSE AND
REG.CODE_REGTYPE = NEW.CODE_REGTYPE AND
REGTYPE.MULTIRECORD = 0)) THEN
EXCEPTION E_DUPLICATE_REG;
END
^

SET TERM ; ^

unsuccessful metadata update.
CREATE OR ALTER TRIGGER REG_BI0 failed.
Dynamic SQL Error.
SQL error code = -204.
Ambiguous field name between table REG and table REG .

In Firebird 2.5.2 this trigger compile success.

=>

CREATE TABLE REGTYPE (
CODE_REGTYPE INTEGER NOT NULL,
NAME VARCHAR(20),
MULTIRECORD SMALLINT
);

ALTER TABLE REGTYPE ADD CONSTRAINT PK_REGTYPE PRIMARY KEY (CODE_REGTYPE);

CREATE TABLE REG (
CODE_REG INTEGER NOT NULL,
CODE_REGTYPE INTEGER,
CODE_HORSE INTEGER
);

ALTER TABLE REG ADD CONSTRAINT PK_REG PRIMARY KEY (CODE_REG);

CREATE EXCEPTION E_DUPLICATE_REG 'Duplicate registration info';

SET TERM ^ ;

/* Trigger: REG_BI0 */
CREATE OR ALTER TRIGGER REG_BI0 FOR REG
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (EXISTS(SELECT 1
FROM REG
INNER JOIN REGTYPE ON REG.CODE_REGTYPE = REGTYPE.CODE_REGTYPE
WHERE REG.CODE_HORSE = NEW.CODE_HORSE AND
REG.CODE_REGTYPE = NEW.CODE_REGTYPE AND
REGTYPE.MULTIRECORD = 0)) THEN
EXCEPTION E_DUPLICATE_REG;
END
^

SET TERM ; ^

unsuccessful metadata update.
CREATE OR ALTER TRIGGER REG_BI0 failed.
Dynamic SQL Error.
SQL error code = -204.
Ambiguous field name between table REG and table REG .

In Firebird 2.5.2 this trigger compile success.

assignee: Adriano dos Santos Fernandes [ asfernandes ]

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

Link: This issue is related to QA575 [ QA575 ]

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: Done successfully

Test Details: Provided code works fine on FB >= 2.0.7.

status: Resolved [ 5 ] => Closed [ 6 ]