Blog

Trigger

Loon andmebaas ja tabelid:

create database trigerViblyy;
use trigerViblyy;
CREATE TABLE toode(
toodeID int primary key identity(1,1),
toodeNimi varchar(50),
hind int);
CREATE TABLE logi(
id int primary key identity(1,1),
kasutaja varchar(100),
kuupaev datetime,
sisestatudAndmed text);

INSERT TRIGER -triger, mis jälgib andmete lisamine tabelisse ja teeb vastava kirje tabelis logi.

CREATE TRIGGER toodeLisamine
ON toode -- tabel, mis jalgitakse
FOR INSERT
AS 
INSERT INTO logi(kasutaja, kuupaev, sisestatudAndmed)
SELECT
USER,
GETDATE(),
CONCAT('lisatud andmed', inserted.toodenimi, ', ',inserted.hind)
FROM inserted

Kontroll:

INSERT INTO toode(toodenimi, hind)
VALUES ('ilus pirn', 30);
SELECT * FROM toode;
SELECT * FROM logi;

DELETE TRIGGER -kustutamise trigger.

CREATE TRIGGER toodeKustutamine
ON toode -- tabel, mis jalgitakse
FOR DELETE
AS 
INSERT INTO logi(kasutaja, kuupaev, sisestatudAndmed)
SELECT
USER,
GETDATE(),
CONCAT('kustutatud andmed: ', deleted.toodenimi, ', ',deleted.hind)
FROM deleted

Kontroll:

DELETE FROM toode
WHERE toodeID=1;
SELECT * FROM toode;
SELECT * FROM logi;

UPDATE TRIGGER – uuendamise trigger

CREATE TRIGGER toodeUuendamine
ON toode -- tabel, mis jalgitakse
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

UPDATE toode SET toodeNimi='orange melon'
WHERE toodeID=2;
SELECT * FROM toode;
SELECT * FROM logi;

Loon uued tabelid

CREATE TABLE categories (
	category_id INT IDENTITY (1, 1) PRIMARY KEY,
	category_name VARCHAR (255) NOT NULL
);
CREATE TABLE brands (
	brand_id INT IDENTITY (1, 1) PRIMARY KEY,
	brand_name VARCHAR (255) NOT NULL
);
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
);

Lisa kommentaar