My case:
--------
Only 1 user access the database (me). When add new records, its ok none
problem. But, when update or delete any record with same value in your fields I
have this error and nothing I can do, just rollback any action.
I tried create one INDEX (no unique) to ID field, but, I have the same error.
I dont want use [Primary Key] or [Unique Index] in ID field (this would solved
my problem, but I dont need this control in this table).
My system summary:
--------
-Windows 8.0 Pro 64bits
-Antivirus: None
-Firebird: v2.54 (32bits)
-User: SYSDBA
-Table: NEW_TABLE (just 1 table in database)
---Index: None
---Primary Key: None
---Foreigner Key: None
---Record: just 2 record with same values:
........ID = 1
........CMP_STRING = 2015-08-101
........CMP_HASH = 100716834395784016
- I add new records mannually, add one new ID field value and the other fields
have your values by TRIGGER BEFORE INSERT
- Then, I try add 2 or more record with ID = 1
- Later, I try delete any one record and I have this message:
*-*-*-*-*-*-*-*-*-*-* Firebird error message when trying delete or update any
record with same values in your fields *-*-*-*-*-*-*-*-*-*-*
.....Cannot delete record!
.....There is at least one record with same fields value!
.....Error Message:
.....----------------------------------------
.....Invalid token.
.....Dynamic SQL Error.
.....SQL error code = -104.
.....Unexpected end of command - line 2, column 66416683.
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
Any idea for this error? The Firebird dont use Internal RDB$DB_KEY (or just
DB_KEY) to control records (internal) in table when dont exist PrimaryKey or
Index?
NOTE: in the book from Helen Borrie (The Firebird Book - A Reference for
Database Developers [2004]) said in page 165, Chapter 30:
----------------------------------------------------------------------------------------------------------------------
"Use it for now, but expect enhancements in a later version of Firebird. It is
a very fast way to do bulk operations in a stored procedure, since it uses the
RDB$DB_KEY to locate its targets. RDB$DB_KEY (or just “the DBKEY”) is an
internal feature that can be used with care in a number of situations. See the
section “Optimization Topic: Using the Internal RDB$DB_KEY” at the end of this
chapter"
Chapter 30: OPTIMIZATION TOPIC: Using the Internal RDB$DB_KEY - page 634
"About RDB$DB_KEY
The first lesson to learn is that RDB$DB_KEY is a raw position, related to the
database itself and not to a physical address on disk. The second is that the
numbers do not progress in a predictable sequence. Don’t consider performing
calculations involving their relative positions! The third lesson is that they
are volatile—they change after a backup and subsequent restore and, sometimes,
after the transaction is committed. It is essential to understand the
transience of the db_key and to make no assumptions about its existence once an
operation that refers to it is committed or rolled back."
------------------------------ Here, my Executive Script in IBExpert
--------------------------------
/******************************************************************************/
/*** Generated by IBExpert 2013.2.20.1 10/08/2015 16:54:32 ***/
/******************************************************************************/
SET SQL DIALECT 3;
SET NAMES ISO8859_1;
CREATE DATABASE '127.0.0.1:F:\RAD_Projetos\prjMaquinas\DB\teste.fdb'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 16384
DEFAULT CHARACTER SET ISO8859_1 COLLATION PT_BR;
/******************************************************************************/
/*** Tables ***/
/******************************************************************************/
CREATE TABLE NEW_TABLE (
ID INTEGER NOT NULL,
CMP_STRING CHAR(30) CHARACTER SET ISO8859_1 COLLATE PT_BR,
CMP_HASH BIGINT
);
/******************************************************************************/
/*** Triggers ***/
/******************************************************************************/
SET TERM ^ ;
/******************************************************************************/
/*** Triggers for tables ***/
/******************************************************************************/
/* Trigger: NEW_TABLE_BI10 */
CREATE OR ALTER TRIGGER NEW_TABLE_BI10 FOR NEW_TABLE
ACTIVE BEFORE INSERT POSITION 10
AS
declare variable cvartexto char(30);
begin
cvartexto = '';
cvartexto = trim(cast(current_date as char(10))||cast(new.id as char(20)));
new.cmp_string = cvartexto;
new.cmp_hash = hash(cvartexto);
end
^
SET TERM ; ^
/******************************************************************************/
/*** Privileges ***/
/******************************************************************************/
/* Privileges of users */
GRANT SELECT ON RDB$FORMATS TO PUBLIC;
GRANT SELECT ON RDB$PAGES TO PUBLIC;
GRANT SELECT ON RDB$ROLES TO PUBLIC;
