Upon prepare, the optimizer determines the best way to process a request. Giving a statement like:

SELECT parcel
FROM :place
WHERE ID = :ID

and then try to prepare, is like telling a postman to prepare everything needed for collecting a parcel. Not knowing where to collect the parcel from, he does not know whether he has to put on his shoes, fill gas into his car or order a plane ticket! In a similar way, there is no way for the optimizer to prepare anything when the table name is unknown.

Does this mean that your problem is unsolvable if you don't know the table name? No, but it has to be done dynamically. Try something like:

CREATE TRIGGER DELETE_OBJECT FOR OBJECTS
ACTIVE AFTER DELETE POSITION 0
AS
DECLARE VARIABLE LOCALENAME VARCHAR(32);
DECLARE VARIABLE MYSTATEMENT VARCHAR(132);
BEGIN
FOR
SELECT rdb$relation_name
FROM rdb$relations
WHERE rdb$view_blr is null AND (rdb$system_flag is null or
rdb$system_flag = 0) AND rdb$relation_name LIKE '__-__'
INTO :LOCALENAME
DO
BEGIN
MYSTATEMENT = 'DELETE FROM ' || :LOCALENAME || ' WHERE ID IN (SELECT OP.ID FROM OBJECTPROPERTIES OP WHERE OP.OBJECT = OLD.ID)';
EXECUTE STATEMENT :MYSTATMENT;
END
DELETE FROM OBJECTPROPERTIES OP WHERE OP.OBJECT = OLD.ID;
END^

Now, I never use EXECUTE STATEMENT myself (I've never needed it and, at least when it was new in Firebird 1.5, people were advised to use it with caution) and the code may be incorrect (e.g. I might have forgotten or added a semicolon). So test it thoroughly and read the documentation.

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Zoltán Török
Sent: 25. mars 2009 18:28
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Using declared variable as table name fails

Hi,

I need to create a complex trigger after a delete statement.
The trigger looks like this:

SET TERM ^ ;

CREATE TRIGGER DELETE_OBJECT FOR OBJECTS
ACTIVE AFTER DELETE POSITION 0
AS
DECLARE VARIABLE LOCALENAME VARCHAR(32);
BEGIN

FOR
SELECT rdb$relation_name
FROM rdb$relations
WHERE rdb$view_blr is null AND (rdb$system_flag is null or
rdb$system_flag = 0) AND rdb$relation_name LIKE '__-__'
INTO :LOCALENAME
DO
BEGIN
DELETE FROM :LOCALENAME WHERE ID IN (SELECT OP.ID FROM
OBJECTPROPERTIES OP WHERE OP.OBJECT = OLD.ID );
END

DELETE FROM OBJECTPROPERTIES OP WHERE OP.OBJECT = OLD.ID;
END^

SET TERM ; ^


It says that 'Token unknown' on :LOCALENAME in the DELETE statement.

Using declared variables as table name is not allowed?

Regs,
Zolee