At 04:09 PM 09-01-02 +0000, you wrote:

>I have created table with the following definition:
>
>/* Table: EVENT_TYPE, Owner: SYSDBA */
>CREATE TABLE "EVENT_TYPE"
>(
> "EVT_EVTID" CHAR(20)NOT NULL,
> "EVT_NAME" CHAR(15)NOT NULL,
> "EVT_PARENT" CHAR(20),
>CONSTRAINT "PK_EVT_EVTID" PRIMARY KEY ("EVT_EVTID")
>);
>ALTER TABLE "EVENT_TYPE" ADD
> CONSTRAINT "CK_EVT_PARENT"
>CHECK (Evt_Parent BETWEEN '1' AND '4' or Evt_Parent is null);
>
>I have the following trigger which should check the table to see if
>the values which are about to be entered into the columns evt_Id and
>evt_parent are unique (can't use a unique constraint as evt_parent
>can accept null values).
>
>SET TERM ^ ;
>CREATE TRIGGER "EVT_BI" FOR "EVENT_TYPE" ACTIVE BEFORE INSERT
>POSITION 0
>AS
> DECLARE VARIABLE Counter SmallInt;
>BEGIN
> SELECT COUNT(*) FROM Event_Type
> Where Evt_Name=NEW.Evt_Name and Evt_Parent=NEW.Evt_Parent INTO
>Counter;
> IF (Counter <1)
> THEN
> IF (NEW.EVT_EVTID IS NULL) THEN
> NEW.EVT_EVTID = GEN_ID(GEN_EVT_ID, 1);
> ELSE
> EXCEPTION Exist_Ex;
>END
>
>When I try to insert a value I receive the following error:
>"Unsuccessful execution caused by system error that does not preclude
>successful execution of subsequent statements.
>index unexpectedly deleted."
>Can any explain what I have done wrong and how to correct it

Well, you have declared EVT.EVT.ID as a CHAR(20) and your code is trying to push a NUMERIC(18,0) into it by way of a call to a generator. If you must do this, then cast
the returned generator to a CHAR(20), i.e.

NEW.EVT_EVTID = CAST(GEN_ID(GEN_EVT_ID, 1) AS CHAR(20)) ;

But you have another problem here that will bite you. If the trigger performs this code and your generator value is, say 14, then it will store '14' followed by 18 significant blanks. If at some time a user enters the string '14' then, to the engine, these two values will be seen as not equal. (The same goes if you have another row with the value '14 ').

A similar problem applies to EVT_PARENT - your CHECK constraint on that column would only make sense on a CHAR(1) column.

If you have no choice but to rely on user-entered values and to use this fall-back strategy, change EVT_ID to a varchar(20) and EVT_PARENT to CHAR(1).

By the way, if you have a NOT NULL constraint on EVT_ID, you will be able to place a UNIQUE composite index on those two columns. It's not very useful, though, given the vulnerability of this column to false mismatches.

Another problem you have here (I don't know whether you have made this error in your actual schema) is that you defined the table and columns with quoted identifiers ("EVENT_TYPE") and then you refer to them in your procedure source in lower case and without a quoted identifier (SELECT COUNT(*) FROM Event_Type
Where Evt_Name=NEW.Evt_Name and Evt_Parent=NEW.Evt_Parent...)

This will bite you too.

And one more problem with your logic:

SELECT COUNT(*) FROM Event_Type
Where Evt_Name=NEW.Evt_Name and Evt_Parent=NEW.Evt_Parent

will not count any rows where EVT_PARENT is null, if NEW.EVT_PARENT. NULL=NULL does not evaluate to TRUE.

If this was mine, I would change EVT_ID to a NUMERIC(18,0), subject it to a generator and let the engine's rules do their work; change EVT_PARENT to a char(1); and replace this trigger with one that invokes the generator instead of this hairy (and multi-user-unsafe) SELECT COUNT check.

regards,
Helen


All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org
_______________________________________________________