Notes

CREATE DATABASE TARIM_URETIM1
GO
USE TARIM_URETIM1
GO
CREATE TABLE BOLGELER
(
BOLGE_NO INT IDENTITY PRIMARY KEY,
BOLGE_ADI NVARCHAR(100)UNIQUE,
AKTIF BIT DEFAULT '1'
)
GO
CREATE TABLE ILLER
(
PLAKA_KODU INT PRIMARY KEY,
IL_ADI NVARCHAR(50) UNIQUE,
TELEFON_KODU NVARCHAR(5) UNIQUE,
AKTIF BIT DEFAULT '1',
BOLGE_ID INT,
FOREIGN KEY (BOLGE_ID) REFERENCES BOLGELER(BOLGE_NO),
)
GO
CREATE TABLE URUNLER
(
URUN_NO INT IDENTITY PRIMARY KEY,
URUN_ADI NVARCHAR(100) UNIQUE
)
GO
CREATE TABLE URUN_DONEMLERI
(
DONEM_NO INT IDENTITY PRIMARY KEY,
DONEM_TANIMI NVARCHAR(50) UNIQUE,
AKTIF BIT DEFAULT '1'
)
GO
CREATE TABLE IL_URUNLERI
(
IL_URUN_ID INT IDENTITY PRIMARY KEY,
MIKTAR INT,
)
GO
CREATE PROC SP_BOLGE_EKLE
@BOLGE_ADI NVARCHAR(100)
AS
INSERT INTO BOLGELER(BOLGE_ADI) VALUES (@BOLGE_ADI)
GO
EXEC SP_BOLGE_EKLE 'MARMARA BOLGESI'
GO
EXEC SP_BOLGE_EKLE 'EGE BOLGESI'
GO
EXEC SP_BOLGE_EKLE 'AKDENIZ BOLGESI'
GO
EXEC SP_BOLGE_EKLE 'IC ANADOLU BOLGESI'
GO
EXEC SP_BOLGE_EKLE 'KARADENIZ BOLGESI'
GO
EXEC SP_BOLGE_EKLE 'DOGU ANADOLU BOLGESI'
GO
EXEC SP_BOLGE_EKLE 'GUNEYDOGU ANADOLU BOLGESI'
GO
DECLARE @RowCount1 INTEGER;
GO
CREATE PROC SP_ILEKLE_SAY
@PLAKAKODU INT,
@ILADI NVARCHAR(100),
@TELEFON_KODU NVARCHAR(5),
@BOLGEID INT
AS
INSERT INTO ILLER(PLAKA_KODU,IL_ADI,TELEFON_KODU,BOLGE_ID) VALUES (@PLAKAKODU,@ILADI,@TELEFON_KODU,@BOLGEID);
SELECT COUNT(*) FROM ILLER AS Toplam_satır_sayısı;
GO
EXEC SP_ILEKLE_SAY '17','ÇANAKKALE','286','1'
EXEC SP_ILEKLE_SAY '35','IZMIR','232','2'
EXEC SP_ILEKLE_SAY '7','ANTALYA','242','3'
EXEC SP_ILEKLE_SAY '42','KONYA','332','4'
EXEC SP_ILEKLE_SAY '52','ORDU','452','5'
EXEC SP_ILEKLE_SAY '25','ERZURUM','442','6'
EXEC SP_ILEKLE_SAY '63','SANLIURFA','414','7'
GO
CREATE PROC SP_DONEMEKLE
@DONEM_TANIMI NVARCHAR(50),
@rowcount int output
AS
BEGIN TRY
INSERT INTO URUN_DONEMLERI(DONEM_TANIMI) VALUES (@DONEM_TANIMI)
set @rowcount = @@ROWCOUNT
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO
declare @rowCount int
declare @topla int
set @topla = 0
EXEC SP_DONEMEKLE '2010 YILI',@rowCount OUTPUT
set @topla = @topla + 1
EXEC SP_DONEMEKLE '2011 YILI',@rowCount OUTPUT
set @topla = @topla + 1
EXEC SP_DONEMEKLE '2012 YILI',@rowCount OUTPUT
set @topla = @topla + 1
EXEC SP_DONEMEKLE '2013 YILI',@rowCount OUTPUT
set @topla = @topla + 1
EXEC SP_DONEMEKLE '2014 YILI',@rowCount OUTPUT
set @topla = @topla + 1
EXEC SP_DONEMEKLE '2015 YILI',@rowCount OUTPUT
set @topla = @topla + 1
EXEC SP_DONEMEKLE '2016 YILI',@rowCount OUTPUT
set @topla = @topla + 1
Select @rowCount AS 'ETKILENEN SATIR'
print 'Etkilenen Satir sayisi'
print @topla
GO
CREATE PROC SP_URU_EKLE
@URUN_ADI NVARCHAR(100),
@SAYI INT
AS
BEGIN TRY
INSERT INTO URUNLER(URUN_ADI) VALUES (@URUN_ADI)
PRINT 'HATA YOK 1'
END TRY
BEGIN CATCH
PRINT 'HATA VAR -1'
END CATCH;
GO
CREATE TRIGGER URUN_EKLENDIGINDE_CALIS
ON URUNLER
AFTER INSERT
AS
INSERT INTO URUN_DONEMLERI VALUES
GO