At 05:01 PM 19-01-02 +0800, Andrew (CSSWA) wrote:

>----------
>and the faulty procedure "log_changes":
>
>CREATE PROCEDURE "SP_LOG_CHANGES"
>(
> "VAR_TABLENAME" VARCHAR(25) CHARACTER SET ISO8859_1,
> "VAR_KEY" NUMERIC(18, 0),
> "VAR_CHANGETYPE" CHAR(1) CHARACTER SET ISO8859_1
>)
>AS
>declare variable var_logging char(1);
>begin
> select f_logging
> from t_table_options
> where f_tablename = :var_tablename
> into :var_logging;
> if (:var_logging <> 'Y') then exit; /* <--- why not exiting here? */
> insert into t_changes_log
> (f_tablename, f_recordkey, f_type)
> values
> (:var_tablename, :var_key, :var_changetype);
>end
> ^

First, take the quotes off procedure arguments and the variable names and see whether that makes a difference to your problem.

Then, try a simpler procedure, viz.

CREATE PROCEDURE "SP_LOG_CHANGES"
(
"VAR_TABLENAME" VARCHAR(25) CHARACTER SET ISO8859_1,
"VAR_KEY" NUMERIC(18, 0),
"VAR_CHANGETYPE" CHAR(1) CHARACTER SET ISO8859_1
)
AS
declare variable var_logging char(1);
begin
if ( EXISTS (
select f_logging
from t_table_options
where f_tablename = :var_tablename
AND f_logging = 'Y' ) ) then
insert into t_changes_log
(f_tablename, f_recordkey, f_type)
values
(:var_tablename, :var_key, :var_changetype);
end
^
(and note that this SQL won't work if you have used quoted identifiers in your DDL, as demonstrated in your posting).

Helen


All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org
_______________________________________________________