7t0UMkFc

1/* 2 ****************  3 procedure – po jednu za INSERT, UPDATE, DELETE naredbu; 
2procedure treba da sadrže logiku pomoću koje se proverava ispravnost podataka i sprečava pojava greške; 
3za greške čija se pojava ne može sprečiti, uraditi obradu greške pomoću posebno kreirane procedure 
4(koja će omogućiti ubacivanje informacija o greškama u tabelu; potrebno je da se kreira nova tabela)  *********************/
5
6
7  -- Kreiranje tabele za cuvanje gresaka -- 
8CREATE TABLE [dbo].[ErrorLog](
9	[ErrorID] [int] IDENTITY(1,1) NOT NULL,
10	[ErrorTime] [datetime] NULL,
11	[ErrorNumber] [int] NOT NULL,
12	[ErrorSeverity] [int] NOT NULL,
13	[ErrorState] [int] NULL,
14	[ErrorProcedure] [nvarchar](126) NULL,
15	[ErrorLine] [int] NULL,
16	[ErrorMessage] [nvarchar](4000) NULL,
17	[UserName] [nvarchar](50) NULL,
18	[Resolved] [bit] NULL,
19 CONSTRAINT [PK_ErrorLog] PRIMARY KEY CLUSTERED (	[ErrorID] ASC ));
20 GO
21
22 -- Kreiranje Stored Procedure za cuvanje informacija o greskama --
23CREATE PROCEDURE dbo.usp_ErrorHandler
24AS
25BEGIN
26	BEGIN TRY
27		INSERT INTO [dbo].[ErrorLog]
28		(
29			  [ErrorNumber]
30			, [ErrorSeverity]
31			, [ErrorState]
32			, [ErrorProcedure]
33			, [ErrorLine]
34			, [ErrorMessage]
35		)
36		VALUES 
37		(
38			  ERROR_NUMBER()
39			, ERROR_SEVERITY()
40			, ERROR_STATE()
41			, ERROR_PROCEDURE()
42			, ERROR_LINE()
43			, ERROR_MESSAGE()
44		)
45	END TRY
46	BEGIN CATCH
47		THROW;
48	END CATCH
49END
50GO
51
52SELECT * FROM [dbo].[ErrorLog]
53----------
54
55/* 2.1. INSERT nove boje i sprecavanje pojave duplikata */
56ALTER PROCEDURE NovaBoja_INSERT
57@nazivBoje nchar (20)
58AS
59BEGIN
60	BEGIN TRY
61		IF NOT EXISTS (SELECT * FROM dbo.Boja WHERE NazivBoje = @nazivBoje)
62		BEGIN
63			INSERT INTO dbo.Boja VALUES(@nazivBoje)	
64			/*DECLARE @ID int = SCOPE_IDENTITY();*/							
65		END							
66	END TRY
67	BEGIN CATCH
68		EXEC dbo.usp_ErrorHandler;
69		THROW;
70	END CATCH
71END
72GO
73-- Pozivanje procedure -- 
74EXEC NovaBoja_INSERT
75@nazivBoje = 'Crvena'
76GO
77------------------------		
78SELECT * FROM dbo.Boja
79-------------------------------------------------------------------------
80
81/* 2.2. UPDATE kontkat telefona i eMail-a vlasnika po ID-u*/
82
83ALTER PROCEDURE UpdateTelefona
84(
85@IDVlasnika int,
86@eMail nvarchar(50),
87@Tel nvarchar(50)
88)
89AS
90BEGIN
91	BEGIN TRY
92		IF EXISTS (SELECT * FROM dbo.Vlasnik WHERE IdVlasnika = @IDVlasnika)
93		BEGIN 
94			UPDATE dbo.Vlasnik SET eMail = @eMail, Telefon = @Tel WHERE IdVlasnika = @IDVlasnika;
95		END
96	END TRY
97	BEGIN CATCH
98		BEGIN
99			PRINT 'Greska u odabiru korisnika!';
100			EXECUTE dbo.usp_ErrorHandler;
101			THROW;
102		END
103	END CATCH
104		
105END
106GO
107
108EXEC UpdateTelefona 50, 'jj@Gmail.com', '0644444444'
109GO
110
111SELECT * FROM dbo.Vlasnik
112-------------------------------------------------------------------------
113
114/* 2.3. DELETE*/
115--Brisanje Vlasnika i auta
116ALTER PROCEDURE ObrisiVlasnika
117@vlasnikID int
118AS
119BEGIN
120	SET NOCOUNT ON;
121	BEGIN TRY
122		IF EXISTS (SELECT * FROM dbo.Vlasnik WHERE IdVlasnika = @vlasnikID)
123		BEGIN
124			BEGIN TRANSACTION
125			DELETE FROM dbo.AutoOprema WHERE IdAuta = @vlasnikID;
126			DELETE FROM dbo.Auto WHERE IdVlasnika = @vlasnikID;
127			DELETE FROM dbo.Vlasnik WHERE IdVlasnika = @vlasnikID;		
128			COMMIT TRANSACTION;
129		END
130	END TRY
131	BEGIN CATCH
132		IF @@TRANCOUNT > 0
133			BEGIN
134				ROLLBACK TRANSACTION;
135				EXEC dbo.usp_ErrorHandler;
136				THROW;
137			END
138	END CATCH
139END
140GO
141SELECT * FROM dbo.Vlasnik
142EXEC ObrisiVlasnika 6