{"id":1670,"date":"2024-09-10T09:34:18","date_gmt":"2024-09-10T06:34:18","guid":{"rendered":"https:\/\/jelizavetaostapjuk23.thkit.ee\/wp\/?page_id=1670"},"modified":"2024-09-10T09:34:18","modified_gmt":"2024-09-10T06:34:18","slug":"trigerid-kahe-seotud-tabelite-pohjal","status":"publish","type":"page","link":"https:\/\/jelizavetaostapjuk23.thkit.ee\/wp\/eng\/trigerid-kahe-seotud-tabelite-pohjal\/","title":{"rendered":"Trigerid kahe seotud tabelite p\u00f5hjal"},"content":{"rendered":"\n<p><strong>Loonud 2 tabelit<\/strong><\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\ncreate database trigeri2tabelid;\nuse trigeri2tabelid;\n\n--Loo tabel linnad\nCreate table linnad(\nlinnID int identity(1,1) PRIMARY KEY,\nlinnanimi varchar(15),\nrahvaarv int);\n\n--Loo tabel logi\nCreate table logi(\nid int identity(1,1) PRIMARY KEY,\naeg DATETIME,\ntoiming  varchar(100),\nandmed text,\nkasutaja varchar(200)\n)\n\nCREATE TABLE maakond(\nmaakondID int Primary KEY identity(1,1),\nmaakond varchar(100) UNIQUE);\n    \nINSERT INTO maakond(maakond)\nVALUES (&#039;Harjumaa&#039;);\nINSERT INTO maakond(maakond)\nVALUES (&#039;P\u00e4rnumaa&#039;);\n\nSELECT * FROM maakond\n\n--seoste loomine linnad\/maakond\nALTER TABLE linnad ADD maakondID int;\nALTER TABLE linnad ADD CONSTRAINT fk_maakond\nFOREIGN KEY (maakondID) References maakond(maakondID) \n<\/pre><\/div>\n\n\n<p><strong>loonud trigeri linnaLisamine<\/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(kasutaja,aeg,toiming,andmed)\nselect USER, GETDATE(),&#039;linn on lisatud&#039;,CONCAT(l.linnanimi, &#039;, &#039;, m.maakond)\nfrom linnad l\ninner join maakond m\non m.maakondID=l.maakondID;\n\n--kontrollimiseks\ninsert into linnad(linnanimi,rahvaarv,maakondID)\nvalues (&#039;Tallinn&#039;, 436863, 1);\n\nselect * from linnad\nselect * from logi\n<\/pre><\/div>\n\n\n<p><strong>loonud trigeri linnaKustutamine<\/strong><\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\ncreate trigger linnaKustutamine\non linnad\nfor delete\nas\ninsert into logi(kasutaja,aeg,toiming,andmed)\nselect USER, GETDATE(),&#039;linn on kustutatud&#039;,CONCAT(deleted.linnanimi, &#039;, &#039;, m.maakond)\nfrom deleted\ninner join maakond m\non deleted.maakondID=m.maakondID;\n\n--kontroll\ndelete from linnad\nwhere linnID=1\nselect * from linnad\nselect * from logi\n<\/pre><\/div>\n\n\n<p><strong>loonud trigeri linnaUuendamine<\/strong><\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\ncreate trigger linnaUendamine\non linnad\nfor update\nas\ninsert into logi(kasutaja,aeg,toiming,andmed)\nselect USER, GETDATE(),&#039;linn on uuendatud&#039;,CONCAT(\n&#039;vanad andmed - &#039;, deleted.linnanimi, &#039;, &#039;, m1.maakond,\n&#039;uuendatud andmed - &#039;, inserted.linnanimi,&#039;, &#039;, m2.maakond)\nfrom deleted\ninner join inserted on deleted.linnID=inserted.linnID\ninner join maakond m1 on deleted.maakondID=m1.maakondID\ninner join maakond m2 on inserted.maakondID=m2.maakondID;\n\n--kontroll\nupdate linnad set linnanimi=&#039;Tallinn-Suur&#039;, maakondID=2\nwhere linnID=2;\nselect * from linnad;\nselect * from logi\n<\/pre><\/div>","protected":false},"excerpt":{"rendered":"<p>Loonud 2 tabelit loonud trigeri linnaLisamine loonud trigeri linnaKustutamine loonud trigeri linnaUuendamine<\/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-1670","page","type-page","status-publish","hentry"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.3 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Trigerid kahe seotud tabelite p\u00f5hjal - Jelizaveta Ostapjuk Portfolio<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/jelizavetaostapjuk23.thkit.ee\/wp\/eng\/trigerid-kahe-seotud-tabelite-pohjal\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Trigerid kahe seotud tabelite p\u00f5hjal - Jelizaveta Ostapjuk Portfolio\" \/>\n<meta property=\"og:description\" content=\"Loonud 2 tabelit loonud trigeri linnaLisamine loonud trigeri linnaKustutamine loonud trigeri linnaUuendamine\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jelizavetaostapjuk23.thkit.ee\/wp\/eng\/trigerid-kahe-seotud-tabelite-pohjal\/\" \/>\n<meta property=\"og:site_name\" content=\"Jelizaveta Ostapjuk Portfolio\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/jelizavetaostapjuk23.thkit.ee\\\/wp\\\/eng\\\/trigerid-kahe-seotud-tabelite-pohjal\\\/\",\"url\":\"https:\\\/\\\/jelizavetaostapjuk23.thkit.ee\\\/wp\\\/eng\\\/trigerid-kahe-seotud-tabelite-pohjal\\\/\",\"name\":\"Trigerid kahe seotud tabelite p\u00f5hjal - Jelizaveta Ostapjuk Portfolio\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/jelizavetaostapjuk23.thkit.ee\\\/wp\\\/eng\\\/#website\"},\"datePublished\":\"2024-09-10T06:34:18+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/jelizavetaostapjuk23.thkit.ee\\\/wp\\\/eng\\\/trigerid-kahe-seotud-tabelite-pohjal\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/jelizavetaostapjuk23.thkit.ee\\\/wp\\\/eng\\\/trigerid-kahe-seotud-tabelite-pohjal\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/jelizavetaostapjuk23.thkit.ee\\\/wp\\\/eng\\\/trigerid-kahe-seotud-tabelite-pohjal\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/jelizavetaostapjuk23.thkit.ee\\\/wp\\\/eng\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Trigerid kahe seotud tabelite p\u00f5hjal\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/jelizavetaostapjuk23.thkit.ee\\\/wp\\\/eng\\\/#website\",\"url\":\"https:\\\/\\\/jelizavetaostapjuk23.thkit.ee\\\/wp\\\/eng\\\/\",\"name\":\"Jelizaveta Ostapjuk Portfolio\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/jelizavetaostapjuk23.thkit.ee\\\/wp\\\/eng\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Trigerid kahe seotud tabelite p\u00f5hjal - Jelizaveta Ostapjuk Portfolio","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/jelizavetaostapjuk23.thkit.ee\/wp\/eng\/trigerid-kahe-seotud-tabelite-pohjal\/","og_locale":"en_US","og_type":"article","og_title":"Trigerid kahe seotud tabelite p\u00f5hjal - Jelizaveta Ostapjuk Portfolio","og_description":"Loonud 2 tabelit loonud trigeri linnaLisamine loonud trigeri linnaKustutamine loonud trigeri linnaUuendamine","og_url":"https:\/\/jelizavetaostapjuk23.thkit.ee\/wp\/eng\/trigerid-kahe-seotud-tabelite-pohjal\/","og_site_name":"Jelizaveta Ostapjuk Portfolio","twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/jelizavetaostapjuk23.thkit.ee\/wp\/eng\/trigerid-kahe-seotud-tabelite-pohjal\/","url":"https:\/\/jelizavetaostapjuk23.thkit.ee\/wp\/eng\/trigerid-kahe-seotud-tabelite-pohjal\/","name":"Trigerid kahe seotud tabelite p\u00f5hjal - Jelizaveta Ostapjuk Portfolio","isPartOf":{"@id":"https:\/\/jelizavetaostapjuk23.thkit.ee\/wp\/eng\/#website"},"datePublished":"2024-09-10T06:34:18+00:00","breadcrumb":{"@id":"https:\/\/jelizavetaostapjuk23.thkit.ee\/wp\/eng\/trigerid-kahe-seotud-tabelite-pohjal\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jelizavetaostapjuk23.thkit.ee\/wp\/eng\/trigerid-kahe-seotud-tabelite-pohjal\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jelizavetaostapjuk23.thkit.ee\/wp\/eng\/trigerid-kahe-seotud-tabelite-pohjal\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jelizavetaostapjuk23.thkit.ee\/wp\/eng\/"},{"@type":"ListItem","position":2,"name":"Trigerid kahe seotud tabelite p\u00f5hjal"}]},{"@type":"WebSite","@id":"https:\/\/jelizavetaostapjuk23.thkit.ee\/wp\/eng\/#website","url":"https:\/\/jelizavetaostapjuk23.thkit.ee\/wp\/eng\/","name":"Jelizaveta Ostapjuk Portfolio","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/jelizavetaostapjuk23.thkit.ee\/wp\/eng\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"}]}},"_links":{"self":[{"href":"https:\/\/jelizavetaostapjuk23.thkit.ee\/wp\/eng\/wp-json\/wp\/v2\/pages\/1670","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jelizavetaostapjuk23.thkit.ee\/wp\/eng\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/jelizavetaostapjuk23.thkit.ee\/wp\/eng\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/jelizavetaostapjuk23.thkit.ee\/wp\/eng\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jelizavetaostapjuk23.thkit.ee\/wp\/eng\/wp-json\/wp\/v2\/comments?post=1670"}],"version-history":[{"count":0,"href":"https:\/\/jelizavetaostapjuk23.thkit.ee\/wp\/eng\/wp-json\/wp\/v2\/pages\/1670\/revisions"}],"wp:attachment":[{"href":"https:\/\/jelizavetaostapjuk23.thkit.ee\/wp\/eng\/wp-json\/wp\/v2\/media?parent=1670"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}