------
------ 1) Create Osoby, Projekty a Trigger po update Osoby obnovi Vztahy 
------    tzn. ak zmenime Priezvisko v Tabulke Osoby
------    => sa vdaka triggeru automaticky opravi Priezvisko v Tabulke Vztahy
------ Riesenie pomocou:
------    CREATE TRIGGER tr_Osoby ON Osoby 
------    AFTER UPDATE
------    if UPDATE(Priezvisko) -- bolo obnovene-apdejtovane Priezvisko

SET NOCOUNT ON;
----USE master;
USE tempdb;
GO

IF OBJECT_ID('dbo.Projekty')IS NOT NULL   DROP TABLE dbo.Projekty;
IF OBJECT_ID('dbo.Osoby')	IS NOT NULL   DROP TABLE dbo.Osoby;
GO

CREATE TABLE Osoby
( 
  id		 int  NOT NULL PRIMARY KEY, 
  Priezvisko varchar(20),
  Krstne	 varchar(20)
);
GO		-- RAISERROR('Tab Osoby Created.', 0, 1);


CREATE TABLE Projekty
( 
  id		int  NOT NULL PRIMARY KEY, 
  Nazov		varchar(40),
  PrieVed	varchar(40),
  idVed		int
);
GO

INSERT Osoby VALUES (1, 'A', 'a' );
INSERT Osoby VALUES (2, 'B', 'b' );
INSERT Osoby VALUES (3, 'C', 'c' );
GO

INSERT Projekty VALUES (1, 'X', 'B', 2);
INSERT Projekty VALUES (2, 'Y', 'C', 3);
INSERT Projekty VALUES (3, 'Z', 'C', 3);
GO

ALTER TABLE Projekty ADD CONSTRAINT f_idVed FOREIGN KEY (idVed) REFERENCES Osoby(id)
GO
--SELECT * FROM Osoby
--SELECT * FROM Projekty
GO

IF OBJECT_ID ('tr_Osoby', 'TR') IS NOT NULL DROP TRIGGER tr_Osoby
GO
CREATE TRIGGER tr_Osoby ON Osoby 
AFTER UPDATE
AS
	DECLARE @rc AS INT;
	SET @rc = @@rowcount;

	IF @rc = 0 RETURN;
	ELSE
--	Select * From inserted; Select * From deleted
--	Select (SELECT Priezvisko FROM deleted) + ' - ' + (SELECT Priezvisko FROM inserted)
	if UPDATE(Priezvisko) -- bolo obnovene Priezvisko
	begin
		print 'Dvojnasobny update ...'
		UPDATE Projekty SET PrieVed = (SELECT Priezvisko FROM inserted)
						WHERE Projekty.idVed = (SELECT id FROM inserted)
	end
GO

--SELECT * FROM Osoby;
--SELECT * FROM Projekty;

-- Zmen priezvisko B na BB:
UPDATE Osoby SET Priezvisko = 'BB' WHERE id = 2

SELECT * FROM Osoby;
SELECT * FROM Projekty;