{"id":466,"date":"2026-04-13T09:20:43","date_gmt":"2026-04-13T06:20:43","guid":{"rendered":"https:\/\/olegberezevski.wordpress.com\/?page_id=466"},"modified":"2026-04-13T09:20:43","modified_gmt":"2026-04-13T06:20:43","slug":"sql-trigger","status":"publish","type":"page","link":"https:\/\/olegberezevski.eu\/wp\/sql-trigger\/","title":{"rendered":"SQL Trigger"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">SQL triger on spetsiaalne andmebaasi objekt, mis k\u00e4ivitab automaatselt teatudn s\u00fcndmus ()nt INSERT, UPDATE, DELETE) triggerid aitavad automatiseerida protsessid andmebassis<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">SQL Server.<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Loome andmebaasi ja kaks tabelit. 1. Tabel &#8211; kuhu kasutaja sisestab andmeid (Linnad). 2. tabel &#8211; logi, kus trigger lisab j\u00e4lgitud andmeid<\/li>\n<\/ol>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\ncreate database OlegBerez;\nuse OlegBerez;\n\n--tabel Linnad\ncreate table Linnad\n(\n\tlinnID int primary key identity(1,1),\n\tlinnaNimi varchar(50) unique,\n\trahvaarv int not null\n);\n\n--tabel Logi\ncreate table Logi\n(\n\tlogID int primary key identity(1,1),\n\tkuupaev datetime,\n\tandmed text\n);\n\n\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">2. trigeri loomine.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">INSERT TRIGGER, mis j\u00e4lgib andmete lisamine tabelisse linnad.<\/h3>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\ncreate trigger linnaLisamine\nON Linnad\nFOR insert\nas \ninsert into Logi(kuupaev, andmed)\nselect \ngetdate(), \nCONCAT\n(\n\t&#039; | Lisatud linn: &#039;, inserted.linnaNimi,\n\t&#039; | rahvaarv: &#039;, inserted.rahvaarv, \n\t&#039; | Id: &#039;, inserted.linnID\n)\nfrom inserted;\n\n--Kontrollime\ninsert into Linnad(linnaNimi, rahvaarv)\nvalues (&#039;Narva&#039;, 487932);\nselect * from Linnad;\nselect * from Logi;\n<\/pre><\/div>\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/olegberezevski.wordpress.com\/wp-content\/uploads\/2026\/04\/7e701047-7d21-4c7a-b510-6aa9f749653d.png?w=537\" alt=\"\" class=\"wp-image-472\" \/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">DELETE triger, mis j\u00e4lgib andmete kustutamine tabelist linnad.<\/h3>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\n-- DELETE triger\ncreate trigger linnaKustutamine\non Linnad\nfor delete\nas \ninsert into Logi(kuupaev, andmed)\nselect \ngetdate(), \nCONCAT\n(\n\t&#039; | Kustutatud linn: &#039;, deleted.linnaNimi,\n\t&#039; | rahvaarv: &#039;, deleted.rahvaarv, \n\t&#039; | Id: &#039;, deleted.linnID\n)\nfrom deleted;\n\ndelete from Linnad where linnID=1;\nselect * from Linnad;\nselect * from Logi;\n<\/pre><\/div>\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/olegberezevski.wordpress.com\/wp-content\/uploads\/2026\/04\/d0d33f17-6894-44d2-a8e2-b7b0b176fca7.png?w=480\" alt=\"\" class=\"wp-image-474\" \/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">UPDATE Trigger, mis j\u00e4lgib andmete uuendamine tabelis linnad.<\/h3>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\n--UPDATE TRIGGER\ncreate trigger linnaUuendamine\non Linnad\nfor update\nas \ninsert into Logi(kuupaev, andmed)\nselect \ngetdate(), \nCONCAT\n(\n\t&#039; | Vana linna andmed: &#039;, d.linnaNimi,\n\t&#039; | rahvaarv: &#039;, d.rahvaarv, \n\t&#039; | Id: &#039;, d.linnID,\n\t\n\t&#039; | Uued linna andmed: &#039;, i.linnaNimi,\n\t&#039; | rahvaarv: &#039;, i.rahvaarv, \n\t&#039; | Id: &#039;, i.linnID\n)\nfrom deleted as d inner join inserted as i\non d.linnID=i.linnID;\n\n--Kontrollime\nselect * from Linnad;\nupdate Linnad set linnaNimi = &#039;Valga&#039;, rahvaarv = 7409832 where linnID = 2;\nselect * from Linnad;\nselect * from Logi;\n<\/pre><\/div>\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/olegberezevski.wordpress.com\/wp-content\/uploads\/2026\/04\/1cd146ac-0d34-421d-ac98-83df958f969f.png?w=746\" alt=\"\" class=\"wp-image-477\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/olegberezevski.wordpress.com\/wp-content\/uploads\/2026\/04\/47930280-3b48-42d1-9d18-c0b118638c14.png?w=834\" alt=\"\" class=\"wp-image-478\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">XXAMP<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">trigger = p\u00e4\u00e4stik<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Insert trigger<\/h3>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/olegberezevski.wordpress.com\/wp-content\/uploads\/2026\/04\/900d169f-a9bc-45c4-a47a-ffaba6602145.png?w=988\" alt=\"\" class=\"wp-image-480\" \/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">Delete trigger<\/h3>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/olegberezevski.wordpress.com\/wp-content\/uploads\/2026\/04\/47c72601-151a-4841-b076-7e6bd19965bd.png?w=695\" alt=\"\" class=\"wp-image-482\" \/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">Update trigger<\/h3>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/olegberezevski.wordpress.com\/wp-content\/uploads\/2026\/05\/081396aa-0397-42fa-b725-b5f915f40643.png?w=701\" alt=\"\" class=\"wp-image-607\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Praktiline t\u00f6\u00f6:<\/h2>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/olegberezevski.wordpress.com\/wp-content\/uploads\/2026\/04\/6ec2127c-0985-41f4-8262-c9d70b77f0ee.png?w=863\" alt=\"\" class=\"wp-image-486\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/olegberezevski.wordpress.com\/wp-content\/uploads\/2026\/04\/33e1dd44-4555-4d93-abac-2a83c0a62f8c.png?w=920\" alt=\"\" class=\"wp-image-488\" \/><\/figure>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\ncreate trigger autoUuedamine\nON auto\nFOR update\nas \ninsert into Logi(kuupaev, andmed, kasutaja)\nselect \ngetdate(), \nCONCAT\n(\n\t&#039; | Vana auto: &#039;, d.autoNr,\n\t&#039; | Auto omanik: &#039;, d.omanik, \n\t&#039; | Auto mark: &#039;, d.mark,\n\t&#039; | Auto aasta: &#039;, d.aasta,\n\t&#039; | Id: &#039;, d.autoID,\n\n\t&#039; | Uus auto: &#039;, i.autoNr,\n\t&#039; | Auto omanik: &#039;, i.omanik, \n\t&#039; | Auto mark: &#039;, i.mark,\n\t&#039; | Auto aasta: &#039;, i.aasta,\n\t&#039; | Id: &#039;, i.autoID\n),\nsystem_user\nfrom deleted as d inner join inserted as i\non d.autoID = i.autoID;\n<\/pre><\/div>\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/olegberezevski.wordpress.com\/wp-content\/uploads\/2026\/04\/38de4265-50c5-464d-8c0d-50a171295e4d.png?w=1024\" alt=\"\" class=\"wp-image-491\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n","protected":false},"excerpt":{"rendered":"<p>SQL triger on spetsiaalne andmebaasi objekt, mis k\u00e4ivitab automaatselt teatudn s\u00fcndmus ()nt INSERT, UPDATE, DELETE) triggerid aitavad automatiseerida protsessid andmebassis SQL Server. 2. trigeri loomine. INSERT TRIGGER, mis j\u00e4lgib andmete lisamine tabelisse linnad. DELETE triger, mis j\u00e4lgib andmete kustutamine tabelist linnad. UPDATE Trigger, mis j\u00e4lgib andmete uuendamine tabelis linnad. XXAMP trigger = p\u00e4\u00e4stik Insert trigger [&hellip;]<\/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-466","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/olegberezevski.eu\/wp\/wp-json\/wp\/v2\/pages\/466","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/olegberezevski.eu\/wp\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/olegberezevski.eu\/wp\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/olegberezevski.eu\/wp\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/olegberezevski.eu\/wp\/wp-json\/wp\/v2\/comments?post=466"}],"version-history":[{"count":0,"href":"https:\/\/olegberezevski.eu\/wp\/wp-json\/wp\/v2\/pages\/466\/revisions"}],"wp:attachment":[{"href":"https:\/\/olegberezevski.eu\/wp\/wp-json\/wp\/v2\/media?parent=466"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}