XAMPP

CREATE TABLE guest(
id int not null PRIMARY KEY AUTO_INCREMENT,
first_name varchar(80),
last_name varchar(80) NULL,
member_since date
);

CREATE TABLE logi(
id int PRIMARY key AUTO_INCREMENT,
kuupaev datetime,
kasutaja varchar(100),
andmed TEXT,
tegevus varchar(100)
);

Protsess, millega abi kõik sisse kirjutatud tegevused tehakse automaatselt.
Näiteks, Trigeri abi kasutaja saab jälgida mis tegevused oled tehtud andmebaasis.
(INSERT, UPDATE, DELETE)

Päästik – triger

Triger mis jälgib lisatud tabelisse andmed.

Näieks insert

insert into logi(kuupaev, kasutaja, andmed, tegevus)
values(NOW(), USER(),
Concat(NEW.last_name, ', ',
NEW.first_name, ', ',
NEW.member_since
), 'guest on lisaud')

Näideks delete

insert into logi(kuupaev, kasutaja, andmed, tegevus)
values(NOW(), USER(),
Concat(OLD.last_name, ', ',
OLD.first_name, ', ',
OLD.member_since
), 'guest on kustutatud')

Näiteks uuendamine

insert into logi(kuupaev, kasutaja, andmed, tegevus)
values(NOW(), USER(),
Concat(OLD.last_name, ', ',
OLD.first_name, ', ',
OLD.member_since, ' UUED andmed ',
NEW.last_name, ', ',
NEW.first_name, ', ',
NEW.member_since
), 'guest on uuendatud')

Iseseisev töö

CREATE TABLE room(id int not null primary key AUTO_INCREMENT,number varchar(10),name varchar(40),status varchar(10),smoke bit);

Näideks lisamine

insert into logi(kuupaev, kasutaja, andmed, tegevus)
values(NOW(), USER(),
Concat(NEW.number, ‘, ‘,
NEW.name, ‘, ‘,
NEW.status, ‘, ‘,
NEW.smoke
), ‘room on lisaud’)

Näideks kustutamine

insert into logi(kuupaev, kasutaja, andmed, tegevus)
values(NOW(), USER(),
Concat(OLD.number, ', ',
OLD.name, ', ',
OLD.status, ', ',
OLD.smoke
), 'room on kustutatud')

Näideks uuendamine

insert into logi(kuupaev, kasutaja, andmed, tegevus)
values(NOW(), USER(),
Concat(OLD.number, ‘, ‘,
OLD.name, ‘, ‘,
OLD.status, ‘, ‘,
OLD.smoke, ‘\n Uued andmed ‘,
NEW.number, ‘, ‘,
NEW.name, ‘, ‘,
NEW.status, ‘, ‘,
NEW.smoke
), ‘room on uuendatud’)

SQL Server

CREATE TABLE guest(
id int not null PRIMARY KEY identity(1,1),
first_name varchar(80),
last_name varchar(80) NULL,
member_since date
);

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

Näideks lisamine

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, ', ',
inserted.member_since
), 'guest on lisaud'
FROM inserted

--kontroll
insert into guest(first_name, last_name, member_since)
values('Apollon', 'Zigmund', '2001-09-11')
SELECT * from guest;
SELECT * from logi

Näideks kustutmine

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, ‘, ‘,
deleted.member_since
), ‘guest on kstutaud’
FROM deleted
–kontroll
delete from guest where id=3
SELECT * from guest;
SELECT * from logi

Näiteks uuendamine

CREATE TRIGGER guestuuendamine
ON guest
FOR update
AS
insert into logi(kuupaev, kasutaja, andmed, tegevus)
SELECT
GETDATE(), USER,
Concat(
deleted.first_name, ', ',
deleted.last_name, ', ',
deleted.member_since, '\n Uued andmed ',
inserted.first_name, ', ',
inserted.last_name, ', ',
inserted.member_since
), 'guest on uuendatud'
FROM deleted inner join inserted
on deleted.id=inserted.id

Iseseisev töö

Näideks lisamine

CREATE TABLE room(id int not null 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 lisaud'
FROM inserted

insert into room(number, name, status, smoke)
values('69', 'Kintchen', 'Closed', 1)
SELECT * from room;
SELECT * from logi

Näideks 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 kustutatud'
FROM deleted

delete from room where id=2
SELECT * from room;
SELECT * from logi

Näideks uudamine

CREATE TRIGGER roomuuendamine
ON room
FOR update
AS
insert into logi(kuupaev, kasutaja, andmed, tegevus)
SELECT
GETDATE(), USER,
Concat(
deleted.number, ', ',
deleted.name, ', ',
deleted.status, ', ',
deleted.smoke, '\n Uued andmed ',
inserted.number, ', ',
inserted.name, ', ',
inserted.status,
inserted.smoke
), 'guest on uuendatud'
FROM deleted inner join inserted
on deleted.id=inserted.id

update room
set name='AstralStep'
where id=1
SELECT * from room;
SELECT * from logi

Procedurid

Lisamine

Create procedure guestLisa
@first_name varchar(80),
@last_name varchar(80),
@member_since DATE
as
begin
Insert into guest(first_name, last_name, member_since)
values(@first_name,@last_name,@member_since);
Select * from guest;
select * from logi
end;

--kontroll
exec guestLisa 'John', 'skala', '2001/09/11';

Kustutamine

Create procedure guestDel
@id int
as
begin
Select * from guest;
delete guest
where @id=id;
Select * from guest;
select * from logi;
end;
--kontroll
exec guestDel 1;

Külaliste arvu

Create procedure guestArvu
as
begin
Select Count(id) as KülalisteArvu
from guest
end;
–kontroll
exec guestArvu;