Daria Halchenko õpimapp I portfoolio

en_USet

SQL TRIGGERID

Sissejuhatus: miks SQL Triggerid on olulised

SQL triggerid on võimsad tööriistad, millest iga andmebaasidega tegelev DBA ja arendaja peaks aru saama. Eelkõige võimaldavad need käivitajad määrata SQL-toiminguid, mis täituvad automaatselt, kui andmebaasis toimuvad konkreetsed sündmused. Näiteks saate konfi7gureerida päästikut, et värskendada kirjet ühes tabelis alati, kui kirje on sisestatud teise tabelisse.

Selles artiklis saate teada, millised on SQL-i triggerid, kuidas need töötavad ja kuidas neid oma andmebaasis kasutada. Seda juhendit järgides saab peagi SQL-is käivitajate (päästikute) asjatundjaks!

Mis on SQL-is trigger?

Päästik on andmebaasiobjekt, mis sisaldab SQL-loogikat ja käivitub automaatselt, kui toimub konkreetne andmebaasisündmus. Selgituseks aktiveerib ta vastuseks konkreetsetele sündmustele, muutes selle tõhusaks meetodiks teatud andmebaasitoimingute automatiseerimiseks.

Pealegi seostuvad SQL-i käivitajad üldjuhul kindlate tabelitega. Järelikult tabeli kustutamisel eemaldatakse ka kõik sellega seotud triggerid. Seega saab SQL-i päästikule tugineda enne või pärast järgmisi sündmusi:

  • INSERT: See sündmus toimub siis, kui tabelisse lisatakse uus rida.
  • UPDATE: See tegevus toimub tabeli olemasoleva rea muutmisel.
  • DELETE: See sündmus toimub siis, kui tabelis olev rida eemaldatakse.

Kuidas triggerid SQL-is töötavad

Kui INSERT, UPDATE või DELETE SQL-päring käivitub, tegeleb RDBMS automaatselt vastava päästiku laskmisega. See protsess mitte ainult ei aita säilitada andmete terviklikkust, vaid tagab ka, et vajalikud toimingud toimuvad järjepidevalt.

Näiteks võivad käivitajad jõustada ärireegleid või säilitada auditiradu ilma täiendava kodeerimispingutuseta. Selle tulemusena ühtlustavad nad andmebaasioperatsioonide üldist haldamist.

SQL triggerite kasutamise eelised

Lisaks pakuvad SQL triggerid mitmeid eeliseid, mis suurendavad andmebaasi funktsionaalsust. Esiteks automatiseerivad nad rutiinseid ülesandeid, mis minimeerivad inimlikke eksimusi. Teiseks tagavad nad, et andmed püsivad sidusalt seotud tabelites, parandades seeläbi andmete terviklikkust.

Peale selle võivad käivitajad aidata muudatuste auditeerimisel, logides sisse andmebaasis esinevad toimingud. Sellest tulenevalt on need väärtuslikuks vahendiks andmete modifikatsioonide jälgimiseks aja jooksul.

Järeldus

Kokkuvõtvalt võib öelda, et SQL triggerid on efektiivse andmebaasihalduse olulised komponendid. Rakendades neid automatiseeritud meetmeid, saate suurendada oma andmebaasisüsteemide funktsionaalsust ja töökindlust, mis toob kaasa tõhusamad toimingud ja rakenduse tulemuslikkuse parandamise.

CREATE DATABASE trigerTARpv23;
USE trigerTARpv23;
--loome tabeli 
CREATE TABLE toode(
toodeId int primary key identity (1,1),
toodeNimi varchar(50),
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);

Insert trigger – a trigger that tracks the inclusion of data in a table and makes a log in the table of the corresponding entry

--LISAMINE
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 
--kontroll 
--kontrollimiseks lisame toode
INSERT INTO toode(toodenimi, hind) 
VALUES ('magus õun', 10); 
SELECT * FROM toode;
SELECT * FROM logi; 

--KUSTUTAMINE
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, ', ', deleted.hind)
from deleted 
--kontroll 
--kontrollimiseks kustutame toode tabelis toode
DELETE FROM toode 
WHERE toodeId=1;
SELECT * FROM toode;
SELECT * FROM logi; 

