This is long, apologies. I've examined this for an hour now and
can't find the reason for the fault.

I have a stored procedure that writes a script out to an external
table for later running in DSQL.

It works fine except for one glitch: a variable within a select seems
to be stuck on the very first value retrieved. It's the var_pkname
variable noted below with <-----. This variable holds the primary
key name for the current table name. As you can see from the sample
output at the very bottom, it correctly finds the pk name of the
first table, but fails to retrieve the pk names of the other 25+
tables.

It's odd because the procedure is walking through all the table
names, and as far as I can tell it should also be walking through the
pk names. I have run the code snippet in DSQL
substituting :var_tablename with various valid table names and the
primary index name always comes back for the correct table.

SELECT RDB$FIELD_NAME
FROM RDB$INDEX_SEGMENTS
WHERE RDB$INDEX_NAME =
(
SELECT RDB$INDEX_NAME
FROM RDB$INDICES
WHERE (RDB$INDEX_NAME STARTING WITH 'RDB$PRIMARY')
AND (RDB$RELATION_NAME = :VAR_TABLENAME)
)

'T_ACTION' -> 'ACT_PK'
'T_ADDRESS' -> 'ADDR_PK'
'T_CODESET' -> 'CODE_PK'

All correctly reported primary keys for those tables. So I know that
bit of SQL works fine. Anyway, here's a chunk of the stored
procedure. If someone can point out why the :var_pkname value isn't
updating as the procedure cycles through the table names, I'd love to
hear it.

ALTER PROCEDURE SP_TEMP_CREATE_TRIGGERS
AS
DECLARE VARIABLE VAR_TABLENAME VARCHAR(30);
DECLARE VARIABLE VAR_SHORTNAME VARCHAR(30);
DECLARE VARIABLE VAR_PKNAME VARCHAR(30);
DECLARE VARIABLE VAR_NEWLINE CHAR(2);
DECLARE VARIABLE VAR_QUOTE CHAR(1);
BEGIN
VAR_NEWLINE = UDF_CHAR(13) || UDF_CHAR(10);
FOR SELECT RDB$RELATION_NAME
FROM RDB$RELATIONS
WHERE RDB$RELATION_NAME STARTING WITH 'T_'
ORDER BY RDB$RELATION_NAME
INTO :VAR_TABLENAME
DO BEGIN
VAR_TABLENAME = UDF_RTRIM(VAR_TABLENAME);
/* SET SHORT TABLE NAME (NO 'T_') */
VAR_SHORTNAME = UDF_RIGHT(VAR_TABLENAME, UDF_STRLEN
(VAR_TABLENAME) - 2);
VAR_SHORTNAME = UDF_RTRIM(VAR_SHORTNAME);
/* SET QUOTE VARIABLE */
VAR_QUOTE = UDF_CHAR(39);
/* GET PRIMARY KEY FIELD NAME */
SELECT RDB$FIELD_NAME
FROM RDB$INDEX_SEGMENTS
WHERE RDB$INDEX_NAME =
(
SELECT RDB$INDEX_NAME
FROM RDB$INDICES
WHERE (RDB$INDEX_NAME STARTING WITH 'RDB$PRIMARY')
AND (RDB$RELATION_NAME = :VAR_TABLENAME)
)
INTO :VAR_PKNAME; <----------*** VARIABLE NOT REFRESHING
VAR_PKNAME = UDF_RTRIM(VAR_PKNAME);

/* WRITE LOGGING TRIGGER - INSERT */
INSERT INTO T_EXT_TRIGGER_SCRIPTS (LINEDATA, NEWLINE)
VALUES ('SET TERM^;', :VAR_NEWLINE);
INSERT INTO T_EXT_TRIGGER_SCRIPTS (LINEDATA, NEWLINE)
VALUES
(
'CREATE TRIGGER TR_' || :VAR_SHORTNAME || '_LOG_AI FOR '
|| :VAR_TABLENAME,
:VAR_NEWLINE
);
INSERT INTO T_EXT_TRIGGER_SCRIPTS (LINEDATA, NEWLINE)
VALUES
(
'ACTIVE AFTER INSERT POSITION 10000',
:VAR_NEWLINE
);

Sample output below. Note, each line is a record in the external
table. Once the procedure is run, I open up the external table with
a text editor and copy-paste the lot into a DSQL window.

SET TERM^;
CREATE TRIGGER TR_ACTION_LOG_AI FOR T_ACTION
ACTIVE AFTER INSERT POSITION 10000
AS
DECLARE VARIABLE VAR_TOPT_PK SMALLINT;
BEGIN
SELECT TOPT_PK
FROM T_TABLE_OPTIONS
WHERE TOPT_TABLE_NAME = 'T_ACTION'
INTO :VAR_TOPT_PK;
EXECUTE PROCEDURE SP_LOG_CHANGES
(VAR_TOPT_PK, NEW.ACT_PK,'I'); <----- 'ACT_PK' IS CORRECT PK NAME FOR
THIS TABLE
END^
SET TERM;^

CREATE TRIGGER TR_ADDRESS_LOG_AI FOR T_ADDRESS
ACTIVE AFTER INSERT POSITION 10000
AS
DECLARE VARIABLE VAR_TOPT_PK SMALLINT;
BEGIN
SELECT TOPT_PK
FROM T_TABLE_OPTIONS
WHERE TOPT_TABLE_NAME = 'T_ADDRESS'
INTO :VAR_TOPT_PK;
EXECUTE PROCEDURE SP_LOG_CHANGES
(VAR_TOPT_PK, NEW.ACT_PK,'I'); <------- STUCK ON 'NEW.ACT_PK', SHOULD
BE 'NEW.ADDR_PK'
END^
SET TERM;^

...ETC.


regards,
Andrew Ferguson