Kool ise.töö - Darja Miljukova portfoolio

Minu eesmärk see töö SQL ja XAMPP teema Kool

A trigger is a stored procedure in a database that automatically invokes whenever a special event in the database occurs. For example, a trigger can be invoked when a row is inserted into a specified table or when specific table columns are updated in simple words a trigger is a collection of SQL statements with particular names that are stored in system memory. It belongs to a specific class of stored procedures that are automatically invoked in response to database server events. Every trigger has a table attached to it.

Because a trigger cannot be called directly, unlike a stored procedure, it is referred to as a special procedure. A trigger is automatically called whenever a data modification event against a table takes place, which is the main distinction between a trigger and a procedure. On the other hand, a stored procedure must be called directly.

The following are the key differences between triggers and stored procedures:

Triggers cannot be manually invoked or executed.
There is no chance that triggers will receive parameters.
A transaction cannot be committed or rolled back inside a trigger.

Why Do We Employ Triggers?

When we need to carry out some actions automatically in certain desirable scenarios, triggers will be useful. For instance, we need to be aware of the frequency and timing of changes to a table that is constantly changing. In such cases, we could create a trigger to insert the required data into a different table if the primary table underwent any changes.

1. tabel Kool, väljad: id, nimi, perekonnanimi, klass, tund, kuupaev(millal tuli)

2. logi: id, kuupaev, kasutaja, andmed, tegevus

3. lisamine, kustutamine

4. näidata, mis kell õpilased saabuvad, näidata samanimelisi õpilasi, kustutamine ja select.

Kool SQL Server

Kood

CREATE TABLE Kool (
id int primary key identity(1,1),
nimi varchar(80),
perekonnanimi varchar(80) NULL,
klass varchar(80),
tund varchar(80),
kuupaev date
);
Select * from Kool;

CREATE TABLE logi(
id int PRIMARY KEY identity(1,1),
kuupaev datetime,
kasutaja varchar(100),
andmed TEXT,
tegevus varchar(100));
select * from logi;

create trigger OpilaneLisamine
on Kool
for insert
as
insert into logi(kuupaev, kasutaja, andmed, tegevus)
select getdate(), user,
concat(inserted.nimi, ', ', inserted.perekonnanimi,  ', ', inserted.klass),
'Õpilane on lisatud'
from inserted


insert into Kool(nimi, perekonnanimi, klass)
values ('Karl','Luih','11A');
select * from Kool;
select * from logi;

insert into Kool(nimi, perekonnanimi, klass, tund, kuupaev)
values ('Mark','Linder','8B', 'keemia', '2023-11-18');
insert into Kool(nimi, perekonnanimi, klass, tund, kuupaev)
values ('Lisa','Linder','12B', 'eesti keel', '2023-10-28');
insert into Kool(nimi, perekonnanimi, klass, tund, kuupaev)
values ('Anneli','Mausel','4E', 'vene keel', '2023-09-08');
insert into Kool(nimi, perekonnanimi, klass, tund, kuupaev)
values ('Mark','Ander','8B', 'keemia', '2023-06-23');
select * from Kool;

create trigger OpilaneKustutamine
on Kool
for delete
as
insert into logi(kuupaev, kasutaja, andmed, tegevus)
select getdate(), user,
concat(deleted.nimi, ', ', deleted.perekonnanimi, ', ', deleted.klass, ', ', deleted.tund, ', ', deleted.kuupaev),
'Õpilane on kustutatud'
from deleted

delete from Kool where id=1
select * from Kool;
select * from logi;

create procedure OpilanKustutamine
@kustutaID int
as
begin
select * from Kool;

delete from Kool
where id=@kustutaID;

select * from Kool;
end
exec OpilanKustutamine @kustutaID=4

create procedure KellSelect
as
begin
select nimi, perekonnanimi, kuupaev
from Kool;
end
exec KellSelect;

create procedure NimiOtsing
@taht char(1)
as
begin
select * from Kool
where nimi like @taht + '%';
end

exec NimiOtsing 'M'

XAMPP Kool

Kood

