Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Access field values of a cursor by field name [CORE4924] #5215

Open
firebird-issue-importer opened this issue Sep 4, 2015 · 7 comments
Open

Access field values of a cursor by field name [CORE4924] #5215

firebird-issue-importer opened this issue Sep 4, 2015 · 7 comments

Comments

@firebird-issue-importer
Copy link

@firebird-issue-importer firebird-issue-importer commented Sep 4, 2015

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Sep 4, 2015

Commented by: @dyemanov

Please explain what you are trying to achieve with this feature. Maybe other solutions could be possible.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Sep 4, 2015

Commented by: @dyemanov

Seems to be a duplicate for CORE2147, BTW.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Sep 4, 2015

Modified by: @dyemanov

Link: This issue duplicates CORE2147 [ CORE2147 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Sep 5, 2015

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.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Sep 5, 2015

Commented by: @asfernandes

You'd better use an external trigger> you can dynamically inspect metadata and data inside them.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Sep 5, 2015

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.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Sep 7, 2015

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?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant