Triger
Protses, millega abil kõik sisse kirjutatud tegevused tehakse automaselt.
Näiteks, Trigeri abil kasutaja saab jälgida mis tegevused oli tehtud andmebaasis (insert, update, delete)
Triger mis jälgib lisatud tabelisse andmed
XAMPP
create table guest (
id int primary key AUTO_INCREMENT,
first_name varchar(20),
last_name varchar(20) null,
member_since date
);
create table logi(
id int primary key AUTO_INCREMENT,
kuupaev datetime,
kasutaja varchar(100),
andmed TEXT,
tegevus varchar(100))
INSERT INTO guest(first_name, last_name, member_since)
VALUES
('Ivan', 'Smirnov', '2018-05-15'),
('Natalya', 'Popova', '2019-02-28'),
('Sergei', 'Kuznetsov', '2020-11-10'),
('Olga', 'Vasilieva', '2017-09-22'),
('Dmitri', 'Romanov', '2021-07-04');
delete from guest
where id=1;
select * from guest;
select* from logi;
DELETE
INSERT INTO logi(kuupaev, kasutaja, andmed, tegevus)
VALUES (NOW(), user(),
Concat(OLD.last_name, ', ', old.first_name, ', ', OLD.member_since),
'guest on kustutatud')
INSERT
INSERT INTO logi(kuupaev, kasutaja, andmed, tegevus)
VALUES (NOW(), user(),
Concat(NEW.last_name, ', ', new.first_name, ', ', new.member_since),
'guest on lisatud')
UPDATE
INSERT INTO logi(kuupaev, kasutaja, andmed, tegevus)
VALUES (NOW(), user(),
Concat('Vanad andmed: ', old.last_name, ', ', old.first_name, ', ', old.member_since,
'\n Uued andmed: ', new.last_name, ', ', new.first_name, ', ', new.member_since),
'guest on uuendatud')
ÜLESANNE
create table room(
id int primary key AUTO_INCREMENT,
number varchar(10),
name_ varchar(40),
status_ varchar(10),
smoke bit
);
SQL SERVER
create table guest (
id int primary key identity(1,1),
first_name varchar(20),
last_name varchar(20) null,
member_since date
);
create table logi(
id int primary key identity(1,1),
kuupaev datetime,
kasutaja varchar(100),
andmed TEXT,
tegevus varchar(100))
create trigger guestLisamine
on guest
for insert
as
INSERT INTO logi(kuupaev, kasutaja, andmed, tegevus)
select getdate(), user, concat(inserted.first_name, ', ', inserted.last_name), 'guest on lisatud'
from inserted
--kontroll
INSERT INTO guest (first_name, last_name, member_since)
VALUES ('Ivan', 'Smirnov', '2018-05-15');
select * from guest
select * from logi
create trigger guestKustutamine
on guest
for delete
as
INSERT INTO logi(kuupaev, kasutaja, andmed, tegevus)
select getdate(), user, concat(deleted.first_name, ', ', deleted.last_name), 'guest on kustatud'
from deleted
--kontroll
delete from guest where id=1
select * from guest;
select * from logi;
create trigger guestUuendamine
on guest
for update
as
INSERT INTO logi(kuupaev, kasutaja, andmed, tegevus)
select getdate(), user, concat('vanad - ', deleted.first_name, ', ', deleted.last_name, ' uued - ', inserted.first_name, ', ', inserted.last_name), 'guest on uuendatud'
from deleted inner join inserted
on deleted.id=inserted.id
--kontroll
select * from guest;
update guest set first_name='Luca'
where id=3;
select * from guest;
select * from logi;
ÜLESANNE
create table room(
id int primary key identity(1,1),
number varchar(10),
name_ varchar(40),
status_ varchar(10),
smoke bit,
);
create trigger roomLisamine
on room
for insert
as
INSERT INTO logi(kuupaev, kasutaja, andmed, tegevus)
select getdate(), user, concat(inserted.number, ', ', inserted.name_, ', ', inserted.status_, ', ', inserted.smoke), 'guest on lisatud'
from inserted
--kontroll
INSERT INTO room (number, name_, status_, smoke)
VALUES ('102', 'Sunny Suite', 'Vacant', 0)
select * from room
select * from logi
create trigger roomKustutamine
on room
for delete
as
INSERT INTO logi(kuupaev, kasutaja, andmed, tegevus)
select getdate(), user, concat(deleted.number, ', ', deleted.name_, deleted.status_, ', ', deleted.smoke), 'guest on kustatud'
from deleted
--kontroll
delete from room where id=1
select * from room;
select * from logi;
create trigger roomUuendamine
on room
for update
as
INSERT INTO logi(kuupaev, kasutaja, andmed, tegevus)
select getdate(), user, concat('vanad - ', deleted.number, ', ', deleted.name_, deleted.status_, ', ', deleted.smoke, ' uued - ', inserted.number, ', ', inserted.name_, ', ', inserted.status_, ', ', inserted.smoke), 'guest on uuendatud'
from deleted inner join inserted
on deleted.id=inserted.id
--kontroll
select * from room;
update room set number='911'
where id=2;
select * from room;
select * from logi;lisamine
create trigger roomLisamine
on room
for insert
as
INSERT INTO logi(kuupaev, kasutaja, andmed, tegevus)
select getdate(), user, concat(inserted.number, ', ', inserted.name_, ', ', inserted.status_, ', ', inserted.smoke), 'guest on lisatud'
from inserted
--kontroll
INSERT INTO room (number, name_, status_, smoke)
VALUES ('102', 'Sunny Suite', 'Vacant', 0)
select * from room
select * from logi
kustutamine
create trigger roomKustutamine
on room
for delete
as
INSERT INTO logi(kuupaev, kasutaja, andmed, tegevus)
select getdate(), user, concat(deleted.number, ', ', deleted.name_, deleted.status_, ', ', deleted.smoke), 'guest on kustatud'
from deleted
--kontroll
delete from room where id=1
select * from room;
select * from logi;
uuendamine
create trigger roomUuendamine
on room
for update
as
INSERT INTO logi(kuupaev, kasutaja, andmed, tegevus)
select getdate(), user, concat('vanad - ', deleted.number, ', ', deleted.name_, deleted.status_, ', ', deleted.smoke, ' uued - ', inserted.number, ', ', inserted.name_, ', ', inserted.status_, ', ', inserted.smoke), 'guest on uuendatud'
from deleted inner join inserted
on deleted.id=inserted.id
--kontroll
select * from room;
update room set number='911'
where id=2;
select * from room;
select * from logi;
PROTSEDURID
--lisamine ja SELECT
create procedure lisaGuest
@fname varchar(100),
@lname varchar(100),
@sinse date
as
begin
insert into guest(first_name, last_name, member_since)
values(@fname, @lname, @sinse);
select * from guest;
select * from logi;
end;
exec lisaguest 'Denis', 'Gorjunov', '2002-10-9';
--kustutamine ja SELECT
create procedure kustutaGuest
@ID int
as
begin
select * from guest;
delete from guest
where @id=id;
select * from guest;
select * from logi;
end;
exec kustutaGuest 3;