21.02

---------------------------------------------------------------------------
----------------------------Транзакции-------------------------------------
---------------------------------------------------------------------------
-- Задача 6-1.
-- Транзакция, която има за цел да изтрие отдел „Мениджмънт“ - 90
-- като преди това прехвърли всички служители от него в отдел „Администрация“. -10

select * from DEPARTMENTS

begin tran
    --прехвърли всички служители от него в отдел „Администрация“
    update EMPLOYEES
    set DEPARTMENT_ID = 10
    where DEPARTMENT_ID = 90

    --изтрие отдел „Мениджмънт“
    delete from DEPARTMENTS
    where DEPARTMENT_ID = 90
commit tran

select * from DEPARTMENTS

--Задача 6-2.
--Транзакция, която изтрива продукт 1726 -първо го изтрива от всички поръчки
--после от таблицата с продукти, и накрая отхвърля направените промени.

begin tran
    delete from ORDER_ITEMS
    where PRODUCT_ID = 1726

    delete from PRODUCTS
    where PRODUCT_ID = 1726
rollback tran

select * from ORDER_ITEMS

---------------------------------------------------------------------------
-----------------------------Процедури-------------------------------------
---------------------------------------------------------------------------
--Пример 7-2.
--Да се създаде процедура, която за подадена като входен параметър ORDER_ID
--извежда имена на служител, който я е обработил, както и общата й стойност.

create proc employees_orders @ORDER INT
AS
SELECT FNAME, LNAME,
       O.ORDER_ID,
       SUM(OI.QUANTITY* OI.UNIT_PRICE) AS TOTAL
FROM EMPLOYEES E JOIN ORDERS O ON E.EMPLOYEE_ID=O.EMPLOYEE_ID
                 JOIN ORDER_ITEMS OI ON OI.ORDER_ID=O.ORDER_ID
WHERE O.ORDER_ID = 2354
GROUP BY FNAME, LNAME, O.ORDER_ID

EXEC employees_orders 100
---------------------------------------------------------------------------
-----------------------------Функции---------------------------------------
---------------------------------------------------------------------------
--Пример 7-5.
--Да се създаде функция, връщаща като резултат (резултатен набор)
--служителите с техните длъжности.

CREATE FUNCTION EMPLOYEES_JOBS() RETURNS TABLE
AS
RETURN
SELECT FNAME, LNAME, JOB_TITLE
FROM EMPLOYEES E JOIN JOBS J
ON E.JOB_ID=J.JOB_ID

SELECT * FROM DBO.EMPLOYEES_JOBS()
ORDER BY FNAME, LNAME

---------------------------------------------------------------------------
-----------------------------Тригери---------------------------------------
---------------------------------------------------------------------------
--Задача 9-1.
--Да се създаде тригер, който при всяка промяна на фамилия на клиент
--записва ред в нова таблица CUSTOMERS_HIST с атрибути:
--• идентификатор на клиент;
--• стара фамилия;
--• нова фамилия.

CREATE TABLE CUSTOMERS_HISTORY
(
CUSTOMER_ID INT,
OLD_NAME VARCHAR(20),
NEW_NAME VARCHAR(20)
)

create TRIGGER TGR_CUSTOMERS_HIST
ON CUSTOMERS
FOR UPDATE
AS
    IF UPDATE(LNAME)
    BEGIN
    INSERT INTO CUSTOMERS_HISTORY (CUSTOMER_ID, OLD_NAME, NEW_NAME )

    SELECT I.CUSTOMER_ID, D.LNAME, I.LNAME
    FROM inserted I , deleted D
    WHERE I.CUSTOMER_ID=D.CUSTOMER_ID
    END

UPDATE CUSTOMERS
SET LNAME = LNAME + '-' + 'КОЛЕВА'
WHERE CUSTOMER_ID IN (103, 111, 114)

SELECT * FROM CUSTOMERS

SELECT * FROM CUSTOMERS_HISTORY