{"id":357,"date":"2026-04-13T08:53:11","date_gmt":"2026-04-13T05:53:11","guid":{"rendered":"https:\/\/timofeistoljarov.wordpress.com\/?page_id=357"},"modified":"2026-04-13T08:53:11","modified_gmt":"2026-04-13T05:53:11","slug":"sql-trigger","status":"publish","type":"page","link":"https:\/\/timofeistoljarov.eu\/wp\/andmebaasid\/sql-trigger\/","title":{"rendered":"SQL TRIGGER"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">SQL triger on spetsiaalne andmebaasi objekt, mis k\u00e4ivitab automaatselt teatud s\u00fcndmus (nt INSERT, UPDATE, DELETE). Trigerid aitavad automatiseerida protsessid andmebaasis<\/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 kuhu kasutaja sisestab andmeid (linnad). 2. tabel &#8211; logi, kus triger lisab j\u00e4lgitud andmeid.<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>create database trigerTimoha\nuse trigerTimoha\n\ncreate table linnad(\nlinnId int primary key identity(1,1),\nlinnanimi varchar(50) unique,\nrahvaarv int not null);\n--tabel logi\nCREATE TABLE logi(\nId int primary key identity(1,1),\nkuupaev datetime,\nandmed TEXT);\n\n<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/timofeistoljarov.eu\/wp\/wp-content\/uploads\/2026\/04\/ac725b40-bb1a-494e-875e-0a1c08c28321-1.png?w=242\" alt=\"\" class=\"wp-image-366\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">2. trigeri loomine.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">INSERT TRIGER, mis jalgib andmete lisamine tabelisse linnad.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Create TRIGGER linnaLisamine\non linnad\nfor insert\nAs\ninsert into logi(kuupaev, andmed)\nSelect\ngetdate(), inserted.linnanimi\nfrom inserted;\n--kontrllimiseks tuleb lisada uus linn tabelisse\ninsert into linnad (linnanimi, rahvaarv)\nvalues(&#039;Narva&#039;, 50000);\nSelect * from linnad;\nSelect * from logi;\n\n<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/timofeistoljarov.eu\/wp\/wp-content\/uploads\/2026\/04\/8dcf9051-6ddd-43d4-a35d-e6c643429888-1.png?w=318\" alt=\"\" class=\"wp-image-367\" \/><\/figure>\n\n\n\n<pre class=\"wp-block-code\"><code>--kustutame triger\ndrop trigger linnaLisamine;\n\nCreate TRIGGER linnaLisamine\non linnad\nfor insert\nAs\ninsert into logi(kuupaev, andmed)\nSelect\ngetdate(),\nCONCAT (&#039;lisarud linn: &#039;, inserted.linnanimi,\n&#039; | rahvaarv: &#039;, inserted.rahvaarv, &#039; id: &#039;, inserted.linnId)\nfrom inserted;<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>--delete trigger\nCreate TRIGGER linnakustutamine\non linnad\nfor delete\nAs\ninsert into logi(kuupaev, andmed)\nSelect\ngetdate(),\nCONCAT (&#039;kustutatud linn: &#039;, deleted.linnanimi,\n&#039; | rahvaarv: &#039;, deleted.rahvaarv, &#039; id: &#039;, deleted.linnId)\nfrom deleted ;\n\ndelete from linnad where linnId=1;\nSelect * from linnad;\nSelect * from logi;<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/timofeistoljarov.eu\/wp\/wp-content\/uploads\/2026\/04\/03c60f21-066c-4969-9eb1-9fec11594185-1.png?w=455\" alt=\"\" class=\"wp-image-369\" \/><\/figure>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE TRIGGER linnaUuendamine\non linnad\nFor update\nas\ninsert into logi(kuupaev, andmed)\nselect\nGETDATE(),\nCONCAT (&#039; vana linna andmed: &#039;, d.linnanimi,\n&#039; | &#039;, d.rahvaarv, &#039; | id: &#039;, d.linnId,\n&#039; vana linna andmed: &#039;, i.linnanimi,\n&#039; | &#039;, i.rahvaarv, &#039; | id: &#039;, i.linnId)\nfrom deleted  d inner join inserted i\non d.linnId=i.linnId<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>--kontrollimiseks uuendame linna andmed\nselect * from linnad\nUPDATE linnad Set linnanimi = &#039;Tapa uus&#039;, rahvaarv= 25\nWhere linnId=2;\nselect * from linnad;\nselect * from logi;\n\n<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>--lisame kasutajanimi logi tabelisse\n\nAlter table logi add kasutaja varchar(40);\n--INSERT, DELETE Trigger\nCreate TRIGGER linnaLisamineKustutamine\non linnad\nfor insert, DELETE\nAs\nbegin\nSET NOCOUNT ON;\ninsert into logi(kuupaev, andmed, kasutaja)\nSelect\ngetdate(),\nCONCAT (&#039;lisarud linn: &#039;, inserted.linnanimi,\n&#039; | rahvaarv: &#039;, inserted.rahvaarv, &#039; id: &#039;, inserted.linnId),\nSYSTEM_USER\nfrom inserted\n\nUNION ALL\n\nSELECT\ngetdate(),\nCONCAT (&#039;kustutatud linn: &#039;, deleted.linnanimi,\n&#039; | rahvaarv: &#039;, deleted.rahvaarv, &#039; id: &#039;, deleted.linnId),\nSYSTEM_USER\nfrom deleted ;\n\n\nend;\n\nDiSABle TRIGGER linnaLisamine on linnad;\nDISABLE TRIGGER linnaLkustutamine ON linnad;\n\ninsert into linnad (linnanimi, rahvaarv)\nvalues(&#039;Paldiski&#039;, 50000);\n\nDelete from linnad Where linnId=5\nselect * from linnad\nSelect * from logi<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"494\" height=\"71\" src=\"https:\/\/timofeistoljarov.eu\/wp\/wp-content\/uploads\/2026\/04\/b76b2a39-e59f-41bd-a62d-e2be2b67c220.png?w=494\" alt=\"\" class=\"wp-image-370\" srcset=\"https:\/\/timofeistoljarov.eu\/wp\/wp-content\/uploads\/2026\/04\/b76b2a39-e59f-41bd-a62d-e2be2b67c220.png 494w, https:\/\/timofeistoljarov.eu\/wp\/wp-content\/uploads\/2026\/04\/b76b2a39-e59f-41bd-a62d-e2be2b67c220-300x43.png 300w\" sizes=\"auto, (max-width: 494px) 100vw, 494px\" \/><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>SQL triger on spetsiaalne andmebaasi objekt, mis k\u00e4ivitab automaatselt teatud s\u00fcndmus (nt INSERT, UPDATE, DELETE). Trigerid aitavad automatiseerida protsessid andmebaasis SQL Server. 2. trigeri loomine. INSERT TRIGER, mis jalgib andmete lisamine tabelisse linnad.<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":7,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-357","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/timofeistoljarov.eu\/wp\/wp-json\/wp\/v2\/pages\/357","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/timofeistoljarov.eu\/wp\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/timofeistoljarov.eu\/wp\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/timofeistoljarov.eu\/wp\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/timofeistoljarov.eu\/wp\/wp-json\/wp\/v2\/comments?post=357"}],"version-history":[{"count":0,"href":"https:\/\/timofeistoljarov.eu\/wp\/wp-json\/wp\/v2\/pages\/357\/revisions"}],"up":[{"embeddable":true,"href":"https:\/\/timofeistoljarov.eu\/wp\/wp-json\/wp\/v2\/pages\/7"}],"wp:attachment":[{"href":"https:\/\/timofeistoljarov.eu\/wp\/wp-json\/wp\/v2\/media?parent=357"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}