Is there a way to access new and old context variables through
rdb$get_context function?

Thanks again for your help

Puri

--- In firebird-support@yahoogroups.com, "puri_vidal"
<puri_vidal@...> wrote:

>
> I see my previous message is completely unreadable, so I am trying
> again. In the meanwhile, I read in README.execute_statement.txt:
>
> >>>
> For all forms of EXECUTE STATEMENT SQL, the DSQL string can not
> contain any parameters. All variable substitution into the static
> part of the SQL statement should be performed before EXECUTE

STATEMENT

> <<<
>
> So, this seems to be the problem (context variables are variables,
> are not they?). Still looking for a workaround...
>
> This was my previous message:
>
> Hi all!!
>
> I am trying to write a trigger to log changes performed in a table
> into a txt file, which will be imported by another system (not to
> firebird, but to a paradox table).
>
> I want to store table name, operation, key field and all the fields
> values. The 'easy' way is to write something like this:
>
> SET TERM ^;
> CREATE TRIGGER MY_TRIGGER
> FOR MY_TABLE
> AFTER INSERT OR UPDATE OR DELETE
> AS
> DECLARE VARIABLE TXT_LINE VARCHAR(1024);
> BEGIN
> IF ( INSERTING ) THEN
> TXT_LINE
>

='MY_TABLE;I;'||NEW.FIELD1||';'||NEW.FIELD1||';'||NEW.FIELD2||';'|| ..

> .. ;
> ELSE
> IF ( UPDATING ) THEN
> TXT_LINE
>

= 'MY_TABLE;U;'||OLD.FIELD1||';'||NEW.FIELD1||';'||NEW.FIELD2||';'|| .

> ... ;
> ELSE
> TXT_LINE
>

= 'MY_TABLE;D;'||OLD.FIELD1||';'||OLD.FIELD1||';'||OLD.FIELD2||';'|| .

> ... ;
> FILE_WRITE( 'MYFILE.TXT', TXT_LINE );
> END;
> SET TERM ;^
>
> The problem is that if I add a new field to my table, I have to
> change the trigger. So I would like to generate this
> sentence 'dinamically'. Something like this:
>
> SET TERM ^;
> CREATE PROCEDURE AUX_WRITE( IN_LINE VARCHAR(1024) )
> AS
> BEGIN
> FILE_WRITE( 'MYFILE.TXT', IN_LINE );
> END^
>
> CREATE TRIGGER MY_TRIGGER
> FOR MY_TABLE
> AFTER INSERT OR UPDATE OR DELETE
> AS
> DECLARE VARIABLE TXT_LINE VARCHAR(1024);
> DECLARE VARIABLE TXT_COMMAND VARCHAR(1500);
> DECLARE VARIABLE MY_CONTEXT CHAR(4);
> DECLARE VARIABLE FIELD_NAME VARCHAR(30);
> BEGIN
> MY_CONTEXT = 'NEW';
> IF ( INSERTING ) THEN
> TXT_LINE = 'MY_TABLE;I;'||NEW.FIELD1;
> ELSE
> BEGIN
> IF ( UPDATING ) THEN
> TXT_LINE = 'MY_TABLE;U;'||OLD.FIELD1;
> ELSE
> BEGIN
> TXT_LINE = 'MY_TABLE;D;'||OLD.FIELD1;
> MY_CONTEXT = 'OLD';
> END
> END
>
> FOR SELECT f.rdb$field_name FROM rdb$relation_fields f
> WHERE f.rdb$relation_name='MY_TABLE'
> ORDER BY f.rdb$field_position INTO :FIELD_NAME DO
> TXT_LINE = :TXT_LINE || ';' || :MY_CONTEXT || '.'

|| :FIELD_NANE;

>
> TXT_COMMAND = 'EXECUTE PROCEDURE AUX_WRITE( ' || TXT_LINE || ' );'
> EXECUTE STATEMENT( TXT_COMMAND );
> END^
> SET TERM ;^
>
> The generated sentence is right, that is, executing it directly

works

> fine, but having it as string and executing through EXECUTE

STATEMENT

> I get an error telling >> 'NEW.FIELD1' IS AN UNKNOWN COLUMN
>
> Probably there is something I am misunderstanding... any

workaround??

>
> Thanks in advance for your help, and sorry for the lenght of my

first

> message...
>
> Puri Vidal
>