For views that are not automatically updatable, the trigger system can be used to make them updatable. The view seller_account_information, which shows the information about the seller account, is not automatically updatable, as shown next:

CREATE OR REPLACE VIEW seller_account_info AS SELECT account.account_id, first_name, last_name, email, password, seller_account_id, total_rank, number_of_advertisement, street_name, street_number, zip_code , city
FROM account INNER JOIN
seller_account ON (account.account_id = seller_account.account_id);

To verify that the view is not updatable:

car_portal=# SELECT is_insertable_into FROM information_schema.tables WHERE table_name = 'seller_account_info';
 is_insertable_into 
--------------------
 NO
(1 row)

The following trigger function assumes that account_id and seller_account_id are always generated using the default values, which are the sequences generated automatically when creating a serial data type. This is often a good approach and relieves the developer of checking the table for a unique constraint before inserting new rows, and it keeps primary key values without big gaps. Furthermore, the trigger function assumes that primary keys cannot be changed for the same reason. Changing primary keys might also cause problems when the default foreign keys options, cascade delete and cascade update, are not used.

Finally, note that the trigger functions return NEW for the INSERT and UPDATE operations, OLD for the DELETE operation, and NULL in the case of an exception. Returning the proper value is important to detect the number of rows that are affected by the operation. It is also very important to return the proper value. Using the RETURNING keyword, as shown in the following function, is used to assign the value for NEW.account_id and NEW.seller_account_id. Note that if the IDs are not assigned properly, this might lead to issues and hard to trace problems for object-relational mappers such as Hibernate: 

CREATE OR REPLACE FUNCTION seller_account_info_update () RETURNS TRIGGER AS $$
DECLARE
    acc_id INT;
    seller_acc_id INT;
BEGIN
    IF (TG_OP = 'INSERT') THEN
        WITH inserted_account AS (
            INSERT INTO car_portal_app.account (account_id, first_name, last_name, password, email) VALUES (DEFAULT, NEW.first_name, NEW.last_name, NEW.password, NEW.email) RETURNING account_id
      ), inserted_seller_account AS (
      INSERT INTO car_portal_app.seller_account(seller_account_id, account_id, total_rank, number_of_advertisement, street_name, street_number, zip_code, city)
      SELECT nextval('car_portal_app.seller_account_seller_account_id_seq'::regclass), account_id, NEW.total_rank, NEW.number_of_advertisement, NEW.street_name, NEW.street_number, NEW.zip_code, NEW.city FROM inserted_account RETURNING account_id, seller_account_id) 
      SELECT account_id, seller_account_id INTO acc_id, seller_acc_id FROM inserted_seller_account;
    NEW.account_id = acc_id;
    NEW.seller_account_id = seller_acc_id;
    RETURN NEW;
  ELSIF (TG_OP = 'UPDATE' AND OLD.account_id = NEW.account_id AND OLD.seller_account_id = NEW.seller_account_id) THEN
    UPDATE car_portal_app.account SET first_name = new.first_name, last_name = new.last_name, password= new.password, email = new.email WHERE account_id = new.account_id;
    UPDATE car_portal_app.seller_account SET total_rank = NEW.total_rank, number_of_advertisement= NEW.number_of_advertisement, street_name= NEW.street_name, street_number = NEW.street_number, zip_code = NEW.zip_code, city = NEW.city WHERE seller_account_id = NEW.seller_account_id;
    RETURN NEW;
  ELSIF (TG_OP = 'DELETE') THEN
    DELETE FROM car_portal_app.seller_account WHERE seller_account_id = OLD.seller_account_id;
    DELETE FROM car_portal_app.account WHERE account_id = OLD.account_id;
    RETURN OLD;
  ELSE
    RAISE EXCEPTION 'An error occurred for % operation', TG_OP;
    RETURN NULL;
  END IF;
END;
$$ LANGUAGE plpgsql;

To run and test the trigger function, let's execute the following SQL statements:

CREATE TRIGGER seller_account_info_trg INSTEAD OF INSERT OR UPDATE OR DELETE ON car_portal_app.seller_account_info FOR EACH ROW EXECUTE PROCEDURE seller_account_info_update ();

To test the INSERT on the view, we can run the following code:

car_portal=# INSERT INTO car_portal_app.seller_account_info (first_name,last_name, password, email, total_rank, number_of_advertisement, street_name, street_number, zip_code, city) VALUES ('test_first_name', 'test_last_name', 'test_password', '[email protected]', NULL, 0, 'test_street_name', 'test_street_number', 'test_zip_code','test_city') RETURNING account_id, seller_account_id;
 account_id | seller_account_id
------------+-------------------
        482 | 147
(1 row)

Notice the return value; the primary keys are returned correctly to the user. To test DELETE and UPDATE, we simply run the following snippet:

car_portal=# UPDATE car_portal_app.seller_account_info set email = '[email protected]' WHERE seller_account_id=147 RETURNING seller_account_id;
 seller_account_id
-------------------
               147
(1 row)

UPDATE 1
car_portal=# DELETE FROM car_portal_app.seller_account_info WHERE seller_account_id=147;
DELETE 1

Finally if we tried to delete all seller accounts , this will fail due to referential integrity constraint as follows:

car_portal=# DELETE FROM car_portal_app.seller_account_info;
ERROR: update or delete on table "seller_account" violates foreign key constraint "advertisement_seller_account_id_fkey" on table "advertisement"
DETAIL: Key (seller_account_id)=(57) is still referenced from table "advertisement".
CONTEXT: SQL statement "DELETE FROM car_portal_app.seller_account WHERE seller_account_id = OLD.seller_account_id"
PL/pgSQL function seller_account_info_update() line 21 at SQL statement
car_portal=#