sqlh20.1

-- Oefening 1 | CURSOR

DECLARE CustCursor CURSOR
FOR
SELECT cust_id, cust_name FROM Customers

DECLARE @cust_id CHAR(10), @cust_name CHAR(50)

OPEN CustCursor

FETCH NEXT FROM CustCursor INTO @cust_id, @cust_name

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT @cust_id + ' | ' + @cust_name

FETCH NEXT FROM CustCursor INTO @cust_id, @cust_name

END

CLOSE CustCursor
DEALLOCATE DateCursor

-- Oefening 2 | FUNCTION

CREATE FUNCTION IntervalMonth (@input datetime)

RETURNS int

AS BEGIN

    DECLARE @Output int

    SET @Output =  DATEDIFF(MM,@input,GETDATE()) 

    RETURN @Output

END

SELECT order_date, dbo.IntervalMonth(order_date)
FROM Orders;

-- Oefening 3 | TRIGGER

CREATE TRIGGER customer_state
ON Customers
FOR INSERT, UPDATE
AS
UPDATE Customers
SET cust_state = Upper(inserted.cust_state)
FROM inserted
WHERE Customers.cust_id = inserted.cust_id;

INSERT INTO Customers(cust_id, cust_name,  cust_state)
VALUES('1000000009', 'Village Toys', 'ny');

SELECT * 
FROM Customers

-- Oefening 4 | TRANSACTION

BEGIN TRY
    BEGIN TRAN

		DECLARE @cust_id CHAR(10)

		SET @cust_id = '1000000004'

		DELETE FROM OrderItems WHERE order_num IN (SELECT order_num FROM orders WHERE cust_id = @cust_id);		
		DELETE FROM Customers WHERE cust_id = @cust_id;
		DELETE FROM Orders WHERE order_num IN (SELECT order_num FROM orders WHERE cust_id = @cust_id);
		
    COMMIT TRAN 
END TRY

BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN 
		PRINT ERROR_MESSAGE()
END CATCH