Oleksandra Ryshniak Portfolio

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;

insert into linnad(linnanimi, rahvaarv)
Values('Tallinn', 650000);
select * from linnad;
select * from logi;

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;

DELETE from linnad where linnID=1;
select * from linnad; 
select * from logi;

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;

Update linnad set rahvaarv=650001
WHERE linnID=2;
select * from linnad;
select * from logi;