Last active
September 25, 2016 10:09
-
-
Save cjmamo/e4b3af389f7a5ba031f7813716c0c3de to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| CREATE TABLE PERSON ( | |
| id bigserial primary key, | |
| first_name varchar(255) not null, | |
| last_name varchar(255) not null | |
| ); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| ALTER TABLE PERSON ADD surname varchar(255); | |
| UPDATE PERSON SET surname = last_name; | |
| ALTER TABLE PERSON ALTER COLUMN surname set not null; | |
| ... |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| ... | |
| CREATE OR REPLACE FUNCTION insert_update_person() | |
| RETURNS trigger AS | |
| $$ | |
| BEGIN | |
| IF (TG_OP = 'INSERT') THEN | |
| -- if the record to be inserted contains last_name then that means app v1 is inserting so the value | |
| -- must be copied to surname in order for app v2 to see the value | |
| IF NEW.last_name IS NOT NULL THEN | |
| NEW.surname = NEW.last_name; | |
| -- if the record to be inserted contains surname then that means app v2 is inserting so the value | |
| -- must be copied to last_name in order for app v1 to see the value | |
| ELSIF NEW.surname IS NOT NULL THEN | |
| NEW.last_name = NEW.surname; | |
| END IF; | |
| ELSE | |
| -- if the record to be updated contains last_name that is different from the updating record then | |
| -- that means app v1 is updating the record so copy the updated last_name value to surname allowing app v2 | |
| -- to see the updated value | |
| IF OLD.last_name IS DISTINCT FROM NEW.last_name THEN | |
| NEW.surname = NEW.last_name; | |
| -- if the record to be updated contains surname that is different from the updating record then | |
| -- that means app v2 is updating the record so copy the updated surname value to last_name allowing app v1 | |
| -- to see the updated value | |
| ELSIF OLD.surname IS DISTINCT FROM NEW.surname THEN | |
| NEW.last_name = NEW.surname; | |
| END IF; | |
| END IF; | |
| RETURN NEW; | |
| END | |
| $$ LANGUAGE plpgsql; | |
| CREATE TRIGGER insert_update_person | |
| BEFORE INSERT OR UPDATE ON PERSON | |
| FOR EACH ROW | |
| EXECUTE PROCEDURE insert_update_person(); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment