{"id":138,"date":"2025-06-02T11:19:23","date_gmt":"2025-06-02T11:19:23","guid":{"rendered":"https:\/\/darjamalkova23.thkit.ee\/wpp\/?page_id=138"},"modified":"2025-06-02T11:19:23","modified_gmt":"2025-06-02T11:19:23","slug":"sql-trigerid","status":"publish","type":"page","link":"https:\/\/darjamalkova23.thkit.ee\/wpp\/sql-trigerid\/","title":{"rendered":"SQL TRIGERID"},"content":{"rendered":"\n

protsess, mille abil tema sisse kirjutanud tegevused automaatselt k\u00e4ivitakse.<\/p>\n\n\n\n

create database TiTtriger;
use TiTtriger;
\u2013tabel, mida automaatselt t\u00e4idab triger
CREATE TABLE logi(
id int primary key identity(1,1),
tegevus varchar(25),
kasutaja varchar(25),
aeg datetime,
andmed TEXT
)<\/bdo>
\u2013tabel, millega t\u00f6\u00f6tab kasutaja
CREATE TABLE puud(
puuID int primary key identity(1,1),
puuNimi varchar(25),
pikkus int,
aasta int,
)
INSERT INTO puud(puuNimi, pikkus, aasta)
VALUES (\u2018Tamm\u2019, 200, 100);
SELECT * FROM puud;
\u2013Triger, mis j\u00e4lgib tabeli puud t\u00e4itmine(lisamine)
CREATE TRIGGER puuLisamine
ON puud
FOR INSERT
AS
INSERT INTO logi(kasutaja, tegevus, aeg, andmed)
SELECT
SYSTEM_USER,
\u2018puu on lisatud\u2019,
GETDATE(),
CONCAT (inserted.puuNimi, \u2018,\u2019 ,inserted.pikkus, \u2018,\u2019 ,inserted.aasta)
FROM inserted;
\u2013kontroll
INSERT INTO puud(puuNimi, pikkus, aasta)
VALUES (\u2018vaher\u2019, 222, 200);
SELECT * FROM puud;
SELECT * FROM logi;
DROP TRIGGER puuLisamine;<\/p>\n\n\n\n

\u2013triger,mis j\u00e4lgib tabelis kustutamine
CREATE TRIGGER puuKustutamine
ON puud
FOR DELETE
AS
INSERT INTO logi(kasutaja, tegevus, aeg, andmed)
SELECT
SYSTEM_USER,
\u2018puu on kustutatud\u2019,
GETDATE(),
CONCAT (deleted.puuNimi, \u2018,\u2019 ,deleted.pikkus, \u2018,\u2019 ,deleted.aasta)
FROM deleted;
DELETE FROM puud WHERE puuID = 5;
SELECT * FROM puud;
SELECT * FROM logi;<\/p>\n\n\n\n

\u2013triger, mis j\u00e4lgib tabelis uuendamine
CREATE TRIGGER puuUuendamine
ON puud
FOR UPDATE
AS
INSERT INTO logi(kasutaja, tegevus, aeg, andmed)
SELECT
SYSTEM_USER,
\u2018puu on uuendatud\u2019,
GETDATE(),
CONCAT (
\u2018vana puu info \u2013 \u2018, deleted.puuNimi, \u2018,\u2019 ,deleted.pikkus, \u2018,\u2019 ,deleted.aasta,
\u2018uus puu info \u2013 \u2018, inserted.puuNimi, \u2018,\u2019 ,inserted.pikkus, \u2018,\u2019 ,inserted.aasta)
FROM deleted INNER JOIN inserted
ON deleted.puuID=inserted.puuID;<\/p>\n\n\n\n

\u2013kontroll
UPDATE puud SET pikkus=5555, aasta=1890
WHERE puuID=4;
SELECT * FROM puud;
SELECT * FROM logi;<\/p>\n\n\n\n

\"\"<\/figure>\n","protected":false},"excerpt":{"rendered":"

protsess, mille abil tema sisse kirjutanud tegevused automaatselt k\u00e4ivitakse. create database TiTtriger;use TiTtriger;\u2013tabel, mida automaatselt t\u00e4idab trigerCREATE TABLE logi(id int primary key identity(1,1),tegevus varchar(25),kasutaja varchar(25),aeg datetime,andmed TEXT)\u2013tabel, millega t\u00f6\u00f6tab kasutajaCREATE TABLE puud(puuID int primary key identity(1,1),puuNimi varchar(25),pikkus int,aasta int,)INSERT INTO puud(puuNimi, pikkus, aasta)VALUES (\u2018Tamm\u2019, 200, 100);SELECT * FROM puud;\u2013Triger, mis j\u00e4lgib tabeli puud t\u00e4itmine(lisamine)CREATE TRIGGER […]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-138","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/darjamalkova23.thkit.ee\/wpp\/wp-json\/wp\/v2\/pages\/138","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/darjamalkova23.thkit.ee\/wpp\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/darjamalkova23.thkit.ee\/wpp\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/darjamalkova23.thkit.ee\/wpp\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/darjamalkova23.thkit.ee\/wpp\/wp-json\/wp\/v2\/comments?post=138"}],"version-history":[{"count":1,"href":"https:\/\/darjamalkova23.thkit.ee\/wpp\/wp-json\/wp\/v2\/pages\/138\/revisions"}],"predecessor-version":[{"id":140,"href":"https:\/\/darjamalkova23.thkit.ee\/wpp\/wp-json\/wp\/v2\/pages\/138\/revisions\/140"}],"wp:attachment":[{"href":"https:\/\/darjamalkova23.thkit.ee\/wpp\/wp-json\/wp\/v2\/media?parent=138"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}