Geny

1. S.cost - на верхнем уровне не виден
2. Условие provide_service_detail.providedserviceid = P.providedserviceid не отработает
3. Нужно переписать как-то так

SELECT S.cost
  FROM (select * from ((SELECT shoppingserviceid, cost, bsid FROM service JOIN shopping_service ON service.serviceid = shopping_service.serviceid) S JOIN
         provided_service ON provided_service.shoppingserviceid = S.shoppingserviceid)) P
  JOIN provide_service_detail
    ON provide_service_detail.providedserviceid = P.providedserviceid

А текст триггера есть?

В рамках этой-же транзакции изменения будут видны, но они не будут видны для других транзакций до COMMIT.

create table ttt (code varchar2(10));
insert into ttt values ('10');
select * from ttt;
-->>10
update ttt set code = '20';
select * from ttt;
-->>>20
drop table ttt;