LIBS - Triggers and Generators 
Author Message
 LIBS - Triggers and Generators

Hi,

I'm having a bit of a problem getting an automatic "insert trigger"
key generated using the Local Interbase Server and WISQL.
I've tried a "before insert" trigger using a key value
stored in another table, using an update query to increment the value
and a subquery to extract the new value and put it into the NEW. value
of the key. However, LIBS gives an error message and tells me that
a subquery cannot be used in this context -- which I assume means that
a subquery cannot be used in a trigger statement. However, I thought
that "singelton" queries were okay -- what gives? The query looks like:
   NEW.key_column = (select key_value from autokeys
                     where (key_id = "key_column"));
where autokeys is (key_id char(25), key_value smallint).

So then I tried the Interbase way, using a generator, but found that
generators do not respect transactions. So if a user attempts to insert
an incorrect row, the generator is tripped in the "before insert", the
insert fails, but the generator does not revert to the prior value.
This is very problematic for systems that need to generate sequential
numbers. Does anyone know a way around this? (Besides removing all
constraints from the row so that an insert never fails!)

Any other methods that work?

Thanks,

Erik.
--
-----------------------------------------------------------_________  ---+
| Erik Pearson                       |    /////  ////  //   --------\\\  |
| University of California, Berkeley |   //     /  /  //   ---//     >/  |
|                                    |  //     ////  //       >====//    |
| "Signature file in progress..."    | /////  /  /  /////   //     >>    |
+--------------------------------------------------------- //_____//-----+
                                                         ---------



Mon, 16 Mar 1998 03:00:00 GMT  
 LIBS - Triggers and Generators

Quote:
>I'm having a bit of a problem getting an automatic "insert trigger"
>key generated using the Local Interbase Server and WISQL.
>I've tried a "before insert" trigger using a key value
>stored in another table, using an update query to increment the value
>and a subquery to extract the new value and put it into the NEW. value
>of the key. However, LIBS gives an error message and tells me that
>a subquery cannot be used in this context -- which I assume means that
>a subquery cannot be used in a trigger statement. However, I thought
>that "singelton" queries were okay -- what gives? The query looks like:
>   NEW.key_column = (select key_value from autokeys
>                     where (key_id = "key_column"));
>where autokeys is (key_id char(25), key_value smallint).
>So then I tried the Interbase way, using a generator, but found that
>generators do not respect transactions. So if a user attempts to insert
>an incorrect row, the generator is tripped in the "before insert", the
>insert fails, but the generator does not revert to the prior value.
>This is very problematic for systems that need to generate sequential
>numbers. Does anyone know a way around this? (Besides removing all
>constraints from the row so that an insert never fails!)
>Any other methods that work?

How about:

  CREATE TRIGGER AUTOINCREMENT FOR MYTABLE
  BEFORE INSERT AS
  DECLARE VARIABLE new_key INTEGER;
  BEGIN
    UPDATE AUTOKEYS
      SET KEY_VALUE = KEY_VALUE + 1
      WHERE (KEY_ID = "A");
    SELECT KEY_VALUE
      FROM AUTOKEYS
      WHERE KEY_ID = "A"
      INTO :new_key;
    new.my_key_column = new_key;
  END ^

This is the same answer I posted for your message in the BDEVTOOLS forum on
CompuServe, too  ;-)

**************************************************************************
Steve Koterski
Local InterBase Server Technical Support
Borland International, Inc.



Mon, 16 Mar 1998 03:00:00 GMT  
 LIBS - Triggers and Generators

: >I'm having a bit of a problem getting an automatic "insert trigger"
: >key generated using the Local Interbase Server and WISQL.
: >I've tried a "before insert" trigger using a key value
: >stored in another table, using an update query to increment the value
: >and a subquery to extract the new value and put it into the NEW. value
: >of the key. However, LIBS gives an error message and tells me that
: >a subquery cannot be used in this context -- which I assume means that
: >a subquery cannot be used in a trigger statement. However, I thought
: >that "singelton" queries were okay -- what gives? The query looks like:
: >   NEW.key_column = (select key_value from autokeys
: >                     where (key_id = "key_column"));
: >where autokeys is (key_id char(25), key_value smallint).

: >So then I tried the Interbase way, using a generator, but found that
: >generators do not respect transactions. So if a user attempts to insert
: >an incorrect row, the generator is tripped in the "before insert", the
: >insert fails, but the generator does not revert to the prior value.
: >This is very problematic for systems that need to generate sequential
: >numbers. Does anyone know a way around this? (Besides removing all
: >constraints from the row so that an insert never fails!)

: >Any other methods that work?

: How about:

:   CREATE TRIGGER AUTOINCREMENT FOR MYTABLE
:   BEFORE INSERT AS
:   DECLARE VARIABLE new_key INTEGER;
:   BEGIN
:     UPDATE AUTOKEYS
:       SET KEY_VALUE = KEY_VALUE + 1
:       WHERE (KEY_ID = "A");
:     SELECT KEY_VALUE
:       FROM AUTOKEYS
:       WHERE KEY_ID = "A"
:       INTO :new_key;
:     new.my_key_column = new_key;
:   END ^

: This is the same answer I posted for your message in the BDEVTOOLS forum on
: CompuServe, too  ;-)

Steve strikes again! Yeah, after you posted the CIS message I realized that my
solution had worked because of the use of a parameter to receive the select
results... Thanks again!

: **************************************************************************
: Steve Koterski
: Local InterBase Server Technical Support
: Borland International, Inc.

Erik Pearson
UC Berkeley



Thu, 19 Mar 1998 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Firing Interbase Generator/Trigger in D4 C/S

2. Interbase Triggers and Generators

3. Math libs .....

4. FPC & C-LIBs with LINUX

5. using c-libs and pascal for dosapplication

6. Speeding up LIBS?

7. BDE 2.50 Config for LIBS 4.0?

8. LIBS and the substring operator

9. Req: Using C libs in TP6

10. gif libs?

11. graph-libs for head mounted displays

12. Modem/Comm libs

 

 
Powered by phpBB® Forum Software