-- Primjer 1. 
DECLARE @Ime nvarchar(50)
DECLARE @Prezime nvarchar(50)

SET @Ime = 'Miro'
SET @Prezime = 'Mirić'

PRINT @Ime
PRINT @Prezime
PRINT 'Korisnik: ' + @Ime + ' ' + @Prezime

SELECT @Ime
SELECT @Prezime
SELECT 'Korisnik: ' + @Ime + ' ' + @Prezime
GO

-- Primjer 2.
DECLARE @Ime nvarchar(50)
DECLARE @Prezime nvarchar(50)

SELECT @Ime = Ime, @Prezime = Prezime FROM Kupac WHERE IDKupac = 16

SELECT 'Korisnik: ' + @Ime + ' ' + @Prezime
GO

-- Primjer 3.
DECLARE @Ime nvarchar(50)
DECLARE @Prezime nvarchar(50)

SELECT @Ime = Ime, @Prezime = Prezime FROM Kupac

SELECT 'Korisnik: ' + @Ime + ' ' + @Prezime
GO

-------------------------------------------------------------------------
-------------------------------------------------------------------------
-- Izrada log tablice. 
CREATE TABLE Student
(
	IDStudent int PRIMARY KEY IDENTITY,
	Ime nvarchar(50),
	Prezime nvarchar(50),
	JMBAG nvarchar(50)
)
INSERT INTO Student VALUES ('Ana', 'Anić', '11111111111')
INSERT INTO Student VALUES ('Miro', 'Mirić', '2222222222222')
INSERT INTO Student VALUES ('Juro', 'Jurić', '3333333333')
INSERT INTO Student VALUES ('Iva', 'Ivić', '4444444444')
INSERT INTO Student VALUES ('Maja', 'Majić', '5555555555')

CREATE TABLE Zapisnik
(
	IDZapisnik int IDENTITY(1,1) PRIMARY KEY,
	Poruka nvarchar(max),
	Vrijeme datetime DEFAULT getdate()
)
GO

-- 1.
CREATE TRIGGER dbo.Okidac1 ON dbo.Student AFTER INSERT
AS
INSERT INTO Zapisnik (Poruka) VALUES ('Umetnut redak u Student!')
GO

INSERT INTO Student (Ime, Prezime, JMBAG) VALUES ('Ime 1', 'Prezime 1', 'JMBAG 1')

SELECT * FROM Zapisnik
GO

-- 2.
ALTER TRIGGER Okidac1 ON Student AFTER INSERT
AS
DECLARE @Ime nvarchar(50)
DECLARE @Prezime nvarchar(50)
DECLARE @JMBAG nvarchar(50)
SELECT @Ime = Ime, @Prezime = Prezime, @JMBAG = JMBAG FROM inserted

INSERT INTO Zapisnik (Poruka) VALUES ('Umetnut redak u Student. Ime: ' + @Ime + ', Prezime: ' + @Prezime + ', JMBAG: ' + @JMBAG)
GO

INSERT INTO Student (Ime, Prezime, JMBAG) VALUES ('Ime 2', 'Prezime 2', 'JMBAG 2')

SELECT * FROM Zapisnik
GO

-- 3.
ALTER TRIGGER Okidac1 ON Student AFTER INSERT, UPDATE, DELETE
AS
DECLARE @i int
DECLARE @d int
SELECT @i = COUNT(*) FROM inserted
SELECT @d = COUNT(*) FROM deleted

INSERT INTO Zapisnik (Poruka) VALUES ('inserted: ' + CAST(@i as nvarchar(20)) + ', deleted: ' + CAST(@d as nvarchar(20)))
GO

INSERT INTO Student (Ime, Prezime, JMBAG) VALUES ('Ime 3', 'Prezime 3', 'JMBAG 3')
SELECT * FROM Zapisnik

UPDATE Student SET Prezime = Prezime + 'ić'
SELECT * FROM Zapisnik

DELETE FROM Student
SELECT * FROM Zapisnik
GO

-- 4.
ALTER TRIGGER Okidac1 ON Student AFTER UPDATE
AS
DECLARE @old nvarchar(50)
DECLARE @new nvarchar(50)
SELECT @old = Prezime FROM deleted
SELECT @new = Prezime FROM inserted

INSERT INTO Zapisnik (Poruka) VALUES ('Stara: ' + @old + ', nova: ' + @new)
GO

UPDATE Student SET Prezime = 'Krkic' WHERE IDStudent = 1
SELECT * FROM Zapisnik
GO

-- 5.
DISABLE TRIGGER Okidac1 ON Student
GO

UPDATE Student SET Prezime = 'Krkic' WHERE IDStudent = 9
SELECT * FROM Zapisnik
GO

ENABLE TRIGGER Okidac1 ON Student
GO

-- 6.
CREATE TRIGGER Okidac2 ON Student AFTER INSERT, UPDATE, DELETE
AS
DECLARE @Dogadjaj nvarchar(50)

IF EXISTS(SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted) BEGIN
	-- INSERT.	
	INSERT INTO Zapisnik (Poruka) VALUES ('Desio se INSERT.')
