INSERT SELECT in Firebird

Question

I'm new to firebird and I have verious issues. I want to insert various lines into a table selected from another table.

Here's the code:

/*CREATE GENERATOR POS; */
SET GENERATOR POS TO 1;

SET TERM ^;

create trigger BAS_pkassign
   for MATERIAL
active before insert position 66

EXECUTE BLOCK
AS

  declare posid bigint;
  select gen_id(POS, 1)
  from RDB$DATABASE
  into :posid;

BEGIN



END

SET TERM ; ^


INSERT INTO MATERIAL ( /*ID */ LOCATION, POSID, ARTID, ARTIDCONT, QUANTITY )
SELECT  1000, ':posid', 309, BAS_ART.ID, 1
FROM    BAS_ART
WHERE   BAS_ART.ARTCATEGORY LIKE '%MyWord%'

The ID should autoincrement from 66 on. The posid should autoincrement from 1 on.

Actually it is not inserting anything.

I'm using Firebird Maestro and have just opened the SQL Script Editor (which doesnt throw any error message on executing the script).

Can anybody help me?

Thanks!

Additional information:

The trigger should autoincrement the column "ID" - but I dont know how exactly I can change it so it works.. The ':posid' throws an error using it :posid but like this theres no error (I guess its interpretated as a string). But how do I use it right?

I dont get errors when I execute it. The table structure is easy. I have 2 tables: 1.

 Material (
ID (INTEGER),
Location (INTEGER),
POSID (INTEGER),
ARTID (INTEGER),
ARTIDCONT (INTEGER),
QUANTITY (INTEGER),
OTHERCOLUMN (INTEGER)) 

and the 2. other table

BAS_ART (ID (INTEGER), ARTCATEGORY (VARCHAR255))

-> I want to insert all entries from the table BAS_ART which contain "MyWord" in the column ARTCATEGORY into the MATERIAL table.

Answer 1

I don't understand why you need the trigger at all.

This problem:

I want to insert all entries from the table BAS_ART which contain "MyWord" into the MATERIAL table

Can be solved with a single insert ... select statement.

insert into material (id, location, posid, artid, quantity)
select next value for seq_mat_id, 1000, next value for seq_pos, id, 1
from bas_art
where artcategory = 'My Word';

This assumes that there is a second sequence (aka "generator") that is named seq_mat_id that provides the new id for the column material.id