Board index » delphi » Key Violation occurs before 'Before Insert'

Key Violation occurs before 'Before Insert'

This is so strange....I have a trigger like the following

CREATE TRIGGER CREATE_ACTIVITYID FOR ACTIVITY BEFORE INSERT POSITION 0 AS
BEGIN
  if (new.ActivityID is NULL) then
       New.ActivityID = GEN_ID(ACTIVITY_GEN, 1);
  insert into test (test)
  values(new.activityid);
END

Note: ActivityID is a primary key

In my delphi program, I've got a "live" query
Select * From Activity Order By ActivityName

When I run the program, and using the DBNavigator, insert a new record
leaving ActivityID=null, I'd expect this trigger to work correctly before
it inserts the record; however, I get a Key violation on the primary key.  

Key Violation
violation of primary or unique key constraint integ_6 on table 'activity'

This is so goofy it's driving me crazy.  Why is the constraint checking
before a 'before insert'?!? What am I doing wrong?!?

Randy Trexler

 

Re:Key Violation occurs before 'Before Insert'


The required property of ActivityId should be set to false if that's any
help. It really is required but this is just a workaround.

Quote
Randy Trexler wrote:

> This is so strange....I have a trigger like the following

> CREATE TRIGGER CREATE_ACTIVITYID FOR ACTIVITY BEFORE INSERT POSITION 0 AS
> BEGIN
>   if (new.ActivityID is NULL) then
>        New.ActivityID = GEN_ID(ACTIVITY_GEN, 1);
>   insert into test (test)
>   values(new.activityid);
> END

> Note: ActivityID is a primary key

> In my delphi program, I've got a "live" query
> Select * From Activity Order By ActivityName

> When I run the program, and using the DBNavigator, insert a new record
> leaving ActivityID=null, I'd expect this trigger to work correctly before
> it inserts the record; however, I get a Key violation on the primary key.

> Key Violation
> violation of primary or unique key constraint integ_6 on table 'activity'

> This is so goofy it's driving me crazy.  Why is the constraint checking
> before a 'before insert'?!? What am I doing wrong?!?

> Randy Trexler

--
Steve Moran
Flying Objects Ltd, London.
--
Note: When replying please delete the first dot from my return address.

Re:Key Violation occurs before 'Before Insert'


Randy:

Steve Moran is correct in stating that you need to set the Required
property to False for the field attached to ActivityID, but I don't believe
that is the cause of the problem you are experiencing. The message you are
getting from InterBase is telling you that the generated ID is not unique
(i.e., it already exists).

If you haven't done this already, go into InterBase ISQL and select
View-Metadata Information from the menu. In the "View Information On"
combobox, select "Generator" and enter the name of your generator
(ACTIVITY_GEN) in the "Object Name" edit field. When you click OK, ISQL
will show you   the current generator value in the output area (don't let
the mis-named label "Next key" fool you; if it says "41" it means that "42"
will be the next generated ID, assuming you increment by one).

Now, select MAX(ActivityID) from ACTIVITY. Is it greater than the current
value of your generator? If so, that means that someone is entering
ActivityID values instead of letting the generator assign a key, so you
will have ID collisions from time to time. That's why when you have a table
where keys are automatically generated it is a good idea to not permit the
user to enter the key themselves... make the ActivityID field read-only, if
you must display it at all.
--
Scott Gammans
Senior Systems Analyst, Deltek Systems, Inc.
(Remove the .spam_filter if replying by mail!)

Randy Trexler <trexl...@earthlink.net> wrote in article
<01bd04fd$a74fbe20$c25f2399@trexler1>...

Quote
> This is so strange....I have a trigger like the following

> CREATE TRIGGER CREATE_ACTIVITYID FOR ACTIVITY BEFORE INSERT POSITION 0 AS
> BEGIN
>   if (new.ActivityID is NULL) then
>        New.ActivityID = GEN_ID(ACTIVITY_GEN, 1);
>   insert into test (test)
>   values(new.activityid);
> END

> Note: ActivityID is a primary key

> In my delphi program, I've got a "live" query
> Select * From Activity Order By ActivityName

> When I run the program, and using the DBNavigator, insert a new record
> leaving ActivityID=null, I'd expect this trigger to work correctly before
> it inserts the record; however, I get a Key violation on the primary key.

> Key Violation
> violation of primary or unique key constraint integ_6 on table 'activity'

> This is so goofy it's driving me crazy.  Why is the constraint checking
> before a 'before insert'?!? What am I doing wrong?!?

> Randy Trexler

Re:Key Violation occurs before 'Before Insert'


Randy

I'm assuming that you're not using persistent fields.  What happens is
that Delphi creates TField components at run-time for each field in
your TQuery.  Because you have declared a PK, Delphi sets the
'Required' property for that component to True.  The checking of this
Required property is done before you reach the BeforeInsert event.

The simple answer is to create persistent fields at design time and set
the 'Required' property to false.

Derek Davidson
Author of DK's Audit Components
Get a FREE copy from my web site at :
http://freespace.{*word*269}.net/d.davidson

(Remove the x to EMail me : der...@mksoft.com)

Other Threads