END
ELSE IF NOT EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted) BEGIN
	-- DELETE.
	INSERT INTO Zapisnik (Poruka) VALUES ('Desio se DELETE.')
END
ELSE IF EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted) BEGIN
	-- UPDATE.
	INSERT INTO Zapisnik (Poruka) VALUES ('Desio se UPDATE.')
END
GO

INSERT INTO Student (Ime, Prezime, JMBAG) VALUES ('Ime 4', 'Prezime 4', 'JMBAG 4')
SELECT * FROM Zapisnik

UPDATE Student SET Prezime = Prezime + 'ić' WHERE IDStudent = 10
SELECT * FROM Zapisnik

DELETE FROM Student WHERE IDStudent = 10
SELECT * FROM Zapisnik
GO

-- 7.
DISABLE TRIGGER Okidac1 ON Student
GO
DISABLE TRIGGER Okidac2 ON Student
GO

CREATE TRIGGER Okidac3 ON Student AFTER UPDATE
AS
IF UPDATE(Prezime) BEGIN
	INSERT INTO Zapisnik (Poruka) VALUES ('Promijenjen je stupac Prezime.')
END
GO

UPDATE Student SET Ime = 'Frane' WHERE IDStudent = 11
SELECT * FROM Zapisnik

UPDATE Student SET Prezime = 'Franić' WHERE IDStudent = 11
SELECT * FROM Zapisnik
GO

-- 8.
DROP TRIGGER Okidac1
DROP TRIGGER Okidac2
DROP TRIGGER Okidac3
GO

CREATE TRIGGER Okidac1 ON Student AFTER INSERT AS
INSERT INTO Zapisnik (Poruka) VALUES ('Pozdrav iz broja 1')
GO

CREATE TRIGGER Okidac2 ON Student AFTER INSERT AS
INSERT INTO Zapisnik (Poruka) VALUES ('Pozdrav iz broja 2')
GO

CREATE TRIGGER Okidac3 ON Student AFTER INSERT AS
INSERT INTO Zapisnik (Poruka) VALUES ('Pozdrav iz broja 3')
GO

CREATE TRIGGER Okidac4 ON Student AFTER INSERT AS
INSERT INTO Zapisnik (Poruka) VALUES ('Pozdrav iz broja 4')
GO

INSERT INTO Student (Ime, Prezime, JMBAG) VALUES ('Ime 5', 'Prezime 5', 'JMBAG 5')
SELECT * FROM Zapisnik
GO

EXEC sp_settriggerorder 'Okidac4', 'FIRST', 'INSERT'
EXEC sp_settriggerorder 'Okidac1', 'LAST', 'INSERT'
GO

INSERT INTO Student (Ime, Prezime, JMBAG) VALUES ('Ime 6', 'Prezime 6', 'JMBAG 6')
SELECT * FROM Zapisnik
GO

EXEC sp_settriggerorder 'Okidac4', 'NONE', 'INSERT'
EXEC sp_settriggerorder 'Okidac1', 'NONE', 'INSERT'
GO

INSERT INTO Student (Ime, Prezime, JMBAG) VALUES ('Ime 7', 'Prezime 7', 'JMBAG 7')
SELECT * FROM Zapisnik
GO

DROP TRIGGER Okidac1
DROP TRIGGER Okidac2
DROP TRIGGER Okidac3
DROP TRIGGER Okidac4
GO

-- 9.
CREATE TABLE Tbl1
(
	Stupac1 int PRIMARY KEY IDENTITY,
	Stupac2 nvarchar(50)
)
CREATE TABLE Tbl2
(
	Stupac1 int PRIMARY KEY IDENTITY,
	Stupac2 nvarchar(50)
)
GO

CREATE TRIGGER Okidac1 ON Tbl1 AFTER INSERT 
AS
INSERT INTO Zapisnik (Poruka) VALUES ('Okidač 1 na tablici 1')
INSERT INTO Tbl2 VALUES ('Upis u drugu tablicu')
GO

CREATE TRIGGER Okidac2 ON Tbl2 AFTER INSERT 
AS
INSERT INTO Zapisnik (Poruka) VALUES ('Okidač 2 na tablici 2')
INSERT INTO Tbl1 VALUES ('Upis u prvu tablicu')
GO

INSERT INTO Tbl1 VALUES ('Upis u prvu tablicu')
SELECT * FROM Zapisnik
GO

-- 10.
CREATE TRIGGER Okidac10 ON Student AFTER INSERT 
AS
INSERT INTO Zapisnik (Poruka) VALUES ('Okidač 10 na Student')
INSERT INTO Student VALUES ('Oki', 'Dačko', '111222333')
GO

INSERT INTO Student (Ime, Prezime, JMBAG) VALUES ('Ime 8', 'Prezime 8', 'JMBAG 8')
SELECT * FROM Zapisnik
GO

ALTER DATABASE AdventureWorksOBP
SET RECURSIVE_TRIGGERS ON
GO

INSERT INTO Student (Ime, Prezime, JMBAG) VALUES ('Ime 8', 'Prezime 8', 'JMBAG 8')
SELECT * FROM Zapisnik
GO

ALTER DATABASE AdventureWorksOBP
SET RECURSIVE_TRIGGERS OFF
GO
