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?
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.
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)
