SQL TRIGERID

Sissejuhatus

Päästikud, tuntud ka kui triggerid, on SQL Serveris ja muudes andmebaasisüsteemides olulised mehhanismid, mis võimaldavad automaatselt käivitada eeldefineeritud tegevusi vastusena andmebaasi sündmustele, nagu andmete lisamine, muutmine või kustutamine. XAMPP on populaarne avatud lähtekoodiga tarkvara, mis võimaldab kasutajatel hõlpsasti seadistada ja hallata oma kohalikke arendusservereid, sealhulgas MySQL-i, mis toetab päästikute loomist.

Mis on Päästikud?

Päästikud on SQL-käskude kogumid, mis aktiveeruvad automaatselt, kui toimub kindel sündmus. Need võivad olla kas:

BEFORE Triggers: Käivituvad enne andmebaasi sündmust, võimaldades andmete valideerimist või muutmist enne nende salvestamist.

AFTER Triggers: Käivituvad pärast andmebaasi sündmust, näiteks pärast andme lisamist või muutmist.

xampp – päästik

create database TrigerTARpv23
use TrigerTARpv23
--loome tabeli
create table Toode(
toodeId int primary key identity(1,1),
toodeNimi varchar(50),
hind int,
);
select * from Toode;
--loome tabeli mis täidab triger
CREATE table logi(
id int primary key identity(1,1),
kasutaja varchar(100),
kuupaev dateTime,
sisestatudAndmed text,
);
select * from logi

INSERT TRIGGER – triger, mis jälgib andmete lisamist tabelisse ja teeb vastava kirje tabelis logi. 

CREATE TRIGGER toodeLisamine
ON toode -- table being monitored
FOR insert
AS
BEGIN
INSERT INTO logi (kasutaja, kuupaev, sisestatudAndmed)
SELECT
USER,
GETDATE(),
CONCAT('Lisatud andmed: ', inserted.toodeNimi, ', ' ,
 inserted.hind)
FROM inserted;
END;
--kontroll
--kontrollimiseks lisame toode
insert into toode(toodeNimi, hind)
Values('Ilus pirn', 30)
select * from toode;
select * from logi;

XAMPP

INSERT INTO logi (kasutaja, kuupaev, sisestatudAndmed)
VALUES
USER(),
NOW(),
CONCAT('Lisatud andmed: ', NEW.toodeNimi, ', ' ,NEW.hind)
END;

kontroll

DELETE TRIGGER – trigger, mis jälgib andmete kustutamist tabelisse ja teeb vastava kirje tabelis logi. 

CREATE TRIGGER toodeKustutamine
ON toode -- table being monitored
FOR delete
AS
BEGIN
INSERT INTO logi (kasutaja, kuupaev, sisestatudAndmed)
SELECT
USER,
GETDATE(),
CONCAT('Kustutatud andmed: ', deleted.toodeNimi, ', ', 
deleted.hind)
FROM deleted;
END;
--kontrollimiseks kustutame toode tabelis toode
delete from toode
where toodeId=1;
select*from toode;
select*from logi;

XAMPP

INSERT INTO logi (kasutaja, kuupaev, sisestatudAndmed)
VALUES(
USER(),
NOW(),
CONCAT('Kustutatud andmed: ', OLD.toodeNimi, ', ' ,OLD.hind))

kontroll

TRIGGER UPDATE – trigger, mis jälgib andmete uuendamist tabelisse ja teeb vastava kirje tabelis logi. 

CREATE TRIGGER toodeUpdate
ON toode
FOR delete
AS
BEGIN
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
END;
--kontrollimiseks uuendame toode
select * from toode;
update toode set toodeNimi='orange melon'
where toodeId=2;
select * from toode;
select * from logi;

XAMPP

INSERT INTO logi (kasutaja, kuupaev, sisestatudAndmed)
VALUES(
USER(),
NOW(),
CONCAT('Kustutatud andmed: ', OLD.toodeNimi, ', ' ,OLD.hind,
      'Uued andmed',', ', NEW.toodeNimi,', ',NEW.hind))

Kontroll

SQL Server CREATE TRIGGER

CREATE TABLE product_audits (
    change_id INT IDENTITY(1,1) 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 DECIMAL(10,2) NOT NULL,
    updated_at DATETIME NOT NULL,
    operation CHAR(3) NOT NULL,
    CHECK(operation IN ('INS', 'DEL'))
);
CREATE TABLE products (
    product_id INT PRIMARY KEY IDENTITY(1,1),
    product_name VARCHAR(100) NOT NULL,
    brand_id INT NOT NULL,
    category_id INT NOT NULL,
    model_year SMALLINT,
    list_price DECIMAL(10,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,
        i.product_name,
        i.brand_id,
        i.category_id,
        i.model_year,
        i.list_price,
        GETDATE(),
        'INS'
    FROM
        inserted i
    UNION ALL
    SELECT
        d.product_id,
        d.product_name,
        d.brand_id,
        d.category_id,
        d.model_year,
        d.list_price,
        GETDATE(),
        'DEL'
    FROM
        deleted d;
END;

 Testing the trigger

INSERT INTO products (
    product_name, 
    brand_id, 
    category_id, 
    model_year, 
    list_price
)
VALUES (
    'Test product',
    1,
    1,
    2018,
    599.00
);
SELECT 
    * 
FROM 
    product_audits;

Kontroll

DELETE FROM products
WHERE 
    product_id = 322;