Praktiline töö / puu / puuleht / trigerid

create database puud;

use puud;

Create table puu (
puuID int identity (1,1) PRIMARY KEY,
puuNimi varchar(15),
kirjeldus varchar(250),
pikkus int);

Create table logi(
logiID int identity (1,1) PRIMARY KEY,
kasutaja varchar (100),
kuupaev DATETIME,
toiming  varchar(100),
andmed varchar(200)
);

--3 

CREATE TRIGGER puueLisamine
ON puu --tabel, mis jälgitakse
FOR INSERT
AS
INSERT INTO logi(kasutaja, kuupaev, toiming, andmed)
SELECT USER, GETDATE(), ' Lisamine',
CONCAT('Nimi: ', inserted.puuNimi, ' Kirjeldus: ',inserted.kirjeldus,' Pikkus: ',inserted.pikkus)
FROM inserted

CREATE TRIGGER puueKustutamine
ON puu 
FOR INSERT
AS
INSERT INTO logi(kasutaja, kuupaev, toiming, andmed)
SELECT USER, GETDATE(), 'Kustutamine',
CONCAT('Nimi: ', inserted.puuNimi, ' Kirjeldus: ',inserted.kirjeldus,' Pikkus: ',inserted.pikkus)
FROM inserted

CREATE TRIGGER puueUuendamine
ON puu --tabel, mis jälgitakse
FOR UPDATE
AS
INSERT INTO logi(kasutaja, kuupaev, toiming, andmed)
SELECT
USER,
GETDATE(), 'Uuendamine',
CONCAT('Vana andmed ', deleted.puuNimi, ', ',
deleted.kirjeldus, ',', deleted.pikkus, ' Uued andmed: ', inserted.puuNimi, ',', inserted.kirjeldus, ',', inserted.pikkus)
FROM deleted INNER JOIN inserted
ON deleted.puuID=inserted.puuID

-- 4
CREATE TABLE puuLeht(
puuLehtID int PRIMARY KEY identity(1,1),
lehtNimi varchar(15),
puuID int,
Foreign key (puuID) REFERENCES puu(puuID)
)


-- 5
INSERT INTO puu(puuNimi, kirjeldus, pikkus) 
VALUES 
('Tamm', 'Kõrge puu', 20),
('Kask', 'Valge tüvega lehtpuu', 15),
('Mänd', 'Okaspuu pikkade okastega', 30);

INSERT INTO puuLeht (lehtNimi, puuID) 
VALUES 
('Tammeleht', 1),
('Kaskeleht', 2),
('Männiokkad', 3);


SELECT * FROM puuLeht
SELECT * FROM puu

SELECT * FROM logi


CREATE TRIGGER puuLisamine
ON puu
FOR INSERT
AS
INSERT INTO logi(kasutaja, kuupaev, toiming, andmed)
SELECT USER, GETDATE(), 'Lisamine',
CONCAT(p.puuNimi, ', ', pl.lehtNimi)
from puu p
Inner join puuLeht pl
ON pl.puuID=p.puuID


CREATE TRIGGER puuKustutamine
ON puu
FOR DELETE 
AS
INSERT INTO logi(kasutaja, kuupaev, toiming, andmed)
SELECT USER, GETDATE(), ' Kustutatud',
CONCAT(deleted.puuNimi, ', ', pl.lehtNimi)
from deleted
Inner join puuLeht pl
ON deleted.puuID=pl.puuID;


CREATE TRIGGER linnaUuendamine
ON puu
FOR UPDATE
AS
INSERT INTO logi(kasutaja, kuupaev, toiming, andmed)
SELECT USER, GETDATE(), ' Muudatud',
CONCAT
('vanad andmed - ', deleted.puuNimi, ', ', pl1.lehtNimi,
' uuendatud andmed - ', inserted.puuNimi, ', ', pl2.lehtNimi)
from deleted
Inner join inserted ON deleted.puuID=inserted.puuID
Inner join puuLeht pl1 ON deleted.puuID=pl1.puuLehtID
Inner join puuLeht pl2 ON inserted.puuID=pl2.puuLehtID;


UPDATE puu SET puuNimi='Tallinn-Väike', puuLehtID = 2
WHERE puuID=2;


DROP TRIGGER puuLisamine
DROP TRIGGER puuKustutamine
DROP TRIGGER linnaUuendamine