{"id":845,"date":"2024-09-10T06:35:45","date_gmt":"2024-09-10T06:35:45","guid":{"rendered":"https:\/\/kirillsats23.thkit.ee\/wp\/?page_id=845"},"modified":"2024-09-10T07:14:54","modified_gmt":"2024-09-10T07:14:54","slug":"sql-server-trigerid-kahe-seotud-tabelite-pohjal","status":"publish","type":"page","link":"https:\/\/kirillsats23.thkit.ee\/wp\/?page_id=845","title":{"rendered":"SQL Server. Trigerid kahe seotud tabelite p\u00f5hjal"},"content":{"rendered":"\n<p><br><br><br>Loome database<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#2e3440ff\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"create database triger2tabelid;\nuse triger2tabelid;\n\/*Loome AB ja kaks tabelid*\/\nCreate table linnad(\nlinnID int identity(1,1) PRIMARY KEY,\nlinnanimi varchar(15),\nrahvaarv int);\n\nCreate table logi(\nid int identity(1,1) PRIMARY KEY,\naeg DATETIME,\ntoiming  varchar(100),\nandmed text,\nkasutaja varchar (200)\n)\n\/*Maakond tabeli lisamine*\/\nCREATE TABLE maakond(\n\tmaakondID int Primary KEY identity(1,1),\n\tmaakond varchar(100) UNIQUE);\n    \nINSERT INTO maakond(maakond)\nVALUES ('Harjumaa');\nINSERT INTO maakond(maakond)\nVALUES ('P\u00e4rnumaa');\n\nSELECT * FROM maakond\n\n\n\/*seoste loomine linnad --&gt; maakond*\/\nALTER TABLE linnad ADD maakondID int;\nALTER TABLE linnad ADD CONSTRAINT fk_maakond\nFOREIGN KEY (maakondID) References maakond(maakondID) \" style=\"color:#d8dee9ff;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki nord\" style=\"background-color: #2e3440ff\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #81A1C1\">create<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">database<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #88C0D0\">triger2tabelid<\/span><span style=\"color: #D8DEE9FF\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">use<\/span><span style=\"color: #D8DEE9FF\"> triger2tabelid;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #616E88\">\/*Loome AB ja kaks tabelid*\/<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">Create<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">table<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #88C0D0\">linnad<\/span><span style=\"color: #D8DEE9FF\">(<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">linnID <\/span><span style=\"color: #81A1C1\">int<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #88C0D0\">identity<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #B48EAD\">1<\/span><span style=\"color: #D8DEE9FF\">,<\/span><span style=\"color: #B48EAD\">1<\/span><span style=\"color: #D8DEE9FF\">) <\/span><span style=\"color: #81A1C1\">PRIMARY KEY<\/span><span style=\"color: #D8DEE9FF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">linnanimi <\/span><span style=\"color: #81A1C1\">varchar<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #B48EAD\">15<\/span><span style=\"color: #D8DEE9FF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">rahvaarv <\/span><span style=\"color: #81A1C1\">int<\/span><span style=\"color: #D8DEE9FF\">);<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">Create<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">table<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #88C0D0\">logi<\/span><span style=\"color: #D8DEE9FF\">(<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">id <\/span><span style=\"color: #81A1C1\">int<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #88C0D0\">identity<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #B48EAD\">1<\/span><span style=\"color: #D8DEE9FF\">,<\/span><span style=\"color: #B48EAD\">1<\/span><span style=\"color: #D8DEE9FF\">) <\/span><span style=\"color: #81A1C1\">PRIMARY KEY<\/span><span style=\"color: #D8DEE9FF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">aeg <\/span><span style=\"color: #81A1C1\">DATETIME<\/span><span style=\"color: #D8DEE9FF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">toiming  <\/span><span style=\"color: #81A1C1\">varchar<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #B48EAD\">100<\/span><span style=\"color: #D8DEE9FF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">andmed <\/span><span style=\"color: #81A1C1\">text<\/span><span style=\"color: #D8DEE9FF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">kasutaja <\/span><span style=\"color: #81A1C1\">varchar<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><span style=\"color: #B48EAD\">200<\/span><span style=\"color: #D8DEE9FF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #616E88\">\/*Maakond tabeli lisamine*\/<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">CREATE<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">TABLE<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #88C0D0\">maakond<\/span><span style=\"color: #D8DEE9FF\">(<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">\tmaakondID <\/span><span style=\"color: #81A1C1\">int<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">Primary KEY<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #88C0D0\">identity<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #B48EAD\">1<\/span><span style=\"color: #D8DEE9FF\">,<\/span><span style=\"color: #B48EAD\">1<\/span><span style=\"color: #D8DEE9FF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">\tmaakond <\/span><span style=\"color: #81A1C1\">varchar<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #B48EAD\">100<\/span><span style=\"color: #D8DEE9FF\">) <\/span><span style=\"color: #81A1C1\">UNIQUE<\/span><span style=\"color: #D8DEE9FF\">);<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">INSERT INTO<\/span><span style=\"color: #D8DEE9FF\"> maakond(maakond)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">VALUES<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">Harjumaa<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\">);<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">INSERT INTO<\/span><span style=\"color: #D8DEE9FF\"> maakond(maakond)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">VALUES<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">P\u00e4rnumaa<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\">);<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">SELECT<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">*<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">FROM<\/span><span style=\"color: #D8DEE9FF\"> maakond<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #616E88\">\/*seoste loomine linnad --&gt; maakond*\/<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">ALTER<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">TABLE<\/span><span style=\"color: #D8DEE9FF\"> linnad <\/span><span style=\"color: #81A1C1\">ADD<\/span><span style=\"color: #D8DEE9FF\"> maakondID <\/span><span style=\"color: #81A1C1\">int<\/span><span style=\"color: #D8DEE9FF\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">ALTER<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">TABLE<\/span><span style=\"color: #D8DEE9FF\"> linnad <\/span><span style=\"color: #81A1C1\">ADD<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">CONSTRAINT<\/span><span style=\"color: #D8DEE9FF\"> fk_maakond<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">FOREIGN KEY<\/span><span style=\"color: #D8DEE9FF\"> (maakondID) <\/span><span style=\"color: #81A1C1\">References<\/span><span style=\"color: #D8DEE9FF\"> maakond(maakondID) <\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p>Trigger &#8216;liisamine&#8217; loomine<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#2e3440ff\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"CREATE TRIGGER linnaLisamine\nON linnad\nFOR INSERT\nAS\nINSERT INTO logi(kasutaja, aeg, toiming, andmed)\nSELECT USER, GETDATE(), 'linn on lisatud',\nCONCAT(l.linnanimi, ',', m.maakond)\nFROM linnad l\nINNER JOIN maakond m\nON m.maakondID=l.maakondID;\n\n--kontrollimeseks\nINSERT INTO linnad(linnanimi, rahvaarv, maakondID)\nVALUES ('Tallinn', 436863,1);\n\nSELECT * FROM linnad\nSELECT *FROM logi\" style=\"color:#d8dee9ff;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki nord\" style=\"background-color: #2e3440ff\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #81A1C1\">CREATE<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">TRIGGER<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #88C0D0\">linnaLisamine<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">ON<\/span><span style=\"color: #D8DEE9FF\"> linnad<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">FOR<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">INSERT<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">AS<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">INSERT INTO<\/span><span style=\"color: #D8DEE9FF\"> logi(kasutaja, aeg, toiming, andmed)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">SELECT<\/span><span style=\"color: #D8DEE9FF\"> USER, <\/span><span style=\"color: #88C0D0\">GETDATE<\/span><span style=\"color: #D8DEE9FF\">(), <\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">linn on lisatud<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #88C0D0\">CONCAT<\/span><span style=\"color: #D8DEE9FF\">(l.linnanimi, <\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">,<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\">, m.maakond)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">FROM<\/span><span style=\"color: #D8DEE9FF\"> linnad l<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">INNER JOIN<\/span><span style=\"color: #D8DEE9FF\"> maakond m<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">ON<\/span><span style=\"color: #D8DEE9FF\"> m.maakondID<\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\">l.maakondID;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #616E88\">--kontrollimeseks<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">INSERT INTO<\/span><span style=\"color: #D8DEE9FF\"> linnad(linnanimi, rahvaarv, maakondID)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">VALUES<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">Tallinn<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\">, <\/span><span style=\"color: #B48EAD\">436863<\/span><span style=\"color: #D8DEE9FF\">,<\/span><span style=\"color: #B48EAD\">1<\/span><span style=\"color: #D8DEE9FF\">);<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">SELECT<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">*<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">FROM<\/span><span style=\"color: #D8DEE9FF\"> linnad<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">SELECT<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">*FROM<\/span><span style=\"color: #D8DEE9FF\"> logi<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p>Triggeri &#8216;Kustutamine&#8217; loomine<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#2e3440ff\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"CREATE TRIGGER linnaKustutamine\nON linnad\nFOR DELETE\nAS\nINSERT INTO logi(kasutaja, aeg, toiming, andmed)\nSELECT USER, GETDATE(), 'linn on kustutatud',\nCONCAT(deleted.linnanimi, ',', m.maakond)\nFROM deleted\nINNER JOIN maakond m\nON deleted.maakondID=m.maakondID;\n\n\n\n\n--kontroll\nDELETE FROM linnad \nWHERE linnID = 2;\nSELECT * FROM linnad;\nSELECT * FROM logi;\n\" style=\"color:#d8dee9ff;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki nord\" style=\"background-color: #2e3440ff\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #81A1C1\">CREATE<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">TRIGGER<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #88C0D0\">linnaKustutamine<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">ON<\/span><span style=\"color: #D8DEE9FF\"> linnad<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">FOR<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">DELETE<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">AS<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">INSERT INTO<\/span><span style=\"color: #D8DEE9FF\"> logi(kasutaja, aeg, toiming, andmed)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">SELECT<\/span><span style=\"color: #D8DEE9FF\"> USER, <\/span><span style=\"color: #88C0D0\">GETDATE<\/span><span style=\"color: #D8DEE9FF\">(), <\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">linn on kustutatud<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #88C0D0\">CONCAT<\/span><span style=\"color: #D8DEE9FF\">(deleted.linnanimi, <\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">,<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\">, m.maakond)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">FROM<\/span><span style=\"color: #D8DEE9FF\"> deleted<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">INNER JOIN<\/span><span style=\"color: #D8DEE9FF\"> maakond m<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">ON<\/span><span style=\"color: #D8DEE9FF\"> deleted.maakondID<\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\">m.maakondID;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #616E88\">--kontroll<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">DELETE<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">FROM<\/span><span style=\"color: #D8DEE9FF\"> linnad <\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">WHERE<\/span><span style=\"color: #D8DEE9FF\"> linnID <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #B48EAD\">2<\/span><span style=\"color: #D8DEE9FF\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">SELECT<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">*<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">FROM<\/span><span style=\"color: #D8DEE9FF\"> linnad;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">SELECT<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">*<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">FROM<\/span><span style=\"color: #D8DEE9FF\"> logi;<\/span><\/span>\n<span class=\"line\"><\/span><\/code><\/pre><\/div>\n\n\n\n<p>Triggeri &#8216;Uuendamine&#8217; loomine<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#2e3440ff\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"CREATE TRIGGER linnaUuendaimne\nON linnad\nFOR UPDATE\nAS\nINSERT INTO logi(kasutaja, aeg, toiming, andmed)\nSELECT USER, GETDATE(), 'linn on kustutatud',\nCONCAT(\n'vanad andmed - ', deleted.linnanimi, ',', m1.maakond,\n'uuendatud andmed - ', inserted.linnanimi, ',', 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 = 'Tallinn-V\u00e4ike', maakondID=2\nWHERE linnID=3; \nSELECT * FROM linnad\nSELECT * FROM logi\n\" style=\"color:#d8dee9ff;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki nord\" style=\"background-color: #2e3440ff\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #81A1C1\">CREATE<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">TRIGGER<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #88C0D0\">linnaUuendaimne<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">ON<\/span><span style=\"color: #D8DEE9FF\"> linnad<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">FOR<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">UPDATE<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">AS<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">INSERT INTO<\/span><span style=\"color: #D8DEE9FF\"> logi(kasutaja, aeg, toiming, andmed)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">SELECT<\/span><span style=\"color: #D8DEE9FF\"> USER, <\/span><span style=\"color: #88C0D0\">GETDATE<\/span><span style=\"color: #D8DEE9FF\">(), <\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">linn on kustutatud<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #88C0D0\">CONCAT<\/span><span style=\"color: #D8DEE9FF\">(<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">vanad andmed - <\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\">, deleted.linnanimi, <\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">,<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\">, m1.maakond,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">uuendatud andmed - <\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\">, inserted.linnanimi, <\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">,<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\">, m2.maakond)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">FROM<\/span><span style=\"color: #D8DEE9FF\"> deleted<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">INNER JOIN<\/span><span style=\"color: #D8DEE9FF\"> inserted  <\/span><span style=\"color: #81A1C1\">ON<\/span><span style=\"color: #D8DEE9FF\"> deleted.linnID<\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\">inserted.linnID<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">INNER JOIN<\/span><span style=\"color: #D8DEE9FF\"> maakond m1 <\/span><span style=\"color: #81A1C1\">ON<\/span><span style=\"color: #D8DEE9FF\"> deleted.maakondID<\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\">m1.maakondID<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">INNER JOIN<\/span><span style=\"color: #D8DEE9FF\"> maakond m2 <\/span><span style=\"color: #81A1C1\">ON<\/span><span style=\"color: #D8DEE9FF\"> inserted.maakondID<\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\">m2.maakondID<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #616E88\">--kontroll<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">UPDATE<\/span><span style=\"color: #D8DEE9FF\"> linnad <\/span><span style=\"color: #81A1C1\">SET<\/span><span style=\"color: #D8DEE9FF\"> linnanimi <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">Tallinn-V\u00e4ike<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\">, maakondID<\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #B48EAD\">2<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">WHERE<\/span><span style=\"color: #D8DEE9FF\"> linnID<\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #B48EAD\">3<\/span><span style=\"color: #D8DEE9FF\">; <\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">SELECT<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">*<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">FROM<\/span><span style=\"color: #D8DEE9FF\"> linnad<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">SELECT<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">*<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">FROM<\/span><span style=\"color: #D8DEE9FF\"> logi<\/span><\/span>\n<span class=\"line\"><\/span><\/code><\/pre><\/div>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Loome database Trigger &#8216;liisamine&#8217; loomine Triggeri &#8216;Kustutamine&#8217; loomine Triggeri &#8216;Uuendamine&#8217; loomine<\/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-845","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>SQL Server. Trigerid kahe seotud tabelite p\u00f5hjal - Kirill Sats &otilde;pimapp | portfoolio<\/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:\/\/kirillsats23.thkit.ee\/wp\/?page_id=845\" \/>\n<meta property=\"og:locale\" content=\"et_EE\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server. Trigerid kahe seotud tabelite p\u00f5hjal - Kirill Sats &otilde;pimapp | portfoolio\" \/>\n<meta property=\"og:description\" content=\"Loome database Trigger &#8216;liisamine&#8217; loomine Triggeri &#8216;Kustutamine&#8217; loomine Triggeri &#8216;Uuendamine&#8217; loomine\" \/>\n<meta property=\"og:url\" content=\"https:\/\/kirillsats23.thkit.ee\/wp\/?page_id=845\" \/>\n<meta property=\"og:site_name\" content=\"Kirill Sats &otilde;pimapp | portfoolio\" \/>\n<meta property=\"article:modified_time\" content=\"2024-09-10T07:14:54+00:00\" \/>\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 minut\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/kirillsats23.thkit.ee\\\/wp\\\/?page_id=845\",\"url\":\"https:\\\/\\\/kirillsats23.thkit.ee\\\/wp\\\/?page_id=845\",\"name\":\"SQL Server. Trigerid kahe seotud tabelite p\u00f5hjal - Kirill Sats &otilde;pimapp | portfoolio\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/kirillsats23.thkit.ee\\\/wp\\\/#website\"},\"datePublished\":\"2024-09-10T06:35:45+00:00\",\"dateModified\":\"2024-09-10T07:14:54+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/kirillsats23.thkit.ee\\\/wp\\\/?page_id=845#breadcrumb\"},\"inLanguage\":\"et\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/kirillsats23.thkit.ee\\\/wp\\\/?page_id=845\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/kirillsats23.thkit.ee\\\/wp\\\/?page_id=845#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/kirillsats23.thkit.ee\\\/wp\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server. Trigerid kahe seotud tabelite p\u00f5hjal\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/kirillsats23.thkit.ee\\\/wp\\\/#website\",\"url\":\"https:\\\/\\\/kirillsats23.thkit.ee\\\/wp\\\/\",\"name\":\"Kirill Sats &otilde;pimapp | portfoolio\",\"description\":\"Noorem Tarkvaarendaja eriala\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/kirillsats23.thkit.ee\\\/wp\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"et\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Server. Trigerid kahe seotud tabelite p\u00f5hjal - Kirill Sats &otilde;pimapp | portfoolio","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:\/\/kirillsats23.thkit.ee\/wp\/?page_id=845","og_locale":"et_EE","og_type":"article","og_title":"SQL Server. Trigerid kahe seotud tabelite p\u00f5hjal - Kirill Sats &otilde;pimapp | portfoolio","og_description":"Loome database Trigger &#8216;liisamine&#8217; loomine Triggeri &#8216;Kustutamine&#8217; loomine Triggeri &#8216;Uuendamine&#8217; loomine","og_url":"https:\/\/kirillsats23.thkit.ee\/wp\/?page_id=845","og_site_name":"Kirill Sats &otilde;pimapp | portfoolio","article_modified_time":"2024-09-10T07:14:54+00:00","twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"1 minut"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/kirillsats23.thkit.ee\/wp\/?page_id=845","url":"https:\/\/kirillsats23.thkit.ee\/wp\/?page_id=845","name":"SQL Server. Trigerid kahe seotud tabelite p\u00f5hjal - Kirill Sats &otilde;pimapp | portfoolio","isPartOf":{"@id":"https:\/\/kirillsats23.thkit.ee\/wp\/#website"},"datePublished":"2024-09-10T06:35:45+00:00","dateModified":"2024-09-10T07:14:54+00:00","breadcrumb":{"@id":"https:\/\/kirillsats23.thkit.ee\/wp\/?page_id=845#breadcrumb"},"inLanguage":"et","potentialAction":[{"@type":"ReadAction","target":["https:\/\/kirillsats23.thkit.ee\/wp\/?page_id=845"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/kirillsats23.thkit.ee\/wp\/?page_id=845#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/kirillsats23.thkit.ee\/wp\/"},{"@type":"ListItem","position":2,"name":"SQL Server. Trigerid kahe seotud tabelite p\u00f5hjal"}]},{"@type":"WebSite","@id":"https:\/\/kirillsats23.thkit.ee\/wp\/#website","url":"https:\/\/kirillsats23.thkit.ee\/wp\/","name":"Kirill Sats &otilde;pimapp | portfoolio","description":"Noorem Tarkvaarendaja eriala","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/kirillsats23.thkit.ee\/wp\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"et"}]}},"_links":{"self":[{"href":"https:\/\/kirillsats23.thkit.ee\/wp\/index.php?rest_route=\/wp\/v2\/pages\/845","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kirillsats23.thkit.ee\/wp\/index.php?rest_route=\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/kirillsats23.thkit.ee\/wp\/index.php?rest_route=\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/kirillsats23.thkit.ee\/wp\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kirillsats23.thkit.ee\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=845"}],"version-history":[{"count":3,"href":"https:\/\/kirillsats23.thkit.ee\/wp\/index.php?rest_route=\/wp\/v2\/pages\/845\/revisions"}],"predecessor-version":[{"id":849,"href":"https:\/\/kirillsats23.thkit.ee\/wp\/index.php?rest_route=\/wp\/v2\/pages\/845\/revisions\/849"}],"wp:attachment":[{"href":"https:\/\/kirillsats23.thkit.ee\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=845"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}