Access field values of a cursor by field name [CORE4924] #5215
Comments
|
Commented by: @dyemanov Please explain what you are trying to achieve with this feature. Maybe other solutions could be possible. |
|
Commented by: Jonathan Neve (jonneve) You're right, there may be a better way to acheive the same thing. I'm simply trying to implement a replication / audit logging trigger that will log the old and new values of every changed field to a table in the database. I have a simple implementation of this that works by just explicitly comparing the old and new values of every field. It works, but it creates a dependency on every single field, which I don't like. It also means that if the user adds a field, they have to remember to recreate the triggers so that the new field will be taken into account. I have another implementation that works using an Execute statement instead, so as to list the Fields from the system tables and get the old and new values using a select statement in a before trigger and an after trigger. It works, but kt's very slow. So hat I would like would be a way to use the OLD and NEW variables without knowing the field names, or some other way of getting the list of old and new values. |
|
Commented by: @asfernandes You'd better use an external trigger> you can dynamically inspect metadata and data inside them. |
|
Commented by: @dyemanov I'd say that if you need replication, then use (or vote for, or sponsor) replication. If you need record-level audit, then request extensions to the trace API. Any homebrew solution is likely to be worse than the one built-in into the engine. |
|
Commented by: Jonathan Neve (jonneve) @adriano: Yes, I had thought of that, and I might give it a try, though I have a hard time figuring out how to use the new UDR system. I would prefer an all-SQL solution, as it's easier to install seamlessly without requiring extra permissions and so on, but it could be a solution. @dmitry: I've already got a replication system, I don't need built-in replication in Firebird. I'm just trying to improve my existing system because I don't like having a dependency on every field in the DB. Apart from that, it does work fine. I would be willing to sponsor development of this feature, or any other alternative that would allow me to easily get a hold of the old and new values of every field. How hard would it be to implement something like that? |
Submitted by: Jonathan Neve (jonneve)
Duplicates CORE2147
Votes: 1
It would be very useful to be able to access the list of field names of a cursor and the field values by field name. I am specifically interested in using this functionality for the new/old cursors from within a trigger (not sure if they are technically cursors or not...). The idea would be to make it possible to access the field values in an abstract way, without knowing the field names, for example, in order to get a list of fields that changed. Something like this:
create trigger tr_mytable for mytable after update
as
declare variable fname varchar(31);
declare variable ftype integer;
begin
for select rdb$field_name, rdb$field_type from rdb$cursor_fields(:new) into :fname, :ftype do begin
if (rdb$cursor_field_value(:old) is distinct from rdb$cursor_field_value(:new)) then
begin
/* the field has changed*/
end
end
end
The text was updated successfully, but these errors were encountered: