--############################# Zadanie 0 ##############################
--napisać kod ktory sprawdzi jakie sa w danej bazie widoki i skasuje wszystkie
SELECT
 OBJECT_SCHEMA_NAME(v.object_id) schema_name,
 v.name
FROM
 sys.views as v;


--############################# ZADANIE 1 ##############################

Create trigger
GO

--DROP TABLE Test;
--GO

CREATE TABLE Test (ID int PRIMARY KEY, Ostatnia_aktualizacja datetime);
GO

INSERT INTO Test(ID) VALUES(1);
INSERT INTO Test(ID) VALUES(2);
GO

--DROP TRIGGER trigger1;
--GO
CREATE TRIGGER trigger1
ON Test
AFTER UPDATE
AS
	BEGIN
		UPDATE Test SET Ostatnia_aktualizacja = GETDATE() WHERE ID = (SELECT ID FROM inserted);
	END
GO

UPDATE Test SET ID = 3 WHERE ID = 2;
GO

SELECT * FROM Test;



--############################# ZADANIE 2 ##############################



--DROP TABLE Test;
GO

CREATE TABLE Test (ID int, Ostatnia_aktualizacja date);
GO

INSERT INTO Test(ID) VALUES(1);
INSERT INTO Test(ID) VALUES(2);
GO

--DROP TABLE Test_Historia;
SELECT * INTO Test_Historia FROM Test WHERE 1 = 0;
ALTER TABLE Test_Historia
	ADD Kto varchar(30),
		Kiedy datetime,
		Operacja varchar(30);
GO

--DROP TRIGGER trigger2;
CREATE TRIGGER trigger2
ON Test
AFTER DELETE
AS
	BEGIN
		INSERT INTO Test_Historia VALUES(
			(SELECT ID FROM deleted),
			(SELECT Ostatnia_aktualizacja FROM deleted),
			CURRENT_USER,
			GETDATE(),
			'DELETE');
	END
GO

DELETE FROM Test WHERE ID = 1;
SELECT * FROM Test_Historia;

--############################# ZADANIE 3 ##############################

--DROP TABLE Test;
GO

CREATE TABLE Test (ID int, Ostatnia_aktualizacja date);
GO

INSERT INTO Test(ID) VALUES(1);
INSERT INTO Test(ID) VALUES(2);
GO

--DROP TABLE Test_Historia;
SELECT * INTO Test_Historia FROM Test WHERE 1 = 0;
ALTER TABLE Test_Historia
	ADD Kto varchar(30),
		Kiedy datetime,
		Operacja varchar(30);
GO

--DROP TRIGGER trigger3;
CREATE TRIGGER trigger3
ON Test
AFTER UPDATE
AS
	BEGIN
		INSERT INTO Test_Historia VALUES(
			(SELECT ID FROM deleted),
			(SELECT Ostatnia_aktualizacja FROM deleted),
			CURRENT_USER,
			GETDATE(),
			'UPDATE');
	END
GO

UPDATE Test SET ID = 3 WHERE ID = 2;
SELECT * FROM Test_Historia;

--############################# ZADANIE 4 ##############################



--DROP TABLE Test;
GO

CREATE TABLE Test (ID int, Kategoria varchar(30));
GO

INSERT INTO Test(ID, Kategoria) VALUES(1, 'Kat 1');
INSERT INTO Test(ID, Kategoria) VALUES(2, 'Kat 2');
GO

--DROP TRIGGER trigger4;
CREATE TRIGGER trigger4
ON Test
AFTER INSERT
AS
	BEGIN
		IF ((SELECT COUNT(*) FROM Test WHERE Kategoria = (SELECT Kategoria FROM inserted)) >= 2)
			BEGIN
				PRINT 'ABC'
				ROLLBACK TRANSACTION
			END
		ELSE
			BEGIN
				PRINT '123'
			END
	END
GO

DELETE FROM Test WHERE ID = 3;
INSERT INTO Test(ID, Kategoria) VALUES(3, 'Kat 3');
SELECT * FROM Test;



--############################# ZADANIE 5 ##############################
--DROP TABLE Test;
GO

CREATE TABLE Test (ID int, Kategoria varchar(30));
GO

