SQL TRIGERID

create database trigerTARpv23;
use trigerTARpv23;

-- loomine tabeli toode  
CREATE TABLE toode (
toodeID int primary key identity(1,1),
toodeNimi varchar(60),
hind int
);

-- loome tabeli, mis täidab triger
create table logi(
id int primary key identity(1,1),
kasutaja varchar(100),
kuupaev datetime,
sisestatudAndmed TEXT
);

CREATE TRIGGER toodeLisamine 
ON toode -- tabel, mis jälgitakse
FOR INSERT
AS
INSERT INTO logi(kasutaja, kuupaev, sisestatudAndmed)
SELECT 
USER, 
GETDATE(),
CONCAT('lisatud andmed ', inserted.toodeNimi, ', ', inserted.hind)
FROM inserted
-- kontrolli
-- kontrollimiseks lisame 'toode'

INSERT INTO toode(toodeNimi, hind) VALUES ('magus õun', 10);
SELECT * FROM toode;
SELECT * FROM logi;

CREATE TRIGGER toodeKustutamine
ON toode -- tabel, mis jälgitakse
FOR DELETE
AS
INSERT INTO logi(kasutaja, kuupaev, sisestatudAndmed)
SELECT 
USER, 
GETDATE(),
CONCAT('kustutatud andmed - "', deleted.toodeNimi, '" indeks on - "', deleted.toodeID, '"')
FROM deleted
-- kontrolli
-- kontrollimiseks lisame 'toode'

DELETE FROM toode WHERE toodeID = 1;
SELECT * FROM toode;
SELECT * FROM logi;

CREATE TRIGGER toodeUuendamine
ON toode -- tabel, mis jälgitakse
FOR UPDATE
AS
INSERT INTO logi(kasutaja, kuupaev, sisestatudAndmed)
SELECT 
USER, 
GETDATE(),
CONCAT('vanad andmed - "', deleted.toodeNimi, '", vana hind -', deleted.hind ,', indeks on - "', deleted.toodeID, '". Uued andmed - "', inserted.toodeNimi, '", uue hind - "', inserted.hind, '"')
FROM deleted INNER JOIN inserted
ON deleted.toodeID = inserted.toodeID
-- kontrolli
-- kontrollimiseks lisame 'toode'

UPDATE toode SET hind = 12 WHERE toodeID = 2;
SELECT * FROM toode;
SELECT * FROM logi;