--Uuendamine
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, ', ', deleted.hind, ' Uued andmed: ', inserted.toodenimi, ', ', inserted.hind)
from deleted INNER JOIN inserted
ON deleted.toodeId=inserted.toodeId
--kontroll 
--kontrollimiseks uuendame toode
SELECT * FROM toode;
UPDATE toode SET toodeNimi='orange melon'
WHERE toodeId=2;
SELECT * FROM toode;
SELECT * FROM logi;

SQL Server CREATE TRIGGER example

1) Create a table for logging the changes

CREATE DATABASE production;
USE production;
--1) Create a table for logging the changes
CREATE TABLE product_audits(
    change_id INT IDENTITY PRIMARY KEY,
    product_id INT NOT NULL,
    product_name VARCHAR(255) NOT NULL,
    brand_id INT NOT NULL,
    category_id INT NOT NULL,
    model_year SMALLINT NOT NULL,
    list_price DEC(10,2) NOT NULL,
    updated_at DATETIME NOT NULL,
    operation CHAR(3) NOT NULL,
    CHECK(operation = 'INS' or operation='DEL')
);
-- CATEGORIES
CREATE TABLE categories (
	category_id INT IDENTITY (1, 1) PRIMARY KEY,
	category_name VARCHAR (255) NOT NULL
);
-- BRANDS
CREATE TABLE brands (
	brand_id INT IDENTITY (1, 1) PRIMARY KEY,
	brand_name VARCHAR (255) NOT NULL
);
-- PRODUCTS
CREATE TABLE products (
	product_id INT IDENTITY (1, 1) PRIMARY KEY,
	product_name VARCHAR (255) NOT NULL,
	brand_id INT NOT NULL,
	category_id INT NOT NULL,
	model_year SMALLINT NOT NULL,
	list_price DECIMAL (10, 2) NOT NULL,
	FOREIGN KEY (category_id) 
        REFERENCES categories (category_id) 
        ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (brand_id) 
        REFERENCES brands (brand_id) 
        ON DELETE CASCADE ON UPDATE CASCADE
);

2) Creating an after DML trigger

--2) Creating an after DML trigger
CREATE TRIGGER trg_product_audit
ON products
AFTER INSERT, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO product_audits(
        product_id, 
        product_name,
        brand_id,
        category_id,
        model_year,
        list_price, 
        updated_at, 
        operation
    )
    SELECT
        i.product_id,
        product_name,
        brand_id,
        category_id,
        model_year,
        i.list_price,
        GETDATE(),
        'INS'
    FROM
        inserted i
    UNION ALL
    SELECT
        d.product_id,
        product_name,
        brand_id,
        category_id,
        model_year,
        d.list_price,
        GETDATE(),
        'DEL'
    FROM
        deleted d;
END 
--kontroll 
INSERT INTO categories (category_name) VALUES ('Electronica');
INSERT INTO brands (brand_name) VALUES ('Brand');
INSERT INTO products(
    product_name, 
    brand_id, 
    category_id, 
    model_year, 
    list_price
)
VALUES (
    'Test product',
    1,
    1,
    2018,
    599
);
SELECT * FROM product_audits;
DELETE FROM products WHERE product_id = 322;
SELECT * FROM product_audits;

XAMPP trigerid

TOODE LISAMINE

insert into logi(kasutaja, kuupaev, sisestatudAndmed)
VALUES (
user (), 
NOW(),
concat('lisatud andmed', NEW.toodeNimi, ', ', NEW.hind))
INSERT INTO toode(toodenimi, hind) 
VALUES ('magus õun', 10); 
SELECT * FROM toode;
SELECT * FROM logi;

DESCRIPTION OF PRODUCTS

insert into logi(kasutaja, kuupaev, sisestatudAndmed)
VALUES (
user (), 
NOW(),
concat('kustutatud andmed', OLD.toodeNimi, ', ', OLD.hind))
DELETE FROM toode 
WHERE toodeId=1;
SELECT * FROM toode;
SELECT * FROM logi;

