| Subject | Re: [firebird-support] How do I use a variable as a fieldname in a trigger |
|---|---|
| Author | Helen Borrie |
| Post date | 2010-09-17T23:53:37Z |
At 09:04 AM 18/09/2010, cornievs wrote:
Probably the first place is to tell you that you cannot use parameters or variables to refer to database object identifiers. What you *can* do is incorporate a string variable into the text of EXECUTE STATEMENT. You will need to research this...but you will be able to concatenate the VALUE of the local variable in the string that you compose for EXECUTE STATEMENT.
Now to the second misapprehension. The colon prefix on a variable name has one and only one purpose. In a dynamic SQL statement *only*, it makes the VALUE of a local variable available...example:
update xyz
set aColumn = :MyLocalVariable
or, another (in a BEFORE trigger):
NEW.aColumn = :MyLocalVariable
A DSQL statement that tries to refer to :MyLocalVariable as though it were a column identifier will always throw an exception.
The third problem is that you are trying to refer to the values of the columns directly in a trigger. That doesn't work. In a trigger, you can only read/change the values via the NEW.* and OLD.* context variables.
The fourth problem seems to be that you are expecting the current record to be updatable from an AFTER trigger. It is not. The current record is read-only in an AFTER trigger.
In your AFTER UPDATE trigger you could theoretically open a cursor to the same table that the current record belongs to and target some rows *other than* the one that the trigger is working on. It would have to be done with extreme care and attention to the logic, though. A trigger will fire, regardless of whether the operation came from a client or from another trigger or a SP...so you will be at high risk of creating an infinite loop unless you are totally aware of the effects and provide an appropriate loop-breaking mechanism.
Note, also, whenever you access a local variable in PSQL (which includes the code where you would be composing a string for EXECUTE STATEMENT) you *do not* apply the colon prefix, e.g.
SqlStmt = SqlStmt || ' SET ' || NewUnitFieldName || ' = ' || MyLocalVariable;
./heLen
>I need to write a trigger that updates a field in another table on updating/inserting, but i can only determine which field when the inserting/updating is done. I try the following, but gets an error on the : characters.You are making a lot of mistakes here...where to start? ;-)
Probably the first place is to tell you that you cannot use parameters or variables to refer to database object identifiers. What you *can* do is incorporate a string variable into the text of EXECUTE STATEMENT. You will need to research this...but you will be able to concatenate the VALUE of the local variable in the string that you compose for EXECUTE STATEMENT.
Now to the second misapprehension. The colon prefix on a variable name has one and only one purpose. In a dynamic SQL statement *only*, it makes the VALUE of a local variable available...example:
update xyz
set aColumn = :MyLocalVariable
or, another (in a BEFORE trigger):
NEW.aColumn = :MyLocalVariable
A DSQL statement that tries to refer to :MyLocalVariable as though it were a column identifier will always throw an exception.
The third problem is that you are trying to refer to the values of the columns directly in a trigger. That doesn't work. In a trigger, you can only read/change the values via the NEW.* and OLD.* context variables.
The fourth problem seems to be that you are expecting the current record to be updatable from an AFTER trigger. It is not. The current record is read-only in an AFTER trigger.
In your AFTER UPDATE trigger you could theoretically open a cursor to the same table that the current record belongs to and target some rows *other than* the one that the trigger is working on. It would have to be done with extreme care and attention to the logic, though. A trigger will fire, regardless of whether the operation came from a client or from another trigger or a SP...so you will be at high risk of creating an infinite loop unless you are totally aware of the effects and provide an appropriate loop-breaking mechanism.
Note, also, whenever you access a local variable in PSQL (which includes the code where you would be composing a string for EXECUTE STATEMENT) you *do not* apply the colon prefix, e.g.
SqlStmt = SqlStmt || ' SET ' || NewUnitFieldName || ' = ' || MyLocalVariable;
./heLen