use poliklinika
select * from Pacienti

-- Uvažujte DB Poliklinika
--1.) Vytvorte trigger, ktorý po vložení návštevy zníži mesačný príjem pacienta o zaplatený poplatok počas jeho návštevy.
USE Poliklinika
GO

IF OBJECT_ID('pridajPlat', 'TR') IS NOT NULL
DROP TRIGGER pridajPlat

GO    
--AFTER triggre sa vykonaju po akcii INSERT, UPDATE, MERGE, DELETE
--AFTER triggre sa nikdy nevykonaju ak sa porusia obmedzenia,
--preto tento typ triggeru netreba pouzivat na overenie obmedzeni
-- Benefity DML triggeru:
-- https://docs.microsoft.com/en-us/sql/relational-databases/triggers/dml-triggers?view=sql-server-ver15#dml-trigger-benefits
--triggre pouzivaju tabulky inserted a deleted,
--ktore su strukturalne totozne s tabulkou nad ktorou bol trigger vytvoreny
--a obsahuju bud stare alebo nove hodnoty riadkov zmenenych pouzivatelom
CREATE TRIGGER pridajPlat ON Navstevy AFTER INSERT 
AS    
    BEGIN
        UPDATE Pacienti SET 
            Pacienti.mesPrijem = Pacienti.mesPrijem - (SELECT poplatok FROM inserted) 
        WHERE 
            (SELECT idP FROM inserted) = Pacienti.idP;
    END;
GO
--kontrola
INSERT Navstevy VALUES	(23, 6, 5, '2008-05-13',33 );

SELECT * FROM Pacienti
GO

-- Co ak spravime insert s viacerymi riadkami? predosly trigger nezbehne
--vlozime viacero riadkov naraz
INSERT Navstevy VALUES	
(24, 6, 5, '2008-05-13',33 ),
(25, 7, 5, '2008-05-13',33 ),
(26, 4, 5, '2008-05-13',33 );

-- treba pouzit UPDATE JOIN na update skrze viacero tabuliek. Skuste sami prerobit
-- hint na UPDATE JOIN https://www.sqlservertutorial.net/sql-server-basics/sql-server-update-join/
DISABLE TRIGGER pridajPlat ON Navstevy 

IF OBJECT_ID('pridajPlatMulti', 'TR') IS NOT NULL
    DROP TRIGGER pridajPlatMulti

GO    

CREATE TRIGGER pridajPlatMulti ON Navstevy AFTER INSERT 
AS    
    BEGIN
        UPDATE p SET 
            p.mesPrijem = p.mesPrijem - i.poplatok 
		FROM Pacienti p JOIN inserted i ON p.idP = i.idP;
    END;
GO

--skusme opat vlozit viacero riadkov naraz, ALE NAJPRV VYMAZTE STARY TRIGGER pridajPlat
INSERT Navstevy VALUES	
(24, 6, 5, '2008-05-13',33 ),
(25, 7, 5, '2008-05-13',33 ),
(26, 4, 5, '2008-05-13',33 );
GO




--návrat k pôvodným dátam
DELETE FROM Navstevy WHERE idN = 23;
UPDATE Pacienti SET mesPrijem = mesPrijem + 33 WHERE idP = 6;
DELETE FROM Navstevy WHERE idN = 24;
UPDATE Pacienti SET mesPrijem = mesPrijem + 33 WHERE idP = 6;
DELETE FROM Navstevy WHERE idN = 25;
UPDATE Pacienti SET mesPrijem = mesPrijem + 33 WHERE idP = 7;
DELETE FROM Navstevy WHERE idN = 26;
UPDATE Pacienti SET mesPrijem = mesPrijem + 33 WHERE idP = 4;


-- 2.) Vytvorte trigger, ktorý nás bude  informovať o tom čo sa zmenilo v tabuľke pacienti.
-- Ak sa niekto vymaže, tak nech nám zobrazí jeho krstné a idP.
-- Ak sa niekto pridá do tabuľky, tak nech nám zobrazí jeho krstné a idP.
-- Ak sa niekomu zmenia hodnoty, tak nech sa vypíše jeho krstné a idP, a zároveň nech sa vypíše čo, sa zmenilo na čo.
-- SKONTROLUJTE, či všetko funguje.
IF OBJECT_ID ('infoTrigger', 'TR') IS NOT NULL 
	DROP TRIGGER infoTrigger
GO
CREATE TRIGGER infoTrigger ON Pacienti AFTER INSERT, UPDATE, DELETE
AS

DECLARE @rc AS INT;
SET @rc = @@rowcount;
DEClARE @idp INT
DECLARE @krstne VARCHAR(15)

IF @rc = 0
	BEGIN
		PRINT 'Nič sa nezmenilo';
		RETURN;
	END
IF EXISTS(SELECT * FROM inserted)
BEGIN
	IF EXISTS(SELECT * FROM deleted)
		BEGIN			
			DECLARE @idpStare INT = (SELECT idP FROM deleted)			
			DECLARE @krstneStare VARCHAR(15) = (SELECT krstne FROM deleted)		
			DECLARE @mesPrijemStare INT = (SELECT mesPrijem FROM deleted)		
			SET @idp = (SELECT idP FROM inserted)
			SET @krstne = (SELECT krstne FROM inserted)
			DECLARE @mesPrijem INT = (SELECT mesPrijem FROM inserted)
			IF @idpStare != @idp
				BEGIN
					PRINT 'Zmenilo sa idp ' + CAST(@idpStare AS VARCHAR(10)) + ' na idp ' + CAST(@idp AS VARCHAR(10))
				END
			IF @krstneStare != @krstne
				BEGIN
					PRINT 'Zmenilo sa krstne ' + @krstneStare + ' na krstne ' + @krstne
				END
			IF @mesPrijemStare != @mesPrijem
				BEGIN
					PRINT 'Zmenil sa mesPrijem ' + CAST(@mesPrijemStare AS VARCHAR(10)) + ' na mesPrijem ' + CAST(@mesPrijem AS VARCHAR(10))
				END		
		END
	ELSE
		BEGIN
			SET @idp = (SELECT idP FROM inserted)
			SET @krstne = (SELECT krstne FROM inserted)		
			PRINT @krstne + ' bolo vložený/á a pridelene id ' + CAST(@idp AS VARCHAR(10))
		END
END
ELSE
	BEGIN
		SET @idp = (SELECT idP FROM deleted)
		SET @krstne = (SELECT krstne FROM deleted)		
        PRINT 'Bol/a vymazaný/á ' + @krstne + ' s id ' + CAST(@idp AS VARCHAR(10))
	END
GO

UPDATE Pacienti SET mesPrijem = mesPrijem WHERE idP = 15

--3.) Vytvorte trigger, ktorý zakáže mazanie z tabuľky pacienti a informuje nás, že nemôžeme mazať pacientov.
IF OBJECT_ID ('errTrigger', 'TR') IS NOT NULL 
	DROP TRIGGER errTrigger
GO
CREATE TRIGGER errTrigger ON Pacienti INSTEAD OF DELETE
AS
BEGIN
	
	PRINT 'Nemozes mazat pacientov'
END

GO
SET NOCOUNT ON
DELETE FROM Pacienti WHERE idP = 1
SELECT * FROM Pacienti