FUTURE OF PRODUCTS

insert into logi(kasutaja, kuupaev, sisestatudAndmed)
VALUES (
user (), 
NOW(),
concat('vanad andmed: ', OLD.toodeNimi, ', ', OLD.hind, ' Uued andmed: ', NEW.toodenimi, ', ', NEW.hind))

Task “animals”

Database name – loomadAndmebaas

--LOOMADANDMEBAAS
CREATE DATABASE loomadAndmebaas;
USE loomadAndmebaas;

Tabelinimi, andmete sisestamiseks ja väljade nimed tabelis, nt. Loo tabel loomad (id, loomaliik ,nimi, vanus, värv)

--LOOMAD
CREATE TABLE loomad(
id int primary key identity (1,1),
loomaliik varchar(50),
nimi varchar(50),
vanus int,
varv varchar(50));

Loo tabel trigerite töö salvestamiseks. Näiteks tabel logi (logiID, kuupaev, andmed, kasutaja)

--loome tabeli, mis täidab triger 
CREATE TABLE logi(
logiID int primary key identity(1,1),
kasutaja varchar(100),
kuupaev datetime,
andmed text);

Trigerinimed ja mida triger peab tegema seoses eelnevalt loodud tabeliga (INSERT, UPDATE, DELETE)

INSERT

--INSERT
create trigger loomadLisamine
on loomad 
for insert
as
insert into logi(kasutaja, kuupaev, andmed)
select
user, 
getdate(),
concat('lisatud andmed', inserted.loomaliik, ', ', inserted.nimi, ', ', inserted.vanus, ', ', inserted.varv)
from inserted 
SELECT * FROM logi;

UPDATE

--UPDATE
create trigger loomadUuendamine
on loomad 
for UPDATE 
as
insert into logi(kasutaja, kuupaev, andmed)
select 
user, 
getdate(),
concat('vanad andmed: ', deleted.loomaliik, ', ', deleted.nimi, ', ', deleted.vanus, ', ', deleted.varv, ' Uued andmed: ',inserted.loomaliik, ', ', inserted.nimi, ', ', inserted.vanus, ', ', inserted.varv)
from deleted INNER JOIN inserted
ON deleted.id=inserted.id
SELECT * FROM logi;

DELETE

--DELETE
create trigger loomadKustutamine
on loomad
for DELETE 
as
insert into logi(kasutaja, kuupaev, andmed)
select 
user, 
getdate(),
concat('kustutatud andmed',deleted.loomaliik, ', ', deleted.nimi, ', ', deleted.vanus, ', ', deleted.varv)
from deleted

Kasutajanimi, kes saab töötada ainult varem loodud tabeliga (Kasutajanimi – loomaarst. Parool – 12345)

Kasutaja õigused

GRANT INSERT, SELECT, UPDATE, DELETE ON loomad TO loomaarst;

Kasutajaõiguste kontroll

INSERT

SELECT * FROM loomad 
INSERT INTO loomad(loomaliik, nimi, vanus, varv)
VALUES ('karu', 'Valeria', 1, 'valge'),
('ahv', 'Bob', 2, 'prune'),
('delfiin', 'Roxy', 5, 'sinine'),
('kass', 'Murzik', 3, 'punakollane'),
('koer', 'Dobi', 4, 'valge');

UPDATE

SELECT * FROM loomad;
UPDATE loomad SET varv ='pruun'
WHERE id=5;
SELECT * FROM loomad;

DELETE

DELETE FROM loomad 
WHERE id =1;
SELECT * FROM loomad;

Kasutajaõiguste kontroll tabelile logi

LOGI tabeli käivitamine

XAMPP

Andmebaasinimi – loomadAndmebaas

Tabelinimi, andmete sisestamiseks ja väljade nimed tabelis, nt. Loo tabel loomad (id, loomaliik ,nimi, vanus, värv)

Loo tabel trigerite (Päästikute) töö salvestamiseks. Näiteks tabel logi (logiID, kuupaev, andmed, kasutaja)

Trigerinimed ja mida triger peab tegema seoses eelnevalt loodud tabeliga (INSERT, UPDATE, DELETE)

