Trigger, Stored Procedure Kullanımı ve Referential Integrity
Mesaj gönderen fahrettin » 17 Eyl 2003 04:29
Bu yazıda verilecek örnek kodlar Sybase Adaptive Server Anywhere ve Interbase6 üzerinde çalıştığı test edilmiş kodlardır. Kodlar içinde kullanılan SQL cümleleri standart SQL cümleleri olması sebebi ile diğer veritabanlarında da kolayca çalışacaktır. Sadece trigger ve stored procedure tanımlamalarındaki kod yapısı farklılıklar göstermektedir. Çalışacağınız veritabanında nasıl trigger ve stored procedure tanımlanabileceğini öğrenerek bu denemeleri yapmak mümkün olabilir. İtiraf edeyim ki yazı biraz uzun olduğu için tekrar baştan aşağı okumadım. Yaptığım hataları uyarmanız sonucu düzeltmekten memnuniyet duyarım.
Bir tablo üzerinde belirli bir olaya bağlı olarak tetiklenip çalışan SQL kodlarıdır. Tablo üzerindeki triggerları tetikleyen olaylar insert, update, delete olaylarıdır. Bu olaylara istinaden 3 ana tip triggerdan bahsedilir. Bunlar insert triggerı update triggerı, delete triggerı şeklindedir. Bir tablo üzerinde bu olayların öncesinde ve sonrasında tetiklenecek istenildiği kadar trigger yazılabilir. Fakat genel eğilim ve kullanım her bir olay için tek bir trigger kullanmak şeklindedir.
Örneğin stok hareketleri sonucunda stok miktarlarının azalması veya artması işlemlerinin, veya tahakkuk ve tahsilatlar sonucu cari hesapların etkilenmesi işlemlerinin triggerlar aracılığı ile yapılmaları tipik bir trigger kullanım yeridir. Ayrıca referential integrity’yi sağlamak amacı ile de trigger kullanımı çok tercih edilir. İlişkisel bir veritabanında örneğin PERSONEL tablosundaki kişinin bolum bilgisi amaçlı olarak BOLUM_NO tutulması ve bolumun adının da BOLUM tablosundan bulunması yapıldığını düşünürsek. Eğer 1 numaralı bolum herhangi bir personele kullanıldıysa BOLUM tablosundan BOLUM_NO değeri 1 olan kaydın kesinlikle silinememesi gerekmektedir. Bu tür kontrollerin yapılarak veri bütünlüğünün korunmasıdır referential integrity. Bu amaçla yazılan veya bir veritabanı tasarım aracı kullanıldıysa onun otomatik olarak yazdığı trigger kodları sayesinde bu bütünlük korunur. Çünkü BOLUM tablosunun delete trigger’inda gerekli kontrolleri yapacak kod yazılır ve eğer silinmek istenen BOLUM_NO herhangi bir personel için kullanıldıysa bu silme işlemine izin verilmez.
Stored Procedure ise bir tabloya bağlı olmaksızın veritabanı içinde tanımlanan belirli bir işi yapmaya yönelik kodlardır. Bu kodlar yazıldığı zaman aynı zamanda compile edildikleri için query optimizer tarafından optimize edilmiş en hızlı şekilde çalışmaya hazır kodlardır. Bu kodlar hem triggerlar içinden hem de veritabanı dışındaki her hangi bir ortamdan (Delphi içinden) kolayca çağırılabildikleri için kullanım amaçları geniştir.
Örneğin her türlü raporu stored procedure kullanarak yazmak mümkündür ve de tavsiye edilir. Örneğin Delphi tarafında veritabanı üzerinde işler yapan bir fonksiyonunuzu stored procedure haline getirmek işlemlerin çok daha hızlı çalışacak olması sebebi ile tavsiye edilir.
Hem trigger hem de stored procedure veritabanı üzerindeki kodlar olmaları sebebi ile veritabanını sunan Server üzerinde çalışırlar. Client&Server mimarinin güçlü enstrümanlarındandırlar. Client Server mimarideki SQL veritabanları tarafından desteklenmektedir. (Oracle, Sybase, MS SQL, Interbase, FireBird vs..) Verilerin bulunduğu Server üzerinde çalışmalarından dolayı veriler client ile Server arasında gidip gelmezler ve de serverdan client tarafına minimum veri çekilmiş olur. Ayrıca yazacağınız stored procedure verilerle çok yoğun uğraşmıyor bile olsa eğer güçlü bir sunucunuz varsa client makinenizin işlemcisinden tasarruf için bile bazı işleri server işlemcisine yüklemek adına stored procedure yazılabilir. Ya da çok kullanıcılı bir sistemde programlar içinde kullanılan tarih ve saat bilgisinin serverdan alınabilmesi için de stored procedure kullanmak basit de olsa bir kullanım şekli olabilir.
Bir örnek ile hem trigger hem de stored procedure kullanımının teknik detayına inelim. Söyle bir örneğimiz olsun. Çok basit olarak bir URUN tablosu ve bu tabloda stok miktarı tutuluyor olsun. URUN_GIRIS ve URUN_SATIS seklinde 2 tane de ürünlerin hareketlerini tutacağımız farklı tablolar olsun. Tabloların yapısı şu şekilde olsun.
URUN (URUN_NO, URUN_ADI)
URUN_GIRIS (URUN_NO, GIRIS_ZAMANI, GIRIS_MIKTARI)
URUN_SATIS (URUN_NO, SATIS_ZAMANI, SATIS_MIKTARI)
URUN_STOK (URUN_NO, STOK_MIKTARI)
Veritabanı tasarımının ERD (Entity Relationship Diagram) şeması şu şekildedir.
Tabloları ilişkisel bir şekilde oluşturacak SQL kodu son bölümde ek olarak verilmiştir.
Şimdi yapmak istediğimizi açıklayalım. URUN_GIRIS tablosuna kayıtlar girdikçe yani bir üründen belirli miktarlarda girişler yapıldıkça o ürüne ait URUN_STOK tablosundaki STOK_MIKTARI alanının değerini giriş miktarı kadar arttırmalıyız. Tabi ki bunun tersi de olabilmeli yani yapılmış bir ürün girişi kaydı silinirse de bu sefer STOK_MIKTARI değeri silinen giriş kadar azaltılarak eski haline getirilmeli. Ve de eğer giriş miktarı diyelim ki 100 adet iken 50 adet olarak değiştirilirse de bu sefer bu duruma göre o ürünün STOK_MIKTARI alanı güncellenebilmeli. Bunu şu şekilde yapacağız. Öncelikle bir stored procedure yazacağız bu stored procedure’e parametre olarak URUN_NO ve GIRIS_MIKTARI’ nı göndereceğiz ve ilgili ürünün STOK_MIKTARI’ nı giriş miktarı kadar arttıracak. Yazdığımız bu stored procedure’ u de URUN_GIRIS tablosunun insert triggerından çağıracağız.
Insert triggerından çağıracağımız stored procedure’un adı SPI_URUN_GIRIS olsun. Bu procedur’un kodu şu şekildedir.
Create procedure dba.SPI_URUN_GIRIS(@URUN_NO integer,@GIRIS_MIKTARI integer)
declare @DLR_KAYIT_SAYISI integer
select @DLR_KAYIT_SAYISI = count(*) from dba.URUN_STOK where URUN_NO = @URUN_NO
if @DLR_KAYIT_SAYISI = 0
insert into dba.URUN_STOK(URUN_NO,STOK_MIKTARI) values(@URUN_NO,0)
update dba.URUN_STOK set
STOK_MIKTARI = STOK_MIKTARI+@GIRIS_MIKTARI where
URUN_NO = @URUN_NO
CREATE PROCEDURE SPI_URUN_GIRIS (  URUN_NO INTEGER,  GIRIS_MIKTARI INTEGER)  AS
DECLARE VARIABLE DLR_KAYIT_SAYISI integer;
from URUN_STOK where URUN_NO = :URUN_NO
if (DLR_KAYIT_SAYISI=0) then
insert into URUN_STOK(URUN_NO,STOK_MIKTARI) values(:URUN_NO,0);
update URUN_STOK set
STOK_MIKTARI = STOK_MIKTARI+:GIRIS_MIKTARI where
URUN_NO = :URUN_NO;
Delete triggerından çağıracağımız stored procedure’un adı SPD_URUN_GIRIS olsun. Bu procedur’un kodu şu şekildedir.
Create procedure dba.SPD_URUN_GIRIS(@URUN_NO integer, @GIRIS_MIKTARI integer)
STOK_MIKTARI = STOK_MIKTARI-@GIRIS_MIKTARI where
delete from dba.URUN_STOK where
URUN_NO = @URUN_NO and STOK_MIKTARI = 0
CREATE PROCEDURE SPD_URUN_GIRIS (  URUN_NO INTEGER,  GIRIS_MIKTARI INTEGER)  AS
STOK_MIKTARI = STOK_MIKTARI-:GIRIS_MIKTARI where
delete from URUN_STOK
where URUN_NO=:URUN_NO and STOK_MIKTARI=0;
Bu stored procedure’lari insert ve delete triggerlarindan çağırılmasını da şöyle yapacağız. URUN_GIRIS tablosunun insert trigger’inin sonuna şu kodu ekleyeceğiz.
URUN_GIRIS tablosunun delete triggerının sonuna da şu kodlar eklenecek. Tabi bizim örneğimizde bu tablo için bir delete triggeri oluşmadığından delete triggerının oluşturulmasını da içerecek kodu verelim.
Create trigger tD_URUN_GIRIS after delete on DBA.URUN_GIRIS
referencing old as oldrows
CREATE TRIGGER TD_URUN_GIRIS FOR URUN_GIRIS AFTER DELETE POSITION 0 AS
execute procedure SPD_URUN_GIRIS OLD.URUN_NO, OLD.GIRIS_MIKTARI;
Triggerlara eklenen bu kodlar sayesinde bir ürün girişi olması durumunda veya bu ürün girişinin silinmesi durumunda URUN tablosundaki STOK_MIKTARI gerekli şekilde düzenlenmiş olacaktır. Bunu görebilmek için önce URUN tablosuna bilgi irişi anlamında şu kodları çalıştırarak tabloyu dolduralım.
insert into URUN(URUN_NO,URUN_ADI) values (1,'Defter');
insert into URUN(URUN_NO,URUN_ADI) values (2,'Kalem');
insert into URUN(URUN_NO,URUN_ADI) values (3,'Silgi');
Bu kodların çalışması sonucunda bütün ürünlerin STOK_MIKTAR larının sıfır olduğu görülebilir. Daha sonra da her üründen belirli miktarlarda giriş için URUN_GIRIS tablosuna kayıt girelim.
insert into URUN_GIRIS(URUN_NO,GIRIS_MIKTARI) values (1,10);
insert into URUN_GIRIS(URUN_NO,GIRIS_MIKTARI) values (2,20);
insert into URUN_GIRIS(URUN_NO,GIRIS_MIKTARI) values (3,25);
Bu kodları çalıştırdıktan sonra da STOK_MIKTARI alanının girişi yapılan miktarlar kadar arttığı görülecektir. Hatta yukarıdaki kodları tekrar çalıştırarak artan STOK_MIKTARI gözlenebilir. Şimdi de örneğin 3 numaralı ürün için yaptığımız girişlerin aslında olmayacağını anlayıp bunları silelim.
Delete from URUN_GIRIS where URUN_NO=3;
Bu durumda 3 numaralı ürün için STOK_MIKTARININ tekrar sıfıra düştüğü görülecektir.
Şimdi gelelim en çok hoşuma giden noktaya. Aslında iki tane stored procedure yazmak yerine bu procedureler içindeki kodları doğrudan trigger içinde yazabilirdik. Aslında doğru. Fakat iş update triggerına gelince iş biraz değişiyor. Kişi hem URUN_NO alanını update etmiş olabilir hem de GIRIS_MIKTARI alanını, dolayısıyla yapılan update ile ilgili bu en basit örneğimizde bile bir takım kontroller yapmak durumunda kalacağız. Sonuçta çalışan bir kodu yazmak belki çok da zor olmayabilir ama. Projenin daha komplike olduğunu ve de benim bir çok projemde olduğu gibi insert triggerından çağırılan procedure’un onlarca satırdan oluşabildiğini ve farklı farklı bir çok tabloya bilgi güncelleyebildiğini düşünürseniz. O zaman iş update triggerını hatasız yazmaya gelince çok zor olurdu eğer bu kodları procedure içinde değil de doğrudan trigger içinde yazsaydık.
Bu durumda yaptığımız ise aynen şudur. Update triggerı içinden update işlemini bir silme ve bir insert işleminin birleşimi gibi düşünüp update edilmeden önceki değerler ya da silinen değerler ile SPD ile başlayan yani delete triggerından çalıştırdığımız procedure’u, onun arkasından da yeni değerler ile yani insert edildiğini varsaydığımız değerler ile de SPI ile başlayan yani insert triggerından çağırılan procedure’u çağırmamız yeterli olacaktır. URUN_GIRIS tablosunun update triggerının sonuna eklenmesi gerekli kod şu şekildedir.
Şimdi de yeni durumda yapılan bir girişi elle değiştirerek yeni duruma göre STOK_MIKTARI’ nın değiştiğini görebilirsiniz.
Yeri gelmişken burada nemli bir noktaya değinelim. Örneğin:
şöyle bir kod çalıştırmanın sonucunu nasıl bekliyoruz? Yaptığımız bütün girişleri 5 adet arttırdığımıza göre her giriş miktarının 5 arttırılması sırasında stored procedurelerimiz çalışacak ve gerekli STOK_MIKTARI artışlarını sağlayacaktır. Yani örneğin 1 numaralı ürün için 7 ve 10 adetlik farklı iki giriş olduysa stokta 17 adet var demektir. Bu girişler 12 ve 15’e değişeceği için stok miktarı da 27’ye değişecektir. Eğer kullandığınız veritabanı row level triggerı destekliyorsa aynen böyle olacaktır. Yani triggerlar her row için ayrı ayrı çalışacaktır. Binlerce kaydınız olsa bile. Ama eğer statement level trigger desteği olan bir veritabanı kullanıyorsanız o zaman insert triggerınız sadece bir defaya mahsus çalışacağı için beklediğimiz etki olmayacaktır. Statement level trigger destekli veritabanı kullanan arkadaşların bu duruma göre daha farklı kodlar yazmaları gerekmektedir. Eğer değişmediyse benim bilgim dahilinde MS SQL statement level trigger destekliyordu. Bunun haricinde Oracle, Sybase, Interbase ve FireBird gibi veritabanları row level trigger desteği vermektedirler.
Benzer şekilde URUN_SATIS tablosunun triggerlarından çağırılmak üzere stored procedureleri ve triggerları yazalım.
create procedure dba.SPI_URUN_SATIS(@URUN_NO integer,@SATIS_MIKTARI integer)
STOK_MIKTARI = STOK_MIKTARI-@SATIS_MIKTARI where
create procedure dba.SPD_URUN_SATIS(@URUN_NO integer, @SATIS_MIKTARI integer)
STOK_MIKTARI = STOK_MIKTARI+@SATIS_MIKTARI where
CREATE PROCEDURE SPI_URUN_SATIS (  URUN_NO INTEGER,  SATIS_MIKTARI INTEGER)  AS
STOK_MIKTARI = STOK_MIKTARI-:SATIS_MIKTARI where
CREATE PROCEDURE SPD_URUN_SATIS (  URUN_NO INTEGER,  SATIS_MIKTARI INTEGER)  AS
STOK_MIKTARI = STOK_MIKTARI+:SATIS_MIKTARI where
Sybase URUN_SATIS tablosunun delete triggerı su şekilde oluşturulacaktır.
create trigger tD_URUN_SATIS after delete on DBA.URUN_SATIS
Interbase URUN_SATIS tablosunun insert triggerı sonuna eklenecek kod:
execute procedure SPI_URUN_SATIS  NEW.URUN_NO, NEW.SATIS_MIKTARI;
Interbase URUN_SATIS tablosunun delete triggerı su şekilde oluşturulacaktır.
CREATE TRIGGER TD_URUN_SATIS FOR URUN_SATIS AFTER DELETE POSITION 0 AS
execute procedure SPD_URUN_SATIS OLD.URUN_NO, OLD.SATIS_MIKTARI;
Interbase URUN_SATIS tablosunun update triggerı sonuna eklenecek kod:
execute procedure SPI_URUN_SATIS NEW.URUN_NO, NEW.SATIS_MIKTARI;
Stored Procedure ile rapor hazırlamaya da bir örnek verecek olursak. İstenilen bir tarih aralığında stok hareketlerini listeleyecek bir raporu bu yapıdan alabilmek için şöyle bir stored procedure hazırlamalıyız.
create procedure dba.SP_TARIH_ARALIGINDA_STOK_HAREKETLERI(@BASLANGIC_TARIHI date,@BITIS_TARIHI date)
select TARIH=UG.GIRIS_ZAMANI,U.URUN_ADI,HAREKET_TIPI='GIRIS',MIKTAR=UG.GIRIS_MIKTARI from
URUN_GIRIS as UG,URUN as U where
UG.URUN_NO = U.URUN_NO and convert(date,UG.GIRIS_ZAMANI) between @BASLANGIC_TARIHI and @BITIS_TARIHI union
select TARIH=US.SATIS_ZAMANI,U.URUN_ADI,HAREKET_TIPI='SATIS',MIKTAR=US.SATIS_MIKTARI from
URUN_SATIS as US,URUN as U where
US.URUN_NO = U.URUN_NO and convert(date,US.SATIS_ZAMANI) between @BASLANGIC_TARIHI and @BITIS_TARIHI order by
1 asc,2 asc
CREATE PROCEDURE SP_STOK_HAREKETLERI (  BASLANGIC_TARIHI DATE,  BITIS_TARIHI DATE) RETURNS (  TARIH DATE,  URUN_ADI CHAR(40),  HAREKET_TIPI CHAR(5),  MIKTAR INTEGER) AS
select UG.GIRIS_ZAMANI,U.URUN_ADI,'GIRIS',UG.GIRIS_MIKTARI from
URUN_GIRIS UG,URUN  U where
UG.URUN_NO = U.URUN_NO and cast(UG.GIRIS_ZAMANI as date) between :BASLANGIC_TARIHI and :BITIS_TARIHI union
select US.SATIS_ZAMANI,U.URUN_ADI,'SATIS',US.SATIS_MIKTARI from
URUN_SATIS  US,URUN  U where
US.URUN_NO = U.URUN_NO and cast(US.SATIS_ZAMANI as date) between :BASLANGIC_TARIHI and :BITIS_TARIHI order by
INTO :TARIH, :URUN_ADI, :HAREKET_TIPI,:MIKTAR
Bu procedure’u SQL explorer’dan çağırarak test edebilmek için şu kod yeterlidir.
CALL DBA.SP_TARIH_ARALIGINDA_STOK_HAREKETLERI('2003.01.01' , '2003.12.31' )
select * from SP_STOK_HAREKETLERI ('2003.01.01','2003.12.31')
Delphi içinden ilgili procedure’u çağırabilmek için bir Tquery’nin SQL özelliğine aşağıdaki kodu yazıp ilgili parametrelerine de gerekli değerleri atamak yeterlidir.
DBA.SP_TARIH_ARALIGINDA_STOK_HAREKETLERI :BASLANGIC_TARIHI, :BITIS_TARIHI
select * from SP_STOK_HAREKETLERI (:BASLANGIC_TARIHI, :BITIS_TARIHI)
Umarım bu basit örnek ile trigger ve stored procedure kullanımı konusunda yardımcı olabilmişimdir.
Özetle şunu söylemek isterim ki bu tarz veritabanı tarafında olup biten hadiselerin mutlaka ama mutlaka örnekteki yöntemlerle yapılmasını öneririm. Bu şekilde yazacağınız bir stok takip ve cari takip programının stok değerlerinde ve cari değerlerinde data girişi sonucunda hata oluşma ihtimali sıfırdır. Bütün bu işlemlerin Delphi tarafında yapılması da mümkün olmakla birlikte daha fazla kod daha fazla kontrol gerektirmekte ve risk taşımaktadır. Şöyle ki bir gün tablolarınıza sizin program dışında bilgi girişi yapacak bir başka arabirim ortaya çıktığında mesela SQL Explorer’dan elle bilgi girmeniz söz konusu olduğunda stok miktarları veya cari hesaplar gelişmelerden habersiz beklemekle yetineceklerdir. Oysa bu işlemlerin veritabanı tarafında yapılması sayesinde triggerları tetiklemeyecek bir bilgi girişi olamayacağı için siz programını içinde farklı ekranlarda farklı şeyler yaparken cari hesapları tamamen unutabilirsiniz. Oraları da etkileyecek kodlar yazmakta olup olmadığınızı düşünmenize gerek yok.
Egzersiz Tavsiyesi: Bu örnekte URUN_STOK tablosundaki STOK_MIKTARI alanı girişlerle arttırılıp çıkışlarla da azaltılarak stok miktarı takip edilmiştir. Fakat seminerde yapılan örnekte girişler TOPLAM_GIRIS isimli bir alana eklenip çıkışlar da TOPLAM_CIKIS isimli bir başka alana eklenerek ve aradaki farktan hareketle de stok miktarı bilgisinin üretilmesi daha uygun bir yöntemdir. Böylece anlık olarak toplam giriş ve toplam çıkış miktarları da takip edilebilmektedir. Bu örnek üzerinde çalışma yapan arkadaşlar bu örneği bu şekilde bir yapıya dönüştürecek bir çalışmayı gerçekleştirebilirler.
Sybase Adaptive Server Anywhere için tabloların, indekslerin ve referential integrity’yi sağlayacak şekilde trigger’lari oluşturacak kod:
CREATE TABLE URUN (
URUN_NO              INTEGER NOT NULL,
URUN_ADI             VARCHAR(40) NOT NULL,
PRIMARY KEY (URUN_NO) );
CREATE UNIQUE INDEX XPKURUN ON URUN( URUN_NO ASC);
CREATE TABLE URUN_SATIS (
SATIS_ZAMANI DATETIME NOT NULL DEFAULT CURRENT TIMESTAMP,
SATIS_MIKTARI        INTEGER NOT NULL,
PRIMARY KEY (URUN_NO, SATIS_ZAMANI));
CREATE UNIQUE INDEX XPKURUN_SATIS ON URUN_SATIS
(  URUN_NO ASC, SATIS_ZAMANI ASC);
CREATE INDEX XIF1192URUN_SATIS ON URUN_SATIS( URUN_NO ASC);
CREATE TABLE URUN_GIRIS (
GIRIS_ZAMANI  DATETIME NOT NULL DEFAULT CURRENT TIMESTAMP,
GIRIS_MIKTARI        INTEGER NOT NULL,
PRIMARY KEY (URUN_NO, GIRIS_ZAMANI) );
CREATE UNIQUE INDEX XPKURUN_GIRIS ON URUN_GIRIS
( URUN_NO ASC,   GIRIS_ZAMANI ASC);
CREATE INDEX XIF1191URUN_GIRIS ON URUN_GIRIS( URUN_NO ASC);
CREATE TABLE URUN_STOK (
STOK_MIKTARI         INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY (URUN_NO));
CREATE UNIQUE INDEX XPKURUN_STOK ON URUN_STOK(URUN_NO ASC);
create trigger tD_URUN after DELETE on URUN
declare parent_delrstrct_err EXCEPTION FOR SQLSTATE VALUE 'Parent Delete Restrict';
declare child_delrstrct_err EXCEPTION FOR SQLSTATE VALUE 'Child Delete Restrict';
select count(*) into numrows
where  URUN_SATIS.URUN_NO = oldrows.URUN_NO;
if (numrows > 0)  then
where  URUN_GIRIS.URUN_NO = oldrows.URUN_NO;
if (numrows > 0)    then
create trigger tU_URUN after UPDATE on URUN
referencing old as oldrows new as newrows
declare parent_updrstrct_err EXCEPTION FOR SQLSTATE VALUE 'Parent Update Restrict';
declare child_updrstrct_err EXCEPTION FOR SQLSTATE VALUE 'Child Update Restrict';
if   oldrows.URUN_NO <> newrows.URUN_NO   then
set  URUN_SATIS.URUN_NO = newrows.URUN_NO
where    URUN_SATIS.URUN_NO = oldrows.URUN_NO;
if    oldrows.URUN_NO <> newrows.URUN_NO  then
set    URUN_GIRIS.URUN_NO = newrows.URUN_NO
create trigger tI_URUN_SATIS after INSERT on URUN_SATIS
referencing new as newrows
declare parent_insrstrct_err EXCEPTION FOR SQLSTATE VALUE 'Parent Insert Restrict';
declare child_insrstrct_err EXCEPTION FOR SQLSTATE VALUE 'Child Insert Restrict';
where  newrows.URUN_NO = URUN.URUN_NO;
if   (numrows = 0)    then
create trigger tU_URUN_SATIS after UPDATE on URUN_SATIS
if   oldrows.URUN_NO <> newrows.URUN_NO  then
if (numrows = 0 )  then
create trigger tI_URUN_GIRIS after INSERT on URUN_GIRIS
where   newrows.URUN_NO = URUN.URUN_NO;
if (numrows = 0  )    then
create trigger tU_URUN_GIRIS after UPDATE on URUN_GIRIS
where    newrows.URUN_NO = URUN.URUN_NO;
if (numrows = 0  )   then
Interbase6 için tabloların, indekslerin ve referential integrity’yi sağlayacak şekilde trigger’lari oluşturacak kod.
CREATE UNIQUE INDEX XPKURUN ON URUN(       URUN_NO);
GIRIS_ZAMANI         DATE DEFAULT 'now' NOT NULL,
PRIMARY KEY (URUN_NO, GIRIS_ZAMANI));
(       URUN_NO,       GIRIS_ZAMANI);
CREATE INDEX XIF803URUN_GIRIS ON URUN_GIRIS(       URUN_NO);
SATIS_ZAMANI         DATE DEFAULT 'now' NOT NULL,
(       URUN_NO,       SATIS_ZAMANI);
CREATE INDEX XIF804URUN_SATIS ON URUN_SATIS(       URUN_NO);
STOK_MIKTARI         INTEGER DEFAULT 0 NOT NULL,
CREATE UNIQUE INDEX XPKURUN_STOK ON URUN_STOK(   URUN_NO);
CREATE EXCEPTION ERWIN_PARENT_INSERT_RESTRICT 'Cannot INSERT Parent table because Child table exists.';
CREATE EXCEPTION ERWIN_PARENT_UPDATE_RESTRICT 'Cannot UPDATE Parent table because Child table exists.';
CREATE EXCEPTION ERWIN_PARENT_DELETE_RESTRICT 'Cannot DELETE Parent table because Child table exists.';
CREATE EXCEPTION ERWIN_CHILD_INSERT_RESTRICT 'Cannot INSERT Child table because Parent table does not exist.';
CREATE EXCEPTION ERWIN_CHILD_UPDATE_RESTRICT 'Cannot UPDATE Child table because Parent table does not exist.';
CREATE EXCEPTION ERWIN_CHILD_DELETE_RESTRICT 'Cannot DELETE Child table because Parent table does not exist.';
CREATE TRIGGER tD_URUN FOR URUN AFTER DELETE AS
DECLARE VARIABLE numrows INTEGER;
where        URUN_SATIS.URUN_NO = OLD.URUN_NO into numrows;
IF (numrows > 0) THEN
URUN_GIRIS.URUN_NO = OLD.URUN_NO into numrows;
CREATE TRIGGER tU_URUN FOR URUN AFTER UPDATE AS
IF    (OLD.URUN_NO <> NEW.URUN_NO) THEN
set    URUN_SATIS.URUN_NO = NEW.URUN_NO
where        URUN_SATIS.URUN_NO = OLD.URUN_NO;
set    URUN_GIRIS.URUN_NO = NEW.URUN_NO
where      URUN_GIRIS.URUN_NO = OLD.URUN_NO;
CREATE TRIGGER tI_URUN_GIRIS FOR URUN_GIRIS AFTER INSERT AS
where      NEW.URUN_NO = URUN.URUN_NO into numrows;
IF (      numrows = 0    ) THEN
CREATE TRIGGER tU_URUN_GIRIS FOR URUN_GIRIS AFTER UPDATE AS
IF (   numrows = 0  ) THEN
CREATE TRIGGER tI_URUN_SATIS FOR URUN_SATIS AFTER INSERT AS
where        NEW.URUN_NO = URUN.URUN_NO into numrows;
IF (     numrows = 0    ) THEN
CREATE TRIGGER tU_URUN_SATIS FOR URUN_SATIS AFTER UPDATE AS
IF (    numrows = 0  ) THEN
En son fahrettin tarafından 01 Eyl 2004 09:05 tarihinde düzenlendi, toplamda 2 kere düzenlendi.
Mesaj gönderen naile » 27 Oca 2004 05:05
Ben de affınıza sığınarak SQL Serverda hazıladığım basit bir triggerı örnek olarak gönderiyorum.
İki tane tablo var SICIL ve SICILEK ikisinde de ortak olarak ISYERI ve SICILNO diye iki alan var. SICIL tablosunda bu iki alana göre silme yapıldığında (bunlar primary key) SICILEK de de aynı ISYERI ve SICILNO kaydını bulup siliyor. @ISYERI ve @SICILNO değişken, silinen tablodan aldığı ISYERI ve SICILNO değerleri bu değişkenlere atanıp silinecek tabloadan (yani SICILEK) silme işlemi gerçekleştiriliyor.
CREATE     TRIGGER DELETE_SICIL ON dbo.SICIL FOR DELETE AS
DECLARE @ISYERI VARCHAR(8)
DECLARE @SICILNO VARCHAR(15)
DECLARE SILINENLER CURSOR FOR SELECT ISYERI,SICILNO FROM DELETED
FETCH NEXT FROM SILINENLER INTO  @ISYERI, @SICILNO
DELETE FROM SICILEK WHERE ISYERI=@ISYERI AND SICILNO=@SICILNO
Mesaj gönderen fahrettin » 29 Oca 2004 10:21
estagfirullah ne demek... Hatta SQL server'dan bir trigger ornegi vermeniz , makalede bahsettigim row level ve statement level trigger desteginin farkini ortaya koymak adina iyi oldu. Aslinda statement level trigger destegi benim sevmedigim biraz da kullanıssız oldugunu dusundugum bir yapıdır. Ama malesef SQL server (populer DB'ler icinde tek veritabanı olarak biliyorum statement level trigger destegi sunan) statement level trigger da ısrarlı. Boyle olunca da yazdiginiz koddaki gibi ayni anda bir cok kayit silinmis olma ihtimaline karsi silinen butun kayitlari bir cursor ile tarayip her birisi icin silme kodunu calistirmak gerekiyor. Oysa ayni kodu mesela Oracle, Sybase veya Interbase gibi row level trigger destegi veren bir veritabanında yazinca sonuc su sekilde oluyor....
CREATE trigger DBA.tD_SICIL after delete on DBA.SICIL
delete from SICILEK
where SICILEK.ISYERI = oldrows.ISYERI and SICILEK.SICILNO=oldrows.SICILNO;
Bu kod zaten silinen her kayit icin calistirildigindan dolayi bir dongu gerekmiyor.
Aslinda statement level trigger ile bu tur islerin nasil yapildigini da mantik olarak tahmin etmekle birlikte kod olarak merak ediyordum. Merakimi da gideren katkinizdan dolayi tesekkur ederim...
Kayıt: 09 Ağu 2003 01:11
Mesaj gönderen name » 16 Nis 2004 02:05
Stored procedure'ler ile ilgili bilgi topluyorum ve Fahrettin beyin bu makalesinide inceledim tabiki. Bu makalede aklıma bir soru takıldı ve bunu Fahrettin beye sormak istiyorum.
Fahrettin bey, makalenizde, basit bir stok takibi örneği vermişsiniz. Örnek data yapısında "URUN,URUN_GIRIS,URUN_SATIS ve URUN_STOK" isminde 4 tablo var. Sorum şu, ürün hareketleri (giriş,satış) databasede bulunduğu için stok durumunu ayrı bir tabloda (URUN_STOK) tutmak gereklimidir? İstenirse, sizin örnek verdiğiniz, stok durumunun kontrolü için, kullanılan 6 adet sp yerine, tek bir sp yazılarak girişlerin ve satışların farkı alınabilir. Bu yöntemin belkide bir çok dezavantajı var. Tecrübeleriniz ile eğer dezavantajları var ise belirtiriseniz çok sevinirim.
Mesaj gönderen fahrettin » 16 Nis 2004 02:53
Oncelikle şunu hatirlatmak isterim ki bu konuları daha detaylı konuşabileceğimiz bir imkanımız olacak...
8. 30 Mayıs 2004 - Sitedeki makale : (Fahrettin)(14:30 - 17:00 arası)
planladığımız 8. eğitimin konusu da bu zaten....
Onerinize gelince... Anladığım kadarı ile stok durumu öğrenilmek istendiğinde çalıştıracağınız bir procedure ile o anki stok durumunu hesaplamak ise. En onemli ve bu hali ile bile bu yontemi uygulamanın onundeki engel, yogun stok hareketlerinden sonra bir urun icin anlik stok durumunu ogrenmek icin her defasinda bunu belki binlerce kayit icinden ogrenerek bulmak performans acisindan dogru degil. Gerce kisinin amaci o anki stok durumu nedir diye bir soruya karsilik bir raporda bunu gormek isterse bu raporun gelemesini beklemye tahammul edebilir tabi. Cunku oyle uzun surmez belki de aninda geldigi hissedilir. Fakat program icin bir cok hesap sırasında o anki stok durumlarinin kontrol edilmesi gerektigini dusunursek eger. Bir takım hos olmayan yavaslamalara ve veritabanı sunucusunu gereksiz bir yogunluga itmis oluruz. Mesela stok miktarinin negatife dusmesini engellemek istiyorsaniz her kayıt guncellemede bu procedure'un calismasi ve mevcut data ile degil guncellenmek istenen data ile birlikte stok miktarının sifirin altina dusecegini tespit ederek bu duruma engel olmak gerekmektedir. Oysa makalade onerilen yontem ile URUN_STOK tablosuna bir check constraint koyarak bu durum halledilir... Bu ve bunun gibi bir takim kontrolleri de dusunursek diger deavantajlarina paralel olarak belki de yazilan toplam kod daha bile fazla olabilir.
Mesaj gönderen name » 16 Nis 2004 10:19
Cevabınız için teşekkürker Fahrettin bey. Aslında benimde hem fikir olduğum bir nokta, ürün hareketi oluştukça ürün durumunun yeniden düzenlenmesi. Fikrinizi öğrenmek istemiştim bir kez daha teşekkürler.
Örnek olacağını düşünerek stok durumunun hesaplanmısını uygulayarak denemek istedim ama beceremedim
Aşağıdaki sql kodunu yazdım fakat stok miktarlarının yanlış hesaplanması yada hiç hesaplanmaması gibi çok farklı sonuçlar üretiyor. Nasıl bir Sql ifadesi yazmamız gerekli?
select giris.URUN_KOD,  sum(giris.miktar)-sum(satis.miktar)
group by giris.urun_kod,satis.URUN_KOD
order by giris.urun_kod
Mesaj gönderen name » 16 Nis 2004 10:25
Özür diliyerek bir önceki mesajımdaki sql ifadesini makaledeki örneğe göre değiştiriyorum.
select URUN_GIRIS.URUN_NO,  sum(URUN_GIRIS.GIRIS_MIKTARI)-sum(URUN_SATIS.SATIS_MIKTARI)
group by URUN_GIRIS.URUN_NO,URUN_SATIS.URUN_NO
order by URUN_GIRIS.URUN_NO
Mesaj gönderen fahrettin » 19 Nis 2004 11:00
oncelikle mevcut kodda where kısmı eksik o yuzden sonuc cikmamasi normal. Fakat eklesek bile bazi sorunlar olabilir yani girişi olmayan cikislar (sacma ama belki imkan veriyopr olabiliriz fiili sebeplerden dolayi) ya da çıkışı olmayan girişlerde sorun cikabilir. left outer join ile filan halledilse de biraz daha kafa karistiran bir yontem olur bence..
en uygun kod soyle olur diye dusunuyorum. Kodu denemedim belki yazim hatasi olabilir ama:
TOPLAM_GIRIS=(Select isnull(sum (UG.GIRIS_MIKTARI),0) from URUN_GIRIS as UG where UG.URUN_NO=U.URUN_NO ),
TOPLAM_SATIS=(Select sum (US.SATIS_MIKTARI) from URUN_SATIS as US where US.URUN_NO=U.URUN_NO ),
from URUN as U
order by U.AD
Sadece bakiye de alinabilirdi. Boyle bir kod ile toplam giriş ve toplam satış da gorulebiliyor...
Mesaj gönderen metemete » 09 May 2004 07:49
verdiğiniz sayfadaki triger örneklerini calıstırdım ama
integer değerler numeric olarak oluşturamıyorum
bazı yerlerde hatalar veriyor?
Mesaj gönderen fahrettin » 10 May 2004 07:17
tam olarak anlayamadım.... Nasıl bir hata veriyor? Sybase icin konusuyoruz degil mi? Eger aynı ornekleri deniyorsanız numeric yapmanıza gerek yok. Integer daha uygun...
Mesaj gönderen metemete » 10 May 2004 08:56
evet hocam sysbase.integer da sınırlı kayıt olmuyormu max integer değerden daha büyük bir rakam girilemiyordu.bu yüzden tüm integer değerleri numeric yapıyorum.triggerlerde hata vermiyor.ürün satış için oluşturacağım Stored Procedurede hata veriyor.tam olarak hatayı yarın verebilirim.şuan şirketteyim
Mesaj gönderen fahrettin » 10 May 2004 09:18
Dogru integer'in 2 milyar kusur gibi bir sınırı var... Ama eger kullandıgınız alanın ozelligi geregi tam sayı oalcaksa double kullanmanızı oneririm... Ama eger decimal bir deger alabilecekse o zaman numeric kullanmada da bir sorun yok....
Hataya gelince belki SP icinde tanımlı bir degiskenin tipi integer olabilir ve buyuk bir rakamı atamaya calisiyor olabilirsiniz. veya SP parametreleri benzer şekilde integer kalmışi olabilir... Kesin teşhis tabi ki net hata mesajı ile daha kolay olacaktır....
Bu arada 23 mayıs ve 30 mayıstaki seminerlerde egitimin geneli sybase uzerinden olacak katılmanızı hararetle tavsiye ederim....
Mesaj gönderen metemete » 13 May 2004 12:14
örneklerdeki karşılaştığım sorunlar
* DATETIME hata veriyordu datetime olarak değiştirince düzeldi
* Delete from URUN_GIRISI where URUN_NO=3;---> olmadı
Delete from URUN_GIRIS where URUN_NO=3;----> oldu
1.double yerine numeric kullanmak daha avantajlı olmazmı?
örneğin kullanıcı ürün kod olarak barkod kodu veya daha uzun rakam girmesi gerekirse?
2.Tüm İnteger Değişkenleri numeric olarak değiştirdim
aşağadaki trigger oluştururken sorun cıkıyor
Trigger definition conflicts with existing triggers
SQLCODE=-271,ODBC 3 State="42S01"
Seminerlere gelmeyi ve sizin gibi değerli insanlarla tanışmayı bende çok arzu ediyorum hocam ama maalesft samsunda ve çalışıyor olduğumdan oldukça zor benim için ama inşallah vcd lerinizi izleyeceğim.Tek ricam iyi çekim olması.
Mesaj gönderen fahrettin » 13 May 2004 07:55
Tablo adı URUN_GIRIS oldugu icin durum normal tabi
Double'i tavsiye etmemin sebebi eger kullanılan primary key 1 den baslayip artarak gidecek ise hangi boyutlarda kalabilecegine karar verip ona uygun bir tip secmekti.
Mesela istanbul'un ilşelerinin tutulduğu bir tabloda ilceler icin bir ILCE_NO alanı olacaksa zaten 30 civarındaki ilce icin en uygunu tinyint kullanmak olur 256 ya kadar destegi var cunku....
Ama eger il tablonuz varsa o zaman il sayısı surekli artan ulkemizde 256 rakam sınırı uzun vadede riskli olabilir . bir de giris problemleri sil tekrar ekle gibi meselelerden dolayı gereksiz numara artışlarını da düşününce smallint yapmak 32bin kusur bir rakam saglar ve yeterli olur....
Eger onun da yeterli olmayacagi bir alan olursa integer, sonra da double vs..... Velhasıl sizin onriniz de zaten bu çerçevede dogru yani eger oraya bir barkod girilecekse numeric olsun tabi... Ama bu amaçla butun tablolardaki primary alanların numeric olmasına gerek yok....
Bu mesajın anlamı aynı isimle zaten bir trigger var aynı isimle create edemezsiniz diyor....
Alter trigger tD_URUN_GIRIS after delete on DBA.URUN_GIRIS
olarak değiştirin. Create statement ile sıfırdan oluşturmak mumkun iken Alter ile de var olan bir trigger'i yenisi ile değiştirmek mümkündür... Bu durum SP, table vs... icin de geçerlidir....
Mesaj gönderen metemete » 13 May 2004 11:12
Insert triggerından çağıracağımız stored procedure?un adı SPI_URUN_GIRIS olsun. Bu procedur?un kodu şu şekildedir.
SPI Stored Procedure Insert anlamında sanırım.SPI yerine baska bişey yazsak farkedermi?
karekterindeki değişkenlerin anlamı ne? as ne anlamda ?
değişkenin türünü tanımlıyor?
URUN_STOK toplam kayıt sayısı alınıyor.URUN NO kısmı ne işe yarıyor?
urunstok toplam kayıt sayısı=0 sa aşağıdaki işlemleri yapıyor,değilse işlem yapmıyormu?
anladığım kadarıyla yeni ürün_girişi yapıldığı zaman urun_stok miktarını değiştiriyor.
aynı zamanda girdiğimiz ürün , ürünstokda tanımlı değilse tanımlanıyor ve her satır her komut ne işe yarar tam olarak bana açıklayabilirseniz çok memnun olurum?
mantığı ve komutları tam olarak anlamak istiyorum