CODEWITHSUNDEEP
firebird
Mazmart
Mazmart

Reputation: 2813

firebird insert into returning into another insert

I'm using Firebird as DB and I need to do this:

INSERT INTO TG (ID, UID, GID) 
    SELECT (INSERT INTO TBO VALUES (GEN_ID('o',1)) RETURNING ID), UID, 10 
    FROM TBL l 
    WHERE l.is=1

the part with select is OK when I use:

SELECT (GEN_ID('o',1)), UID, 10 
    FROM TBL l 
    WHERE l.is=1

but I need the ID in other table for dependency first.

I know about something called procedures but I have no idea how to use them. Is there an option to do this using SQL?

Upvotes: 1

Views: 1847

Answers (2)

Marcodor
Marcodor

Reputation: 5771

Take a look at the EXECUTE BLOCK statement. It allows you to execute multiple statements in one "batch" or write complex logic if you cannot embed it in one SQL query.

Inside EXECUTE BLOCK you can write multiple commands using PSQL.

EB allows input parameters, output parameters (yes you can use it as a table), local variables, if statement, while, for select etc. A very powerful tool.

Just prepare your block and execute it like simple SQL query.

Upvotes: 2

Duilio Juan Isola
Duilio Juan Isola

Reputation: 237

A simpler aproach would be to use triggers. In this example it would be a before insert trigger. Something like this:

CREATE TRIGGER TG_BI0 FOR TABLE TG ACTIVE BEFORE INSERT 0
AS
BEGIN
    /* Before insert create a new record in TBO */
    INSERT INTO TBO (ID) VALUES (NEW.ID);
END

After having this trigger you shloud only insert records in TG.

INSERT INTO TG (ID, UID, GID)
VALUES (GEN_ID('o',1), 'SOME_UUID', 10)

Upvotes: 0

Related Questions