INSERT INTO Test(ID, Kategoria) VALUES(1, 'Kat 1');
INSERT INTO Test(ID, Kategoria) VALUES(2, 'Kat 2');
GO

DROP TRIGGER trigger5;
GO

CREATE TRIGGER trigger5
ON Test
INSTEAD OF INSERT
AS
	BEGIN
		INSERT INTO test SELECT inserted.ID, UPPER(inserted.Kategoria) FROM inserted;
	END
GO

DELETE FROM Test WHERE ID = 3;
INSERT INTO Test(ID, Kategoria) VALUES(3, 'Kat 3');
SELECT * FROM Test;


--############################# ZADANIE 5.1 ##############################



--DROP TABLE Test;
GO

CREATE TABLE Test (ID int, Cena int);
GO

INSERT INTO Test(ID, Cena) VALUES(1, 10);
INSERT INTO Test(ID, Cena) VALUES(2, 20);
GO

--DROP TRIGGER trigger6;
CREATE TRIGGER trigger6
ON Test
INSTEAD OF INSERT
AS
	BEGIN
		IF ((SELECT Cena FROM inserted) < 0)
			BEGIN
				INSERT INTO test SELECT inserted.ID, 0 FROM inserted;
			END
		ELSE
			BEGIN
				INSERT INTO test SELECT * FROM inserted;
			END
	END
GO

DELETE FROM Test WHERE ID = 3;
INSERT INTO Test(ID, Cena) VALUES(4, 15);
SELECT * FROM Test;



--############################# ZADANIE 5.2 ##############################




--DROP TABLE Test;
GO

CREATE TABLE Test (ID int, NumerKategorii int);
GO

INSERT INTO Test(ID, NumerKategorii) VALUES(1, 1);
INSERT INTO Test(ID, NumerKategorii) VALUES(2, 3);
GO

--DROP TRIGGER trigger7;
CREATE TRIGGER trigger7
ON Test
INSTEAD OF INSERT
AS
	BEGIN
		IF NOT EXISTS (SELECT CategoryID FROM Categories WHERE CategoryID = (SELECT NumerKategorii FROM inserted))
			BEGIN
				PRINT 'Kategoria nie istnieje';
			END
		ELSE
			BEGIN
				INSERT INTO test SELECT * FROM inserted;
			END
	END
GO

DELETE FROM Test WHERE ID = 3;
INSERT INTO Test(ID, NumerKategorii) VALUES(2, 2);
SELECT * FROM Test;



--############################# ZADANIE 5.3 ##############################


--DROP TABLE Test;
GO

CREATE TABLE Test (ID int, NazwaKategorii varchar(30));
GO

INSERT INTO Test(ID, NazwaKategorii) VALUES(1, 'Kat 1');
INSERT INTO Test(ID, NazwaKategorii) VALUES(2, 'Kat 2');
GO

--DROP TRIGGER trigger8;
CREATE TRIGGER trigger8
ON Test
AFTER UPDATE
AS
	BEGIN
		IF (COLUMNS_UPDATED() = 2)
			BEGIN
				PRINT 'Nie można zmieniać nazwy kategorii';
				ROLLBACK TRANSACTION;
			END
	END
GO

DELETE FROM Test WHERE ID = 2;
UPDATE Test SET ID = 8 WHERE ID = 2;
SELECT * FROM Test;


--############################# ZADANIE 5.4 ##############################

--DROP TABLE Test;
GO

CREATE TABLE Test (ID int, NazwaKategorii varchar(30));
GO

INSERT INTO Test(ID, NazwaKategorii) VALUES(1, 'Kat 1');
INSERT INTO Test(ID, NazwaKategorii) VALUES(2, 'Kat 2');
GO

--DROP TRIGGER trigger9;
CREATE TRIGGER trigger9
ON Test
AFTER UPDATE
AS
	BEGIN
		IF UPDATE(NazwaKategorii)
			BEGIN
				PRINT 'Nie można zmieniać nazwy kategorii';
				ROLLBACK TRANSACTION;
			END
	END
GO

DELETE FROM Test WHERE ID = 8;
UPDATE Test SET NazwaKategorii = 'Kat 2137' WHERE ID = 2;
SELECT * FROM Test;



--############################# ZADANIE 5.5 ##############################

DROP TRIGGER trigger1;