create database martinTrigerid
use martinTrigerid

Create table linnad(
linnID int identity(1,1) PRIMARY KEY,
linnanimi varchar(15),
rahvaarv int);

--tabel logi näitab adminile kuidas tabeli linnad kasutatakse, 
--tabel logi täidab triger
Create table logi(
id int identity(1,1) PRIMARY KEY,
aeg DATETIME,
toiming  varchar(100),
andmed varchar(200),
kasutaja varchar(100)

--insert trigger mis jälgib tabeli linnad täitmine
create trigger linnaLisamine
on linnad
for insert
as
insert into logi(aeg, kasutaja, toiming, andmed)
select
getdate(),
SYSTEM_USER,
'linn on lisatud',
inserted.linnanimi
from inserted;

--trigeri tegevuse kontroll
insert into linnad(linnanimi,rahvaarv)
values ('Tallinn', 650000);
select * from linnad;
select * from logi;

--delete trigger, jälgib linna kustatamine tabelis linnad
create trigger linnaKustutamine
on linnad
for delete
as
insert into logi(aeg, kasutaja, toiming, andmed)
select
getdate(),
SYSTEM_USER,
'linn on kustutatud',
deleted.linnanimi
from deleted;

--kontroll
delete from linnad where linnID=1;
select * from linnad;
select * from logi;

--update trigger
create trigger linnaUuendamine
on linnad
for update
as
insert into logi(aeg, kasutaja, toiming, andmed)
select
getdate(),
SYSTEM_USER,
'linn on uuendatud',
concat ('vanad andmed: ', deleted.linnanimi, ' ,', deleted.rahvaarv, 'uued andmed: ', inserted.linnanimi, ' ,',inserted.rahvaarv)
from deleted
inner join inserted
on deleted.linnId=inserted.linnId;

--kontroll
update linnad set rahvaarv=650002
where linnID=2;
select * from linnad;
select * from logi;

create database triger2tabelid
use triger2tabelid

--tabel toode ja toodekategooria on seotud oma vahel
create table toodekategooria(
toodekategooriaId int not null primary key identity (1,1),
toodekategooria varchar(100) unique,
kirjeldus text);

create table toode(
toodeId int not null primary key identity (1,1),
toodenimetus varchar(100) unique,
hind decimal(5,2),
toodekategooriaId int,
foreign key (toodekategooriaId) references toodekategooria(toodekategooriaId)
);

insert into toodekategooria(toodekategooria)
values ('joogid');
select * from toodekategooria

insert into toode(toodenimetus, hind, toodekategooriaId)
values ('kino',10,1)
select t.toodenimetus, t.hind, tk.toodekategooria from toode t
inner join toodekategooria tk
on t.toodekategooriaId=tk.toodekategooriaId;

--tabel logi loomine

create table logi(
logiId int not null primary key identity(1,1),
kuupaev datetime,
kasutaja varchar (100),
andmed text
);

--triger mis jälgib insert lisamine tabelisse
create trigger toodeLisamine
on toode
for insert
as
insert into logi(kuupaev, kasutaja, andmed)
select
getdate(),
SYSTEM_USER,
concat(inserted.toodenimetus,', ',inserted.hind, ', ',tk.toodekategooria)
from inserted
inner join toodekategooria tk
on inserted.toodekategooriaId=tk.toodekategooriaId;

--kontroll
insert into toode(toodenimetus, hind, toodekategooriaId)
values ('teater',100,1)
select * from toode
select * from logi

--triger mis jälgib delete kustutamine tabelisse
create trigger toodeKustutamine
on toode
for delete
as
insert into logi(kuupaev, kasutaja, andmed)
select
getdate(),
SYSTEM_USER,
concat('Kustutatud tabelist :',deleted.toodenimetus,', ',deleted.hind, ', ',tk.toodekategooria)
from deleted
inner join toodekategooria tk
on deleted.toodekategooriaId=tk.toodekategooriaId;

--kontroll
delete from toode where toodeId=5;
select * from toode
select * from logi

--triger mis jälgib update tabelisse
Create trigger toodeUuendamine
ON toode
for update
as
Insert into logi(kuupaev, kasutaja, andmed)
select
GETDATE(),
SYSTEM_USER,
CONCAT(
'vanad: ', deleted.toodenimetus, ', ', deleted.hind, ', ', tk1.toodekategooria,
' || uued:', inserted.toodenimetus, ', ', inserted.hind, ', ', tk2.toodekategooria)
from deleted
inner join inserted on deleted.toodeId=inserted.toodeId
inner join toodekategooria tk1
on deleted.toodekategooriaId=tk1.toodekategooriaId
inner join toodekategooria tk2
on inserted.toodekategooriaId=tk2.toodekategooriaId;
 
 --kontroll
update toode set toodenimetus='teater', toodekategooriaID=1
Where toodeID=1

select * from toode
select * from logi


--#1
create trigger toodeLisamineJaKasutamine
on toode
after insert, delete
as
begin
set nocount on
insert into logi(kasutaja, kuupaev, andmed)
select
SYSTEM_USER,
GETDATE(),
CONCAT('Lisatud: ', inserted.toodenimetus, ', ', inserted.hind, ', ', tk.toodekategooria)
from inserted
inner join toodekategooria tk
on inserted.toodekategooriaId=tk.toodekategooriaId
union all
select
SYSTEM_USER,
GETDATE(),
CONCAT('Kustutatud: ', deleted.toodenimetus, ', ',deleted.hind, ', ', tk.toodekategooria)
from deleted
inner join toodekategooria tk
on deleted.toodekategooriaId=tk.toodekategooriaId
end;

disable trigger toodeKustutamine on toode
disable trigger toodeLisamine on toode

insert into toode(toodeNimetus, hind, toodekategooriaID)
values('kino', 2.50,1)
select * from toode
select * from logi

create database videocardproducts
use videocardproducts

--tabel videokaardkategooria loomine
create table videokaardkategooria(
videokaardkategooriaId int not null primary key identity (1,1),
videokaardkategooria varchar(100) unique,
kirjeldus text);

--tabel videokaardid
create table videokaardid(
id int identity(1,1) primary key,
tootja varchar(20),
mudel varchar (50),
kuupaev datetime,
videokaardkategooriaId int,
foreign key (videokaardkategooriaId) references videokaardkategooria(videokaardkategooriaId)
)

--tabel logi
create table logi(
id int identity(1,1) not null primary key,
kuupaev datetime,
andmed varchar (200),
kasutaja varchar (100),
);

insert into videokaardkategooria(videokaardkategooria)
values ('Integreeritud');
select * from videokaardkategooria

--trigger videokaardLisamine
create trigger videokaardLisamine
on videokaardid
for insert
as
insert into logi(kuupaev, kasutaja, andmed)
select
getdate(),
SYSTEM_USER,
concat('Videokaard on lisatud: ',inserted.mudel,', ',inserted.tootja, ', ',vk.videokaardkategooria)
from inserted
inner join videokaardkategooria vk
on inserted.videokaardkategooriaId=vk.videokaardkategooriaId;

--kontroll
insert into videokaardid(tootja,mudel,videokaardkategooriaId)
values ('AMD', 'RX9060',1);
select * from videokaardid
select * from logi

--trigger videokaardKusutamine
create trigger videokaardKusutamine
on videokaardid
for delete
as
insert into logi(kuupaev, kasutaja, andmed)
select
getdate(),
SYSTEM_USER,
concat('Kustutatud tabelist :',deleted.mudel,', ',deleted.tootja, ', ',vk.videokaardkategooria)
from deleted
inner join videokaardkategooria vk
on deleted.videokaardkategooriaId=vk.videokaardkategooriaId;

--kontroll
delete from videokaardid where id=2;
select * from videokaardid
select * from logi

--Kaksik-trigger lisamise ja kustutamisega
create trigger videokaardLisamineJaKasutamine
on videokaardid
after insert, delete
as
begin
  set nocount on;
  insert into logi(kuupaev, kasutaja, andmed)
  select
    GETDATE(),
    SYSTEM_USER,
    CONCAT('Lisatud: ', inserted.mudel, ', ', inserted.tootja, ', ', vk.videokaardkategooria)
  from inserted
  inner join videokaardkategooria vk
    on inserted.videokaardkategooriaId = vk.videokaardkategooriaId
  union all
  select
    GETDATE(),
    SYSTEM_USER,
    CONCAT('Kustutatud: ', deleted.mudel, ', ', deleted.tootja, ', ', vk.videokaardkategooria)
  from deleted
  inner join videokaardkategooria vk
    on deleted.videokaardkategooriaId = vk.videokaardkategooriaId;
end;



--kontroll
disable trigger videokaardLisamine on videokaardid
disable trigger videokaardKusutamine on videokaardid

insert into videokaardid(tootja, mudel,videokaardkategooriaId)
values('NVIDIA NEW','RTX 2080',1)
select * from videokaardid
select * from logi

create database kool
use kool

--tabelid
CREATE TABLE Opetaja (
OpetajaID int NOT NULL,
OpetajaNimi varchar(100),
Aine varchar(50),
CONSTRAINT Opetaja_pk PRIMARY KEY (OpetajaID)
);

CREATE TABLE Klass (
KlassID int NOT NULL,
KlassNimi varchar(30),
Opetaja_OpetajaID int,
OpilasteArv int,
CONSTRAINT Klass_pk PRIMARY KEY (KlassID)
);

CREATE TABLE Opilane (
OpilaneID int NOT NULL,
OpilaseNimi varchar(100),
Klass_KlassID int NOT NULL,
CONSTRAINT Opilane_pk PRIMARY KEY (OpilaneID)
);

create table logi(
Id int identity(1,1) primary key,
kuupaev datetime,
tegevus varchar(100),
andmed varchar (200),
kasutaja varchar(100)
);

INSERT INTO Opetaja (OpetajaID, OpetajaNimi, Aine)
VALUES
(1, 'Marina Oleinik', 'Mobiilrakendus'),
(2, 'Irina Merkulova', 'Andmebaasid'),
(3, 'Anton Buketov', 'Programmeerimine'),
(4, 'Ruslan Morris', 'Inglise Keel');


INSERT INTO Klass (KlassID, KlassNimi, Opetaja_OpetajaID, OpilasteArv)
VALUES
(1, 'TARpv24', 2, 20),
(2, 'TARpv23', 1, 18),
(3, 'LOGITpv24', 3, 22),
(4, 'LOGITpv23', 4, 19);


INSERT INTO Opilane (OpilaneID, OpilaseNimi, Klass_KlassID)
VALUES
(1, 'Martin Rossakov', 1),
(2, 'Mark Jurgen', 1),
(3, 'Illia Blahun', 2),
(4, 'Marek Lukk', 3);


select * from Klass
select * from Opetaja
select * from Opilane

-- foreign keys
ALTER TABLE Klass ADD CONSTRAINT Klass_Opetaja
    FOREIGN KEY (Opetaja_OpetajaID) REFERENCES Opetaja (OpetajaID);

ALTER TABLE Opilane ADD CONSTRAINT Opilane_Klass
    FOREIGN KEY (Klass_KlassID) REFERENCES Klass (KlassID);


--user
GRANT SELECT, INSERT ON Klass to opilaneNimi;
GRANT SELECT, INSERT ON Opetaja to opilaneNimi;
GRANT SELECT, INSERT ON Opilane to opilaneNimi;

--trigger delete
CREATE TRIGGER KlassKustutamine
ON Klass
FOR DELETE
AS
INSERT INTO Logi (kuupaev, tegevus, andmed, kasutaja)
SELECT
GETDATE(),
'Klassi kustutamine',
CONCAT('Kustutati klass: nimi=', d.KlassNimi,', õpetaja=', o.OpetajaNimi,', aine=', o.Aine,', õpilaste arv=', d.OpilasteArv),
SYSTEM_USER
FROM deleted d
LEFT JOIN Opetaja o ON d.Opetaja_OpetajaID = o.OpetajaID;

--trigger insert
CREATE TRIGGER KlassLisamine
ON Klass
FOR INSERT
AS
INSERT INTO Logi (kuupaev, tegevus, andmed, kasutaja)
SELECT
GETDATE(),
'Klassi lisamine',
CONCAT('Lisati klass: nimi=', i.KlassNimi,', õpetaja=', o.OpetajaNimi,', aine=', o.Aine,', õpilaste arv=', i.OpilasteArv),
SYSTEM_USER
FROM inserted i
LEFT JOIN Opetaja o ON i.Opetaja_OpetajaID = o.OpetajaID;

--kontroll
INSERT INTO Klass (KlassID, KlassNimi, Opetaja_OpetajaID, OpilasteArv) 
VALUES (6,'TARpv21', 2, 15);
select * from Klass
select * from logi

DELETE FROM Klass WHERE KlassID = 5;
select * from Klass
select * from logi


--procedure kõik klassid ja nennde õpilased
CREATE PROCEDURE klassidJaOpilased
@opetaja_nimi NVARCHAR(50)
AS
BEGIN
    SELECT 
        k.KlassNimi AS Klass,
        o.OpilaseNimi AS Opilane
    FROM Klass k
    JOIN Opetaja t ON k.Opetaja_OpetajaID = t.OpetajaID
    JOIN Opilane o ON o.Klass_KlassID = k.KlassID
    WHERE t.OpetajaNimi = @opetaja_nimi;
END;

EXEC klassidJaOpilased 'Irina Merkulova';

--transaktsioon
begin transaction;
save transaction esimene;
select * from Opilane
insert into Opilane (OpilaneID, OpilaseNimi, Klass_KlassID)
values (6, 'Nikita Orlenko', 6);
select * from Opilane
rollback transaction esimene;
select* from Opilane

--oma andmebaasi tegevus // uuendamine tabel Klass
CREATE TRIGGER KlassUuendamine
ON Klass
FOR UPDATE
AS
INSERT INTO Logi (kuupaev, tegevus, andmed, kasutaja)
SELECT
GETDATE(),
'Klassi uuendamine',
CONCAT('Vana: klass=', d.KlassNimi,', õpetaja=', tOld.OpetajaNimi,', aine=', tOld.Aine,', õpilaste arv=', d.OpilasteArv,
' / Uus: klass=', i.KlassNimi,', õpetaja=', tNew.OpetajaNimi,', aine=', tNew.Aine,', õpilaste arv=', i.OpilasteArv),
SYSTEM_USER
FROM deleted d
INNER JOIN inserted i ON d.KlassID = i.KlassID
LEFT JOIN Opetaja tOld ON d.Opetaja_OpetajaID = tOld.OpetajaID
LEFT JOIN Opetaja tNew ON i.Opetaja_OpetajaID = tNew.OpetajaID;

--kontroll
update Klass set OpilasteArv = 19
where KlassID = 1;
select * from Klass
select * from logi


use kool

select * from Klass
select * from Opetaja
select * from Opilane

INSERT INTO Opilane (OpilaneID, OpilaseNimi, Klass_KlassID)
VALUES
(5, 'Roman Zaitsev', 4);

update Opilane set OpilaseNimi='Roman Test' where OpilaneID=5 

create table testkasutaja(
id int identity(1,1) primary key,
test varchar(15),
);