{"id":9688,"date":"2019-06-18T11:47:08","date_gmt":"2019-06-18T11:47:08","guid":{"rendered":"https:\/\/ittutorial.org\/?p=9688"},"modified":"2019-07-30T11:10:09","modified_gmt":"2019-07-30T11:10:09","slug":"pl-sql-programlama-11-trigger-kullanimi","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/pl-sql-programlama-11-trigger-kullanimi\/","title":{"rendered":"PL\/SQL Programlama -11 Trigger Kullan\u0131m\u0131"},"content":{"rendered":"<p>Merhaba, bu yaz\u0131da Trigger konusuna bakaca\u011f\u0131z bir \u00f6nceki yaz\u0131da fonksiyonlar\u0131 incelemi\u015ftik bu yaz\u0131ya ge\u00e7meden \u00f6nce incelemenizi tavsiye ederiz.<a href=\"https:\/\/ittutorial.org\/pl-sql-programlama-10\/\"> https:\/\/ittutorial.org\/pl-sql-programlama-10\/<\/a><\/p>\n<p>Trigger&#8217;\u0131n t\u00fcrk\u00e7e kar\u015f\u0131l\u0131\u011f\u0131 tetikleyci demektir veritaban\u0131nda bir insert, update vb gibi bir i\u015flem ger\u00e7ekle\u015fti\u011fininde bunu alg\u0131lay\u0131p istedi\u011fimiz bir\u015fey ger\u00e7ekle\u015ftirmeyi otomatize eder.<\/p>\n<p>Trigger&#8217;lar\u0131 \u015fu i\u015flemlerde kullanabiliriz;<\/p>\n<ul>\n<li>INSERT-UPDATE-DELETE<\/li>\n<li>ALTER-CREATE-DROP<\/li>\n<li>LOGON-LOGOFF-STARTUP-SHUTDOWN<\/li>\n<\/ul>\n<h5>Trigger Olu\u015fturma:<\/h5>\n<p>B\u00fct\u00fcn olu\u015fturma i\u015flemlerinde oldu\u011fu gibi trigger olu\u015ftururken de <strong>CREATE <\/strong> anahtar kelimesini kullan\u0131r\u0131z<\/p>\n<pre>CREATE [OR REPLACE] TRIGGER trigger_name\r\n\r\n{BEFORE | AFTER | INSTEAD OF }\r\n\r\n{INSERT [OR] | UPDATE [OR] | DELETE}\r\n\r\n[OF colum_name]\r\n\r\nON table_name\r\n\r\n[REFERENCING OLD AS o NEW AS n]\r\n\r\n[FOR EACH ROW]\r\n\r\nWHEN()\r\n\r\nDECLARE\r\n\r\n...............\r\n\r\nBEGIN\r\n\r\n.............\r\n\r\nEXCEPTION\r\n\r\n..................\r\n\r\nEND;<\/pre>\n<h4>BEFORE | AFTER | INSTEAD OF KULLANIMI:<\/h4>\n<p>Trigger&#8217;\u0131n hangi i\u015flemler \u00f6nce,sonra veya yerine \u00e7al\u0131\u015faca\u011f\u0131n\u0131 belirtebilece\u011fimiz opsiyonar da mevcuttur<\/p>\n<ul>\n<li>BEFORE<\/li>\n<li>AFTER<\/li>\n<li>INSTEAD OF<\/li>\n<\/ul>\n<h4>BEFORE:<\/h4>\n<p>Herhangi bir tablo i\u00e7erisinde de\u011fi\u015fiklil yapaca\u011f\u0131m\u0131z PL\/SQL kodu \u00e7al\u0131\u015fmadan \u00f6nce BEFORE Trigger&#8217;lar\u0131 \u00e7al\u0131\u015f\u0131r ve burada bizim verdi\u011fimiz kontrolleri yapar<\/p>\n<p>\u00d6rnek olarak sal\u0131 g\u00fcn\u00fc tabloya kay\u0131r girmeyi engelleyen bir trigger yazal\u0131m<\/p>\n<pre>CREATE OR REPLACE TRIGGER islem_kontrol\r\nBEFORE INSERT ON calisanlar\r\nBEGIN\r\nIF (to_char(sysdate,'DY') in ('TUE')) then\r\nraise_application_error(-20500,'Bu tabloya sal\u0131 g\u00fcn\u00fc kay\u0131 girilemez!');\r\nEND IF;\r\nEND;\r\n\/\r\n\r\ninsert into calisanlar values('Kemal',27,20000);\r\n<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-9860\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/06\/Screenshot_10-300x89.png\" alt=\"\" width=\"583\" height=\"173\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/06\/Screenshot_10-300x89.png 300w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/06\/Screenshot_10-768x228.png 768w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/06\/Screenshot_10.png 939w\" sizes=\"auto, (max-width: 583px) 100vw, 583px\" \/><\/p>\n<p>G\u00f6r\u00fcld\u00fc\u011f\u00fc gibi trigger sa\u011fl\u0131kl\u0131 bir \u015fekilde \u00e7al\u0131\u015fm\u0131\u015f.<\/p>\n<h4>AFTER:<\/h4>\n<p>BEFORE i\u015fleminin yapt\u0131\u011f\u0131 kontrolleri PL\/SQL kod bitiminde yaparak i\u015flemleri devam ettirir.<\/p>\n<p>\u00d6rnek olarak Employees tablosunda yap\u0131lan de\u011fi\u015fikli\u011fi log tablosuna kay\u0131t edelim.<\/p>\n<pre>CREATE OR REPLACE TRIGGER maas_kontrol\r\nAFTER UPDATE ON HR.EMPLOYEES\r\nBEGIN\r\nINSERT INTO log_emp values('Calisan\u0131n maas\u0131 de\u011fi\u015fti',sysdate);\r\nEND;\r\n\/\r\n\r\nupdate HR.employees SET salary=salary+1000 where employee_id=102;\r\n\r\nselect * from log_emp;<\/pre>\n<p>&nbsp;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-9867\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/06\/Screenshot_13-300x161.png\" alt=\"\" width=\"576\" height=\"309\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/06\/Screenshot_13-300x161.png 300w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/06\/Screenshot_13.png 617w\" sizes=\"auto, (max-width: 576px) 100vw, 576px\" \/><\/p>\n<p>Yeni bir Trigger olu\u015ftural\u0131m ve Employees tablosundaki de\u011fi\u015fiklikleri ekrana basal\u0131m.<\/p>\n<pre>CREATE OR REPLACE TRIGGER bolum_takip\r\nBEFORE DELETE OR INSERT OR UPDATE ON HR.EMPLOYEES\r\nFOR EACH ROW\r\nWHEN(NEW.employee_id&gt;0)\r\nDECLARE\r\nebolum varchar2(20);\r\nybolum varchar2(20);\r\nBEGIN\r\nINSERT INTO log_emp(islem,tarih) values('B\u00f6l\u00fcm De\u011fi\u015fti', SYSDATE);\r\nDBMS_OUTPUT.PUT_LINE('Eski b\u00f6l\u00fcm' || :NEW.department_id);\r\nDBMS_OUTPUT.PUT_LINE('Yeni b\u00f6l\u00fcm' || :OLD.department_id);\r\nEND;\r\n\/<\/pre>\n<p>update HR.EMPLOYEES set department_id=120 where employee_id=200;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-9869\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/06\/Screenshot_14-300x126.png\" alt=\"\" width=\"593\" height=\"249\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/06\/Screenshot_14-300x126.png 300w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/06\/Screenshot_14.png 339w\" sizes=\"auto, (max-width: 593px) 100vw, 593px\" \/><\/p>\n<p>Birka\u00e7 farkl\u0131 kod blo\u011fu var hemen onlara g\u00f6z atal\u0131m<\/p>\n<h3>FOR EACH ROW:<\/h3>\n<p>\u00c7al\u0131\u015fankodun tablo \u00fczerinde de\u011fi\u015fiklik yapt\u0131\u011f\u0131 her sat\u0131r i\u00e7in anlam\u0131ndad\u0131r.<\/p>\n<h3>WHEN:<\/h3>\n<p>PL\/SQLkodu i\u00e7erisinde ko\u015ful belirtti\u011fimiz aland\u0131r. FOR EACH ROW kulland\u0131\u011f\u0131m\u0131zda ROW kullan\u0131r\u0131z.<\/p>\n<h3>OLD-NEW:<\/h3>\n<p>Kodun de\u011fi\u015ftirdi\u011fi verilerin referans noktalar\u0131d\u0131. De\u011fi\u015fiklk \u00f6nceki hali ve sonraki halini bunlar\u0131 sorgulayarak g\u00f6r\u00fcnt\u00fcleyebiliriz.<\/p>\n<p>&nbsp;<\/p>\n<p>Bu yaz\u0131n\u0131n da sonuna geldik bir sonraki yaz\u0131da Trigger&#8217;larda kald\u0131\u011f\u0131m\u0131z yerden devam edece\u011fiz,<\/p>\n<p>G\u00f6r\u00fc\u015fmek \u00fczere..<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Merhaba, bu yaz\u0131da Trigger konusuna bakaca\u011f\u0131z bir \u00f6nceki yaz\u0131da fonksiyonlar\u0131 incelemi\u015ftik bu yaz\u0131ya ge\u00e7meden \u00f6nce incelemenizi tavsiye ederiz. https:\/\/ittutorial.org\/pl-sql-programlama-10\/ Trigger&#8217;\u0131n t\u00fcrk\u00e7e kar\u015f\u0131l\u0131\u011f\u0131 tetikleyci demektir veritaban\u0131nda bir insert, update vb gibi bir i\u015flem ger\u00e7ekle\u015fti\u011fininde bunu alg\u0131lay\u0131p istedi\u011fimiz bir\u015fey ger\u00e7ekle\u015ftirmeyi otomatize eder. Trigger&#8217;lar\u0131 \u015fu i\u015flemlerde kullanabiliriz; INSERT-UPDATE-DELETE ALTER-CREATE-DROP LOGON-LOGOFF-STARTUP-SHUTDOWN Trigger Olu\u015fturma: B\u00fct\u00fcn olu\u015fturma i\u015flemlerinde oldu\u011fu gibi trigger &hellip;<\/p>\n","protected":false},"author":67,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[5631],"tags":[3930,3664,3928,3931,3630,3658,3923,3632,3929,3927,3932,3925,3709,3926,3720,3718,3921,3922,3924,3710,3933,1572,3719],"class_list":["post-9688","post","type-post","status-publish","format-standard","","category-pl-sql-turkce","tag-basit-plsql","tag-bastan-sona-pl-sql","tag-pl-sql-case-when","tag-pl-sql-degiskenler","tag-pl-sql-dersleri","tag-pl-sql-document","tag-pl-sql-lesson","tag-pl-sql-nedir","tag-plsql-baslangic-dersleri","tag-plsql-case-yapisi","tag-plsql-degisken-atama","tag-plsql-donguler","tag-plsql-fonksiyonlar","tag-plsql-if-else","tag-plsql-kitaplari","tag-plsql-kurallari","tag-plsql-learning","tag-plsql-lessons","tag-plsql-ogrenmek","tag-plsql-prosedur","tag-set-serveroputput-on","tag-sql","tag-sql-ve-plsql"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/9688","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/users\/67"}],"replies":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/comments?post=9688"}],"version-history":[{"count":2,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/9688\/revisions"}],"predecessor-version":[{"id":9870,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/9688\/revisions\/9870"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=9688"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=9688"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=9688"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}