{"id":221,"date":"2025-05-27T11:39:33","date_gmt":"2025-05-27T09:39:33","guid":{"rendered":"https:\/\/maratsaukans23.thkit.ee\/vp\/?page_id=221"},"modified":"2025-05-27T11:45:58","modified_gmt":"2025-05-27T09:45:58","slug":"sql-trigerid","status":"publish","type":"page","link":"https:\/\/maratsaukans23.thkit.ee\/vp\/sql-trigerid\/","title":{"rendered":"SQL Trigerid"},"content":{"rendered":"\n<pre class=\"wp-block-code\"><code>--table, mida automaatselt t\u00e4itab triger\nCreate table logi(\nid int primary key identity(1,1),\ntegevus varchar(25),\nkasutaja varchar(25),\naeg datetime,\nandmet text)\n--tabel ,millega t\u00f6\u00f6tab kasutaja\ncreate table puud(\npuuid int primary key identity(1,1),\npuuNimi varchar(25),\npikkus int,\naasta int)\ninsert into puud (puuNimi,pikkus,aasta) values ('tamm', 20, 20);\n\ncreate trigger puuKustutamine\non puud\nfor insert\nas \ninsert into logi(kasutaja, tegevus, aeg, andmet)\nselect \nSYSTEM_USER,\n'puu on kustutatud',\nGETDATE(),\nconcat (deleted.puuNimi,',' ,deleted.pikkus,',' ,deleted.aasta)\nfrom deleted;\n--kontroll\ninsert into puud(puuNimi)\nvalues ('k\u00e4sk');\nselect * from puud;\nselect * from logi;\ndrop trigger puulisamine\ndelete from puud where puuid=1;\n\n--triger, mis j\u00e4lgib tabelis uuendamine\ncreate trigger puuUuendamine\non puud\nfor insert\nas \ninsert into logi(kasutaja, tegevus, aeg, andmet)\nselect \nSYSTEM_USER,\n'puu on uuendatud',\nGETDATE(),\nconcat (\n'vana puu info',',' ,deleted.puuNimi,',' ,deleted.pikkus,',' ,deleted.aasta,\n'uus puu info',',' ,inserted.puuNimi,',' ,inserted.pikkus,',' ,inserted.aasta\n)\nfrom deleted inner join inserted\non deleted.puuid=inserted.puuid;\n--kontroll\ninsert into puud(puuNimi)\nvalues ('k\u00e4sk');\nselect * from puud;\nselect * from logi;\ndrop trigger puulisamine\nupdate puud set pikkus=25555, aasta=1900\nwhere puuid=2\n\nfrom deleted inner join inserted\non deleted.puuid=inserted.puuid\n<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"676\" height=\"261\" src=\"https:\/\/maratsaukans23.thkit.ee\/vp\/wp-content\/uploads\/2025\/05\/image-35.png\" alt=\"\" class=\"wp-image-232\" srcset=\"https:\/\/maratsaukans23.thkit.ee\/vp\/wp-content\/uploads\/2025\/05\/image-35.png 676w, https:\/\/maratsaukans23.thkit.ee\/vp\/wp-content\/uploads\/2025\/05\/image-35-300x116.png 300w, https:\/\/maratsaukans23.thkit.ee\/vp\/wp-content\/uploads\/2025\/05\/image-35-624x241.png 624w\" sizes=\"auto, (max-width: 676px) 100vw, 676px\" \/><\/figure>\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"class_list":["post-221","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/maratsaukans23.thkit.ee\/vp\/wp-json\/wp\/v2\/pages\/221","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/maratsaukans23.thkit.ee\/vp\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/maratsaukans23.thkit.ee\/vp\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/maratsaukans23.thkit.ee\/vp\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/maratsaukans23.thkit.ee\/vp\/wp-json\/wp\/v2\/comments?post=221"}],"version-history":[{"count":5,"href":"https:\/\/maratsaukans23.thkit.ee\/vp\/wp-json\/wp\/v2\/pages\/221\/revisions"}],"predecessor-version":[{"id":233,"href":"https:\/\/maratsaukans23.thkit.ee\/vp\/wp-json\/wp\/v2\/pages\/221\/revisions\/233"}],"wp:attachment":[{"href":"https:\/\/maratsaukans23.thkit.ee\/vp\/wp-json\/wp\/v2\/media?parent=221"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}