Siz de benim gibi baz\u0131 hesaplamalar\u0131 veritaban\u0131na kaydetme s\u0131ras\u0131nda yapmay\u0131 sevenlerdenseniz veya b\u00f6yle bir ihtiyac\u0131n\u0131z varsa s\u0131kl\u0131kla bu i\u015fleri Trigger’ler \u00fczerinden halletmeniz gerekir.
\n
\nSQL Server’de bir Trigger’in hangi modda oldu\u011funu o an i\u00e7in bize s\u00f6yleyen bir i\u015flev yok. Bunu \u00f6\u011frenebilmek i\u00e7in INSERTED ve DELETED tablolar\u0131n\u0131n dolu olup olmad\u0131\u011f\u0131na bakman\u0131z ve bir \u00e7\u0131karsama yapman\u0131z gerekir. Bu i\u015fi kolayla\u015ft\u0131ran bir fonksiyon yazm\u0131\u015ft\u0131m, sizlerle payla\u015fman\u0131n faydal\u0131 olaca\u011f\u0131na inan\u0131yorum;<\/p>\n
SET ANSI_NULLS ON\nGO\nSET QUOTED_IDENTIFIER ON\nGO\n-- =============================================\n-- Author: \n-- Create date: <16.12.2014 12:46>\n-- Description: \n-- =============================================\n--CREATE FUNCTION dbo.fn_triggerkipi\n ALTER FUNCTION dbo.fn_triggerkipi\n ( @InsertedCount INT = 0 --> \u00d6rnek (SELECT TOP 1 COUNT(*) FROM INSERTED)\n , @DeletedCount INT = 0 --> \u00d6rnek (SELECT TOP 1 COUNT(*) FROM DELETED)\n )\nRETURNS VARCHAR(8)\nAS BEGIN\n RETURN\n CASE\n WHEN @InsertedCount > 0 AND @DeletedCount = 0 THEN 'ekle' --> INSERT Kipidir, DELETED tablosu bo\u015f gelir...\n WHEN @InsertedCount > 0 AND @DeletedCount > 0 THEN 'de\u011fi\u015ftir' --> UPDATE Kipidir, Her iki tablo da doludur... DELETED tablosu \u00f6nceki veriyi, INSERTED ise yeni veriyi tutar...\n WHEN @InsertedCount = 0 AND @DeletedCount > 0 THEN 'sil' --> DELETE Kipidir, INSERTED tablosu bo\u015f gelir...\n ELSE 'yok' --> Hi\u00e7biri , Her iki tablo da bo\u015ftur... (Ki asl\u0131nda parametre hatal\u0131ysa b\u00f6yle bir durum olu\u015fur)\n END;\nEND<\/trigger'lerin><\/u\u011fur><\/code><\/pre>\nKullan\u0131m\u0131 olduk\u00e7a basittir ve genel anlamda s\u0131k s\u0131k trigger’ler ile u\u011fra\u015f\u0131yorsan\u0131z bu bahsetti\u011fim ad\u0131m\u0131 basite indirgemenizde size yard\u0131mc\u0131 olacakt\u0131r.<\/p>\n
SET ANSI_NULLS ON\nGO\nSET QUOTED_IDENTIFIER ON\nGO\n-- =============================================\n-- Author : \n-- Create date : <2017-02-20>\n-- Description : <...>\n-- =============================================\nALTER TRIGGER [dbo].[TRG_Ajanda]\n ON [dbo].[Ajanda]\nFOR INSERT, DELETE, UPDATE\nAS BEGIN\n \n SET NOCOUNT ON;\n \n DECLARE @TRG_Durum VARCHAR(8) = dbo.fn_triggerkipi( (SELECT TOP 1 COUNT(*) FROM INSERTED), (SELECT TOP 1 COUNT(*) FROM DELETED) )\n \n IF (@TRG_Durum = 'ekle')\n or (@TRG_Durum = 'de\u011fi\u015ftir') BEGIN\n\n UPDATE X\n SET X.Taslak = ISNULL(I.Taslak, 0)\n FROM dbo.Ajanda as X LEFT OUTER\n JOIN INSERTED as I on I.Ref = X.Ref\n WHERE X.Ref in (SELECT DISTINCT Ref FROM INSERTED)\n ;\n END ELSE\n IF (@TRG_Durum = 'sil') BEGIN\n SET NOCOUNT ON; --> bu sat\u0131r\u0131n yerine silme i\u015flemi s\u0131ras\u0131nda bir log tutuyorsan\u0131z ilgili kodlar\u0131n\u0131z\u0131 bu noktaya yazabilirsiniz...\n END\nEND<\/u\u011fur><\/code><\/pre>\nG\u00f6r\u00fcld\u00fc\u011f\u00fc gibi 17. sat\u0131rda (@TRG_Durum) de\u011fi\u015fkenine hangi modda \u00e7al\u0131\u015ft\u0131\u011f\u0131 i\u015fleniyor ve devam\u0131ndaki IF blo\u011funda da mevcut duruma g\u00f6re SQL kodlar\u0131n\u0131z\u0131 birbirine kar\u0131\u015ft\u0131rmadan yazabiliyorsunuz.<\/p>\n","protected":false},"excerpt":{"rendered":"
Siz de benim gibi baz\u0131 hesaplamalar\u0131 veritaban\u0131na kaydetme s\u0131ras\u0131nda yapmay\u0131 sevenlerdenseniz veya b\u00f6yle bir ihtiyac\u0131n\u0131z varsa s\u0131kl\u0131kla bu i\u015fleri Trigger’ler \u00fczerinden halletmeniz gerekir.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[11,7,20],"class_list":["post-160","post","type-post","status-publish","format-standard","hentry","category-blog","tag-programlama","tag-sql-server","tag-trigger"],"_links":{"self":[{"href":"http:\/\/www.potansif.com\/index.php?rest_route=\/wp\/v2\/posts\/160","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.potansif.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.potansif.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.potansif.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.potansif.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=160"}],"version-history":[{"count":1,"href":"http:\/\/www.potansif.com\/index.php?rest_route=\/wp\/v2\/posts\/160\/revisions"}],"predecessor-version":[{"id":161,"href":"http:\/\/www.potansif.com\/index.php?rest_route=\/wp\/v2\/posts\/160\/revisions\/161"}],"wp:attachment":[{"href":"http:\/\/www.potansif.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=160"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.potansif.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=160"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.potansif.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=160"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}