CREATE TABLE Kool (
id int primary Key AUTO_INCREMENT,
nimi varchar(80),
perekonnanimi varchar(80) NOT NULL,
klass varchar(80),
tund varchar(80),
kuupaev date
);
Select * from Kool;




CREATE TABLE logi(
id int PRIMARY KEY AUTO_INCREMENT,
kuupaev datetime,
kasutaja varchar(100),
andmed TEXT,
tegevus varchar(100));
select * from logi;

Triggerite loomine 2 tabelid Kool

Lisa veel üks tabel õpetajad(id, nimi, perekonnanimi, idklass(fk))
Tabelid omavahel seotud – kool.idklass=opetajad.idklass
lisada esimesse tabelisse primaarne võti – klass
Koosta trigeri mis jälgib lisamine ja kustutamine
kontrolli

Microsoft SQL Server kood

CREATE TABLE Opetajad (
Id int primary key identity(1,1),
KlassId int, 
Nimi varchar(80),
PerekonnaNimi varchar(80) NOT NULL,
CONSTRAINT FK_Opetajad_Klass FOREIGN KEY (KlassId) REFERENCES Kool(id) );
select * from Opetajad;


INSERT INTO Opetajad (KlassId, Nimi, PerekonnaNimi)
VALUES
    (1, 'Olja', 'Lauri'),
    (2, 'Kelli', 'Kentri'),
	(2, 'Toomas', 'Lande'),
    (3, 'Kolja', 'Nills');
SELECT * FROM Opetajad;

CREATE TRIGGER trg_Kool_Insert
ON Kool
AFTER INSERT
AS
BEGIN
INSERT INTO logi (kuupaev, kasutaja, andmed, tegevus)
SELECT GETDATE(), USER, 'Kool: ' + nimi, 'Insert'
FROM inserted;
END;


insert into Kool(nimi, perekonnanimi, klass, tund, kuupaev)
values ('Mark','Linder','9B', 'keemia', '2023-11-18');

SELECT * FROM logi

--Ei saa teha, sest FOREIGN KEY
CREATE TRIGGER trg_Kool_Delete
ON Kool 
AFTER DELETE
AS
BEGIN
INSERT INTO logi (kuupaev, kasutaja, andmed, tegevus)
SELECT GETDATE(), USER, 'Kool: ' + nimi, 'Delete'
FROM deleted;
END;

SELECT * FROM Kool
DELETE FROM Kool
WHERE id = 1;
SELECT * FROM Kool
SELECT * FROM logi

CREATE TRIGGER trg_Opetajad_Insert
ON Opetajad
AFTER INSERT
AS
BEGIN
INSERT INTO logi (kuupaev, kasutaja, andmed, tegevus)
SELECT GETDATE(), USER, 'Opetajad: ' + Nimi, 'Insert' 
FROM inserted;
END; 


select * from Kool;
insert into Kool(nimi, perekonnanimi, klass, tund, kuupaev)
values ('Lina','Kanti','3B', 'kirjandus', '2023-01-18');

select * from Kool;
SELECT * FROM logi;

CREATE TRIGGER trg_Opetajad_Delete
ON Opetajad
AFTER DELETE
AS
BEGIN
INSERT INTO logi (kuupaev, kasutaja, andmed, tegevus)
SELECT GETDATE(), USER, 'Opetajad: ' + Nimi, 'Delete'
FROM deleted;
END;

SELECT * FROM Opetajad
DELETE FROM Opetajad
WHERE KlassId = 3;
SELECT * FROM Opetajad
SELECT * FROM logi

XAMPP kood

CREATE TABLE Opetajad (
 Id int primary key AUTO_INCREMENT,
 KlassId int, 
Nimi varchar(80),
 PerekonnaNimi varchar(80) NOT NULL,
 CONSTRAINT FK_Opetajad_Klass FOREIGN KEY (KlassId) REFERENCES Kool(id) ); 

INSERT INTO Opetajad (KlassId, Nimi, PerekonnaNimi)
VALUES
    (1, 'Olja', 'Lauri'),
    (2, 'Kelli', 'Kentri'),
	(2, 'Toomas', 'Lande');