Tabelita ja trigerite loomine

create database koolAB
use koolAB
CREATE TABLE Opilased (
OpilaneID INT identity(1,1) PRIMARY KEY,
OpilaneNimi VARCHAR(100),
Klass VARCHAR(10),
KeskmineHinne DECIMAL(3,2)
);
CREATE TABLE Logid (
LogiID INT identity(1,1) PRIMARY KEY,
Kuupaev DATETIME,
Andmed TEXT,
Kasutaja VARCHAR(100)
);
CREATE TABLE Hinded (
HinneID INT identity(1,1) PRIMARY KEY,
OpilaneID INT,
Aine VARCHAR(50),
Hinne INT,
FOREIGN KEY (OpilaneID) REFERENCES Opilased(OpilaneID)

);
CREATE TRIGGER OpilaneLisamineKustutamine
ON Opilased
FOR INSERT, DELETE
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Logid (Kuupaev, Andmed, Kasutaja)
SELECT
GETDATE(),
CONCAT(
'Lisatud opilane: ',inserted.OpilaneNimi,
'  klass: ', inserted.Klass,
'  keskmine hinne: ', inserted.KeskmineHinne
),
SYSTEM_USER
FROM inserted

UNION ALL

SELECT
GETDATE(),
CONCAT(
'Kustutatud opilane: ',
deleted.OpilaneNimi,
' | klass: ', deleted.Klass,
' | keskmine hinne: ', deleted.KeskmineHinne
),
SYSTEM_USER
FROM deleted;

END;

CREATE TRIGGER HinneLisamineUuendamine
ON Hinded
FOR INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO Logid (Kuupaev, Andmed, Kasutaja)
SELECT
GETDATE(),
CONCAT(
'Lisatud hinne: ', inserted.Hinne,
'  aine: ', inserted.Aine,
'  opilane: ', o.OpilaneNimi
),
SYSTEM_USER
FROM inserted
INNER JOIN Opilased o
ON inserted.OpilaneID = o.OpilaneID
WHERE NOT EXISTS (
SELECT 1
FROM deleted
WHERE deleted.HinneID = inserted.HinneID
    );

INSERT INTO Logid (Kuupaev, Andmed, Kasutaja)
SELECT
GETDATE(),
CONCAT(
'Vana hinne: ', deleted.Hinne,
' || Uus hinne: ', inserted.Hinne,
' uus aine: ', inserted.Aine,
' vana aine: ', deleted.Aine,
' uus opilane: ', o_uus.OpilaneNimi,
' vana opilane: ', o_vana.OpilaneNimi
),
SYSTEM_USER
FROM inserted
INNER JOIN deleted
ON inserted.HinneID = deleted.HinneID
INNER JOIN Opilased o_vana
ON deleted.OpilaneID = o_vana.OpilaneID
INNER JOIN Opilased o_uus
ON inserted.OpilaneID = o_uus.OpilaneID;
END;

drop trigger HinneLisamineUuendamine


GRANT SELECT, INSERT, UPDATE, DELETE ON Opilased TO Opetaja;
GRANT SELECT, INSERT, UPDATE, DELETE ON Hinded TO Opetaja;
DENY SELECT, INSERT, UPDATE, DELETE ON Logid TO Opetaja;
DENY ALTER ANY DATABASE DDL TRIGGER TO Opetaja;
DENY ALTER ANY SCHEMA TO Opetaja;


INSERT INTO Opilased (OpilaneNimi, Klass, KeskmineHinne)
VALUES
('Mari Tamm', '10A', 4.5),
('Jaan Kask', '11B', 3.8);


INSERT INTO Hinded (OpilaneID, Aine, Hinne)
VALUES
(1, 'Matemaatika', 5),
(2, 'Ajalugu', 4);

DELETE FROM Hinded
WHERE HinneID = 2;


SELECT * FROM Logid;
SELECT * FROM Logid;
select * from hinded;
SELECT * FROM Opilased

Kasutaja

use koolAB
INSERT INTO Opilased (OpilaneNimi, Klass, KeskmineHinne)
VALUES
('Koit Lemm', '11A', 4.1),
('Andrei Stechkin', '10B', 3.2);
select * from Logid
CREATE TABLE Logid2 (
LogiID INT identity(1,1) PRIMARY KEY,
Kuupaev DATETIME,
Andmed TEXT,
Kasutaja VARCHAR(100)
);

CREATE TRIGGER OpilaneLisamineKustutamine2
ON Opilased
FOR INSERT, DELETE
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Logid (Kuupaev, Andmed, Kasutaja)
SELECT
GETDATE(),
CONCAT(
'Lisatud opilane: ',inserted.OpilaneNimi,
'  klass: ', inserted.Klass,
'  keskmine hinne: ', inserted.KeskmineHinne
),
SYSTEM_USER
FROM inserted

UNION ALL

SELECT
GETDATE(),
CONCAT(
'Kustutatud opilane: ',
deleted.OpilaneNimi,
' | klass: ', deleted.Klass,
' | keskmine hinne: ', deleted.KeskmineHinne
),
SYSTEM_USER
FROM deleted;

END;

INSERT INTO Hinded (OpilaneID, Aine, Hinne)
VALUES
(3, 'Matemaatika', 5);
INSERT INTO Hinded (OpilaneID, Aine, Hinne)
VALUES
(1, 'Eesti Keel', 4);

update Hinded 
set OpilaneID = 3, Hinne = 5, Aine = 'Inglise Keel'
where HinneID = 3