LOOMAD LISAMINE

LOOMAD UUENDAMINE

LOOMAD KUSTUTAMINE

Kasutajanimi, kes saab töötada ainult varem loodud tabeliga (Kasutajanimi – loomaarst. Parool – 12345)

Kasutajaõiguste kontroll

INSERT

UPDATE

DELETE

Kasutajaõiguste kontroll tabelile logi (Kasutajanimi – loomaarst)

LOGI tabeli käivitamine

SQL Server Trigerid kahe seotud tabelite põhjal

CREATE DATABASE loomadAndmebaas;
USE loomadAndmebaas;

--LOOMAD
CREATE TABLE loomad(
id INT PRIMARY KEY IDENTITY(1,1),
loomaliik VARCHAR(50),
nimi VARCHAR(50),
vanus INT,
varv VARCHAR(50)
);


INSERT INTO loomad (loomaliik, nimi, vanus, varv)
VALUES ('karu', 'Valeria', 1, 'valge'),
('ahv', 'Bob', 2, 'prune');

--LOGI
CREATE TABLE logi(
id INT PRIMARY KEY IDENTITY(1,1),
aeg DATETIME,
toiming VARCHAR(100),
kasutaja VARCHAR(200),
andmed TEXT
);

--OMANIK
CREATE TABLE omanik(
    omanikID INT PRIMARY KEY IDENTITY(1,1),
    nimi VARCHAR(100) UNIQUE,
    perekonnanimi VARCHAR(50),
    sunniaeg DATE
);
INSERT INTO omanik (nimi, perekonnanimi, sunniaeg)
VALUES ('Daria', 'Halchenko', '2006-10-06'),
('Valeria', 'Allik', '2006-03-25');

ALTER TABLE loomad ADD omanikID int;
ALTER TABLE loomad ADD CONSTRAINT fk_omanik
FOREIGN KEY (omanikID) REFERENCES omanik(omanikID);

Select * from omanik;
select * from loomad;

Loomad Lisamine

CREATE TRIGGER loomadLisamine
ON loomad
FOR INSERT 
AS 
INSERT INTO logi(kasutaja, aeg, toiming, andmed)
SELECT USER, GETDATE(), 'loomad on lisatud',
CONCAT(i.loomaliik, ', ', o.nimi)
FROM inserted i
INNER JOIN omanik o
ON o.omanikID = i.omanikID;


--kontrollimiseks
INSERT INTO loomad (loomaliik, nimi, vanus, varv, omanikID)
VALUES ('karu', 'Marta', 3, 'pruun', 1),
('delfiin', 'Roxy', 5, 'sinine', 2),
('kass', 'Murzik', 3, 'punakollane', 1),
('koer', 'Dobi', 4, 'valge', 2);

SELECT * FROM loomad;
SELECT * FROM logi;

Loomad Kustutamine

create trigger loomadKustutamine
on loomad
for delete
as insert into logi(kasutaja, aeg, toiming, andmed)
select user, getdate(), 'Loomad on kustutatud', concat(deleted.loomaliik, ', ', o.nimi)
from deleted
inner join omanik o
on deleted.omanikID=o.omanikID

--kontroll
delete from loomad 
where id=1;

SELECT * FROM loomad;
SELECT * FROM logi;

LoomadUuendamine

create trigger loomadUuendamine
on loomad
for update
as 
insert into logi(kasutaja, aeg, toiming, andmed)
select user, getdate(), 'Loomad on uuendatud', concat('Vanad andmed : ', deleted.loomaliik, ', ', o1.nimi,
'Uuendanud andmed : ', inserted.loomaliik, ', ', o2.nimi)
from deleted
inner join inserted on deleted.id=inserted.id
inner join omanik o1 on deleted.omanikID=o1.omanikID
inner join omanik o2 on inserted.omanikID=o2.omanikID;
update loomad set loomaliik='jänes', omanikID=5
where id=5;

select * from loomad;
select * from logi;

XAMPP Trigerid kahe seotud tabelite põhjal

Loomad Lisamine

Loomad Kustutamine

LoomadUuendamine

Find out more about SQL requests.