{"id":554,"date":"2023-05-14T14:53:35","date_gmt":"2023-05-14T14:53:35","guid":{"rendered":"https:\/\/tarkvara.thkit.ee\/veeb\/?page_id=554"},"modified":"2023-05-21T21:52:57","modified_gmt":"2023-05-21T21:52:57","slug":"lisavoimalused-lk-72-andmetuupi-loomine-paastikprotsess-shema","status":"publish","type":"page","link":"https:\/\/tarkvara.thkit.ee\/veeb\/lisavoimalused-lk-72-andmetuupi-loomine-paastikprotsess-shema\/","title":{"rendered":"P\u00e4\u00e4stikprotsess"},"content":{"rendered":"\n<h5 class=\"wp-block-heading\">P\u00e4\u00e4stikprotsess<\/h5>\n\n\n\n<p>Andmebaasides saab toiminguid ka automaatselt t\u00f6\u00f6le panna. N\u00e4iteks kui soovitakse m\u00f5ningates tabelites toimunud muutused soovitud kohtadesse kokku logisse lugeda.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nCREATE TABLE logi(\n  id INT IDENTITY PRIMARY KEY,\n  aeg DATETIME,\n  toiming VARCHAR(20),\n  andmed VARCHAR(20),\n)\n<\/pre><\/div>\n\n\n<p>SQL 2005 v\u00f5imaldab luua p\u00e4\u00e4stikuid ka DDL k\u00e4skudele e. CREATE, ALTER ja DROP k\u00e4skudele!<\/p>\n\n\n\n<h6 class=\"wp-block-heading\">Loomine TRIGGERI LISAMINE<\/h6>\n\n\n\n<p>J\u00e4rgnev p\u00e4\u00e4stik kannab iga linnade tabelis toimunud muutuse kohta teate logitabelisse. Salap\u00e4rane nimi &#8220;inserted&#8221; t\u00e4hendab p\u00e4\u00e4stiku sees kasutatavat ajutist tabelit, mille kaudu saab k\u00e4tte lisatud rea. Selle ajutise inserted-tabeli tulpade nimed ja t\u00fc\u00fcbid on samad kui tegelikul tabelil, millega muutus toimus \u2013 siinjuhul tabel nimega linnad. Siin k\u00fcsitakse linnanimi k\u00f5igepealt eraldi muutujasse, et seda oleks mugavam p\u00e4\u00e4stiku raames toimuva lisamise juures kasutada.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nCREATE TRIGGER linnamuutus \nON linnad\nFOR INSERT \nAS\nINSERT INTO logi (aeg, toiming, andmed)\nSELECT GETDATE(), &#039;lisati&#039;, linnanimi\nFROM inserted\n<\/pre><\/div>\n\n\n<h6 class=\"wp-block-heading\">K\u00e4ivitus<\/h6>\n\n\n\n<p>Kui juhtutakse linnade tabelisse andmeid lisama, siis n\u00e4en kahel korral teateid, et &#8220;\u00fchele reale m\u00f5jus muutus&#8221;. \u00dcks teade on siis algse lisamise kohta ning teine teade p\u00e4\u00e4stiku abil toimunud logikande kohta.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nINSERT INTO linnad(linnanimi, rahvaarv)\nVALUES (&#039;Valga&#039;, 25000)\n<\/pre><\/div>\n\n\n<p>N\u00f5nda iga muutuse korral.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nINSERT INTO linnad(linnanimi, rahvaarv)\nVALUES (&#039;J\u00f5geva&#039;, 17000)\n<\/pre><\/div>\n\n\n<p>Ja kui edasi minna ja vaadata, mis logisse kirjutatud, siis on teated ilusti n\u00e4ha. Mitmes s\u00fcndmus, millal, mida tehti ja millise linnaga on tegemist.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSELECT * FROM logi\n<\/pre><\/div>\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"424\" height=\"380\" src=\"https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-16.png\" alt=\"\" class=\"wp-image-614\" srcset=\"https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-16.png 424w, https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-16-300x269.png 300w\" sizes=\"auto, (max-width: 424px) 100vw, 424px\" \/><\/figure>\n\n\n\n<p>SELECT * FROM Linnad<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"328\" height=\"300\" src=\"https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-17.png\" alt=\"\" class=\"wp-image-618\" srcset=\"https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-17.png 328w, https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-17-300x274.png 300w\" sizes=\"auto, (max-width: 328px) 100vw, 328px\" \/><\/figure>\n\n\n\n<p>Edasij\u00f5udnutele 44<br>Pikemad p\u00e4ringud 44<br>Keerukamad seosed tabelite vahel 57<br>Alamp\u00e4ringud 66<br>Lisav\u00f5imalused<\/p>\n\n\n\n<p class=\"has-medium-font-size\"><strong>Triggerid SQL Server<\/strong><\/p>\n\n\n\n<p><strong>TABEL linnad:<\/strong><\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nCreate table linnad(\nlinnID int identity(1,1) PRIMARY KEY,\nlinnanimi varchar(15) unique,\nrahvaarv int);\n<\/pre><\/div>\n\n\n<p><strong>TABEL logiI:<\/strong><\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nCreate table logi(\nid int identity(1,1) PRIMARY KEY,\naeg DATETIME,\ntoiming  varchar(100),\nandmed varchar(200),\nkasutaja varchar(100)\n)\n\n<\/pre><\/div>\n\n\n<p><strong>TRIGGERI LISAMINE:<\/strong><\/p>\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(aeg, toiming, andmed, kasutaja)\nSELECT GETDATE(), &#039;linn on lisatud&#039;, \ninserted.linnanimi, USER\nFROM inserted\n<\/pre><\/div>\n\n\n<p><strong>Kontrollimiseks:<\/strong><\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nINSERT INTO linnad(linnanimi, rahvaarv)\nVAlues (&#039;Tallinn&#039;, 600000);\nSELECT * FROM linnad;\nselect * from logi\n<\/pre><\/div>\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"534\" height=\"446\" src=\"https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-20.png\" alt=\"\" class=\"wp-image-636\" srcset=\"https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-20.png 534w, https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-20-300x251.png 300w\" sizes=\"auto, (max-width: 534px) 100vw, 534px\" \/><\/figure>\n\n\n\n<p><strong>Triggeri sisu muutmine:<\/strong><\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nALTER TRIGGER &#x5B;dbo].&#x5B;linnaLisamine]\nON &#x5B;dbo].&#x5B;linnad]\nFOR INSERT\nAS\nINSERT INTO logi(aeg, toiming, andmed, kasutaja)\nSELECT GETDATE(), &#039;linn on lisatud&#039;, \nconcat(inserted.linnanimi, &#039;, &#039;, inserted.rahvaarv),\nUSER\nFROM inserted\n<\/pre><\/div>\n\n\n<p><strong>Kontrollimiseks:<\/strong><\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nDelete from linnad\nWhere linnID = 1;\nselect * from linnad;\nselect * from logi\n<\/pre><\/div>\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"453\" height=\"407\" src=\"https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-21.png\" alt=\"\" class=\"wp-image-640\" srcset=\"https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-21.png 453w, https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-21-300x270.png 300w\" sizes=\"auto, (max-width: 453px) 100vw, 453px\" \/><\/figure>\n\n\n\n<p><strong>Trigger, mis j\u00e4lgib andmete uuendamine tabelis linnad<\/strong><\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nCREATE TRIGGER linnaUuendamine\nON linnad\nFOR UPDATE\nAS\nINSERT INTO logi(aeg, toiming, andmed, kasutaja)\nSELECT GETDATE(), &#039;linn on uuendatud&#039;, \nconcat(&#039;vanad andmed: &#039;, deleted.linnanimi, &#039;, &#039;,deleted.rahvaarv,\n &#039; uued andmed: &#039;, inserted.linnanimi, &#039;, &#039;,inserted.rahvaarv),\nUSER\nFROM deleted INNER JOIN inserted\nON inserted.linnID=deleted.linnID\n<\/pre><\/div>\n\n\n<p><strong>Kontrollimiseks:<\/strong><\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nupdate linnad \nset linnanimi=&#039;P\u00e4rnu&#039;,rahvaarv=20083\nwhere linnID=2;\nselect * from linnad;\nselect * from logi\n<\/pre><\/div>\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"456\" height=\"383\" src=\"https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-22.png\" alt=\"\" class=\"wp-image-642\" srcset=\"https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-22.png 456w, https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-22-300x252.png 300w\" sizes=\"auto, (max-width: 456px) 100vw, 456px\" \/><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>P\u00e4\u00e4stikprotsess Andmebaasides saab toiminguid ka automaatselt t\u00f6\u00f6le panna. N\u00e4iteks kui soovitakse m\u00f5ningates tabelites toimunud muutused soovitud kohtadesse kokku logisse lugeda. SQL 2005 v\u00f5imaldab luua p\u00e4\u00e4stikuid ka DDL k\u00e4skudele e. CREATE, ALTER ja DROP k\u00e4skudele! Loomine TRIGGERI LISAMINE J\u00e4rgnev p\u00e4\u00e4stik kannab iga linnade tabelis toimunud muutuse kohta teate logitabelisse. Salap\u00e4rane nimi &#8220;inserted&#8221; t\u00e4hendab p\u00e4\u00e4stiku sees kasutatavat &hellip; <a href=\"https:\/\/tarkvara.thkit.ee\/veeb\/lisavoimalused-lk-72-andmetuupi-loomine-paastikprotsess-shema\/\" class=\"more-link\">Loe edasi <span class=\"screen-reader-text\">P\u00e4\u00e4stikprotsess<\/span> <span class=\"meta-nav\">&rarr;<\/span><\/a><\/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-554","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/tarkvara.thkit.ee\/veeb\/wp-json\/wp\/v2\/pages\/554","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/tarkvara.thkit.ee\/veeb\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/tarkvara.thkit.ee\/veeb\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/tarkvara.thkit.ee\/veeb\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/tarkvara.thkit.ee\/veeb\/wp-json\/wp\/v2\/comments?post=554"}],"version-history":[{"count":27,"href":"https:\/\/tarkvara.thkit.ee\/veeb\/wp-json\/wp\/v2\/pages\/554\/revisions"}],"predecessor-version":[{"id":643,"href":"https:\/\/tarkvara.thkit.ee\/veeb\/wp-json\/wp\/v2\/pages\/554\/revisions\/643"}],"wp:attachment":[{"href":"https:\/\/tarkvara.thkit.ee\/veeb\/wp-json\/wp\/v2\/media?parent=554"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}