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

update only if different [CORE3684] #4033

Open
firebird-automations opened this issue Dec 2, 2011 · 1 comment
Open

update only if different [CORE3684] #4033

firebird-automations opened this issue Dec 2, 2011 · 1 comment

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Philip Williams (unordained)

Allow update and insert-or-update statements to have a flag for "no trivial changes", if the update would have no effect except locking the row. Something like "update people set name = 'Bob' where id = 5 ONLY IF DIFFERENT". This is useful for migration / data-loading scenarios where you're running large import datasets, but you just want the data to end up a certain way, you don't want record locks / versions on rows that didn't really need to be touched. It'd be entirely optional, only for situations where you know you don't want do-nothing updates to trigger on-update triggers or place locks, and you're okay with it. To accomplish this currently, a simple:

update or insert into people (name, id) values ('Bob', 5);

becomes

if (not exists(select id from people where id = 5)) then insert into people (name, id) values ('Bob', 5)
else update people set name = 'Bob' where id = 5 and name is distinct from 'Bob';
-- not so bad for one field, but much more annoying for high-column-count tables

I know it's a very niche feature.

@firebird-automations
Copy link
Collaborator Author

Commented by: Philip Williams (unordained)

Also equivalent to:

create exception e_trivial_change 'Nothing to be done';

create trigger trg_people_trivial on bob before update as
begin
if (http://new.name is not distinct from http://old.name and http://new.id is not distinct from http://old.id) then
exception e_trivial_change;
end

begin
update or insert into people (name, id) values ('Bob', 5);
when exception e_trivial_change do begin end
end

... again, annoying, and you have to deal with the position of the trigger relative to any others. And this one would *always* happen, which is the opposite of the default I'd want. (I'd have to setup a rdb$set_context to indicate my desire to have the exception thrown, and my readiness to ignore it. GUI code would probably want to just forge ahead with the normal consequences of update-or-insert.)

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

No branches or pull requests

1 participant