Hello Thomas,

I ran your version of the script (in Flamerobin) and got the following
error:

Starting transaction...
Preparing query: CREATE TRIGGER Source_Id_Upd FOR PHYSICAL_COPY
BEFORE INSERT OR UPDATE
AS BEGIN
select source_id from copy where id = :NEW.COPY_ID into :NEW.source_id
Error: *** IBPP::SQLException ***
Context: Statement::Prepare( CREATE TRIGGER Source_Id_Upd FOR PHYSICAL_COPY
BEFORE INSERT OR UPDATE
AS BEGIN
select source_id from copy where id = :NEW.COPY_ID into :NEW.source_id )
Message: isc_dsql_prepare failed

SQL Message : -104
Invalid token

Engine Code : 335544569
Engine Message :
Dynamic SQL Error
SQL error code = -104
Token unknown - line 4, column 43
.

Any thoughts?

On Tue, Dec 21, 2010 at 12:03 PM, Thomas Steinmaurer <ts@...>wrote:

>
>
> Hello,
>
> > I am trying to denormalize two tables by copying SOURCE_ID column from
> one
> > table to another. I am creating a trigger which executes before an insert
> > and calculates the value of one of the columns for the inserted row. Why
> do
> > I get a syntax error when running the following?
> >
> > CREATE TRIGGER Source_Id_Upd FOR PHYSICAL_COPY
> > BEFORE INSERT OR UPDATE
> > AS BEGIN
> > NEW.source_id = (select source_id from copy where id = NEW.COPY_ID);
> > END;
> >
> > I know I could use computed columns, but I need that column to be
> indexed.
> > Are there any other ways to do this?
>
> While your provided syntax is newly supported in Firebird 2.5, in
> previous versions it should be:
>
>
> CREATE TRIGGER Source_Id_Upd FOR PHYSICAL_COPY
> BEFORE INSERT OR UPDATE
> AS BEGIN
> select source_id from copy where id = :NEW.COPY_ID into :NEW.source_id;
> END;
>
> This all without knowing your exact error message. ;-)
>
> --
> With regards,
>
> Thomas Steinmaurer
> Upscene Productions
> http://www.upscene.com
> http://blog.upscene.com/thomas/
>
> Download LogManager Series, FB TraceManager today!
> Continuous Database Monitoring Solutions supporting
> Firebird, InterBase, Advantage Database, MS SQL Server
> and NexusDB!
>
>


[Non-text portions of this message have been removed]