none
Best Practice: Stored Procedure per inserire contemporaneamente in 2 tabelle collegate da foreign key? RRS feed

  • Domanda

  • Ciao a tutti,

    qualcuno sa dirmi qual'è la best practice per inserire con una sola stored procedure un record in una tabella, prendere l'id del record appena inserito e procedere con un altro insert in un'altra tabella collegata alla prima tramite foreign key riportando l'id restituito dalla prima insert?

    Nel mio caso si parla di creare un nuovo record nella tabella contatto e se l'insert ha avuto successo allora aggiungere un record nella tabella persona passando alla insert anche l'id del contatto creato. P.s. Tutti i miei ID sono auto-increment.

    Il mio istinto mi suggerisce che è richiesta una transaction e l'utilizzo di una variabile in cui salvo @@IDENTITY della prima insert.

    Mi potreste dare una mano?

    lunedì 13 maggio 2013 09:43

Risposte

  • eh cribbio...

    oramai l'ho scritta e, anche se in ritardo causa telefono squillante, la posto comunque, anche se non "cambia" niente rispetto al discorso di Luca :)

    [quindi, scusa Luca :) ]

    SET NOCOUNT ON;
    USE tempdb;
    GO
    CREATE TABLE dbo.t1 ( 
    	Id int IDENTITY NOT NULL PRIMARY KEY,
    	valore int NOT NULL
    		CHECK (valore >= 2)
    	);
    CREATE TABLE dbo.t2 (
    	Id int IDENTITY NOT NULL PRIMARY KEY,
    	IdT1 int NOT NULL
    		CONSTRAINT fk_t2$is$t1 FOREIGN KEY
    			REFERENCES dbo.t1 (Id),
    	valore int NOT NULL
    		CHECK (valore >= 10)
    	);
    GO
    CREATE PROCEDURE dbo.usp_t1_t2 (
    	@valoreT1 int,
    	@valoreT2 int
    	)
    AS BEGIN
    	
    	DECLARE @id int = NULL;
    	
    	BEGIN TRAN;
    	
    	BEGIN TRY			
    		INSERT INTO dbo.t1 (valore)
    			VALUES (@valoreT1);
    		SELECT @id = SCOPE_IDENTITY();
    
    		IF (@id IS NOT NULL) BEGIN
    			IF (@valoreT2 > 50) BEGIN			
    				DECLARE @msg varchar(150);
    				DECLARE @key varchar(10);
    				SET @key = CONVERT(varchar(20), @id);
    				SET @msg = 'Impossibile inserire riga con Riferimento = [%s] in  [%s].' + CHAR(10) + 'Transazione annullata.';
    				RAISERROR (@msg, 16, 1, @key, 'tabella T2');
    				END;
    
    			INSERT INTO dbo.t2 (IdT1, valore)
    				VALUES (@id, @valoreT2);
    			END;
    		COMMIT;
    	END TRY
    
    	BEGIN CATCH
    		ROLLBACK;
    		-- ritorna l'errore
    		DECLARE @ErrorMSG varchar(2000);
    		SET @ErrorMSG = ERROR_MESSAGE();
    		RAISERROR (@ErrorMSG, 16, 1);
    		RETURN -100;
    	END CATCH
    END
    GO
    EXEC dbo.usp_t1_t2 10, 20;
    GO
    SELECT *
    	FROM dbo.t1 t1
    		LEFT JOIN dbo.t2 t2 ON t2.IdT1 = t1.Id;
    GO
    PRINT 'fallisce t1';
    EXEC dbo.usp_t1_t2 1, 20;
    GO
    SELECT *
    	FROM dbo.t1 t1
    		LEFT JOIN dbo.t2 t2 ON t2.IdT1 = t1.Id;
    GO
    PRINT 'fallisce t2';
    EXEC dbo.usp_t1_t2 20, 5;
    GO
    SELECT *
    	FROM dbo.t1 t1
    		LEFT JOIN dbo.t2 t2 ON t2.IdT1 = t1.Id;
    GO
    PRINT 'fallisce t2 per errore di business non trappato da apposito constraint';
    EXEC dbo.usp_t1_t2 20, 120;
    GO
    SELECT *
    	FROM dbo.t1 t1
    		LEFT JOIN dbo.t2 t2 ON t2.IdT1 = t1.Id;
    GO
    DROP PROCEDURE dbo.usp_t1_t2;
    DROP TABLE dbo.t2, dbo.t1;

    il concetto resta invariato.. se un'azione viene annullata da constraints a tutela e salvaguardia della base dati, anche nel caso di fallimento della seconda operazione di insert, l'intera transazione viene annullata... allo stesso modo, se la logica di business "estende" in un qualche modo (e solitamente accade) i constraint di validazione/protezione, e' sempre possibile far fallire l'intera transazione mantenendo corretto lo sviluppo dei dati senza incorrere in operazioni orfane... nel caso di cui sopra, ad esempio, ho fatto fallire l'operazione con un raiserror, 

    			IF (@valoreT2 > 50) BEGIN			
    				DECLARE @msg varchar(150);
    				DECLARE @key varchar(10);
    				SET @key = CONVERT(varchar(20), @id);
    				SET @msg = 'Impossibile inserire riga con Riferimento = [%s] in  [%s].' + CHAR(10) + 'Transazione annullata.';
    				RAISERROR (@msg, 16, 1, @key, 'tabella T2');
    				END;
    

    ma la medesima cosa puo' essere fatta senza sollevare l'eccezione ma semplicemente effettuando in sua sostituzione un rollback nel flusso del codice... personalmente preferisco solitamente sollevare un'eccezione in quanto si tratta a tutti gli effetti di un "fallimento" nell'operazione DML, a prescindere che questo sia fisico o logico, ma questo e' un altro aspetto :)

    saluti 


    http://www.hotelsole.com/asql/index.php - DbaMgr2k - DbaMgr and further SQL Tools http://www.hotelsole.com/

    • Proposto come risposta _ Luca Gaspari lunedì 13 maggio 2013 15:46
    • Proposta come risposta annullata _ Luca Gaspari lunedì 13 maggio 2013 15:46
    • Contrassegnato come risposta DkSw martedì 21 maggio 2013 13:09
    lunedì 13 maggio 2013 15:32
    Moderatore

Tutte le risposte

  • Ciao DkSw, in SQL Server esiste una tabella che fà al caso tuo in quanto memorizza i valori appena inseriti.

    Puoi richiamare il tuo id appena generato usando la semplice sintassi

    SELECT TuoId FROM Inserted

    Un saluto

    lunedì 13 maggio 2013 09:49
  • Ciao, grazie per l'attenzione.

    Tuttavia la mia domanda era a proposito della best-practice per realizzare qualcosa tipo:

    INSERT INTO Customer(LastName,FirstName,......) VALUES(Value1, Value2, .....)
    SELECT @NewID = @@IDENTITY
    INSERT INTO Employment(CID,Employer,.....) VALUES(@NewID, ValueA,..........)

    Tuttavia in questo caso se fallisce la prima INSERT la stored procedure procede con la seconda.

    Credo la best practice sia qualcosa come una transaction, ma non ne sono sicuro.

    Speravo che qualcuno potesse illuminarmi sul miglior modo per affrontare il problema del doppio INSERT di record parent-child in tabelle collegate da foreign key.

    lunedì 13 maggio 2013 13:16
  • Allora così dovrebbe andarti bene :

    CREATE TABLE #Samples
    (
    	pk_Sample INT IDENTITY(1,1) NOT NULL,
    	Value VARCHAR(100)
    )
    
    CREATE TABLE #Parameters
    (
    	pk_Parameter INT IDENTITY(1,1) NOT NULL,
    	fk_Sample INT NOT NULL,
    	Value VARCHAR(100)
    )
    
    DECLARE @lastKeyInserted INT
    
    BEGIN TRY
    
    	BEGIN TRANSACTION t1
    	
    	INSERT INTO #Samples ( Value ) 
    	SELECT 
    		'A Random Sample Name'
    	
    	SET @lastKeyInserted = (SELECT @@IDENTITY AS [@@IDENTITY])
    	
    	INSERT INTO #Parameters ( fk_Sample, Value )
    	SELECT
    		@lastKeyInserted,
    		'A Random Param Name'
    	
    	INSERT INTO #Parameters ( fk_Sample, Value )
    	SELECT
    		@lastKeyInserted,
    		'A Random Param Name'
    	
    	
    	PRINT 'All is fine, committing transaction...'
    	COMMIT TRANSACTION t1
    
    END TRY
    BEGIN CATCH
    	
    	PRINT 'Rolling back transaction... you''ll be more lucky next time (:'
    	ROLLBACK TRANSACTION t1	
    	
    END CATCH
    

    Un saluto

    lunedì 13 maggio 2013 14:57
  • eh cribbio...

    oramai l'ho scritta e, anche se in ritardo causa telefono squillante, la posto comunque, anche se non "cambia" niente rispetto al discorso di Luca :)

    [quindi, scusa Luca :) ]

    SET NOCOUNT ON;
    USE tempdb;
    GO
    CREATE TABLE dbo.t1 ( 
    	Id int IDENTITY NOT NULL PRIMARY KEY,
    	valore int NOT NULL
    		CHECK (valore >= 2)
    	);
    CREATE TABLE dbo.t2 (
    	Id int IDENTITY NOT NULL PRIMARY KEY,
    	IdT1 int NOT NULL
    		CONSTRAINT fk_t2$is$t1 FOREIGN KEY
    			REFERENCES dbo.t1 (Id),
    	valore int NOT NULL
    		CHECK (valore >= 10)
    	);
    GO
    CREATE PROCEDURE dbo.usp_t1_t2 (
    	@valoreT1 int,
    	@valoreT2 int
    	)
    AS BEGIN
    	
    	DECLARE @id int = NULL;
    	
    	BEGIN TRAN;
    	
    	BEGIN TRY			
    		INSERT INTO dbo.t1 (valore)
    			VALUES (@valoreT1);
    		SELECT @id = SCOPE_IDENTITY();
    
    		IF (@id IS NOT NULL) BEGIN
    			IF (@valoreT2 > 50) BEGIN			
    				DECLARE @msg varchar(150);
    				DECLARE @key varchar(10);
    				SET @key = CONVERT(varchar(20), @id);
    				SET @msg = 'Impossibile inserire riga con Riferimento = [%s] in  [%s].' + CHAR(10) + 'Transazione annullata.';
    				RAISERROR (@msg, 16, 1, @key, 'tabella T2');
    				END;
    
    			INSERT INTO dbo.t2 (IdT1, valore)
    				VALUES (@id, @valoreT2);
    			END;
    		COMMIT;
    	END TRY
    
    	BEGIN CATCH
    		ROLLBACK;
    		-- ritorna l'errore
    		DECLARE @ErrorMSG varchar(2000);
    		SET @ErrorMSG = ERROR_MESSAGE();
    		RAISERROR (@ErrorMSG, 16, 1);
    		RETURN -100;
    	END CATCH
    END
    GO
    EXEC dbo.usp_t1_t2 10, 20;
    GO
    SELECT *
    	FROM dbo.t1 t1
    		LEFT JOIN dbo.t2 t2 ON t2.IdT1 = t1.Id;
    GO
    PRINT 'fallisce t1';
    EXEC dbo.usp_t1_t2 1, 20;
    GO
    SELECT *
    	FROM dbo.t1 t1
    		LEFT JOIN dbo.t2 t2 ON t2.IdT1 = t1.Id;
    GO
    PRINT 'fallisce t2';
    EXEC dbo.usp_t1_t2 20, 5;
    GO
    SELECT *
    	FROM dbo.t1 t1
    		LEFT JOIN dbo.t2 t2 ON t2.IdT1 = t1.Id;
    GO
    PRINT 'fallisce t2 per errore di business non trappato da apposito constraint';
    EXEC dbo.usp_t1_t2 20, 120;
    GO
    SELECT *
    	FROM dbo.t1 t1
    		LEFT JOIN dbo.t2 t2 ON t2.IdT1 = t1.Id;
    GO
    DROP PROCEDURE dbo.usp_t1_t2;
    DROP TABLE dbo.t2, dbo.t1;

    il concetto resta invariato.. se un'azione viene annullata da constraints a tutela e salvaguardia della base dati, anche nel caso di fallimento della seconda operazione di insert, l'intera transazione viene annullata... allo stesso modo, se la logica di business "estende" in un qualche modo (e solitamente accade) i constraint di validazione/protezione, e' sempre possibile far fallire l'intera transazione mantenendo corretto lo sviluppo dei dati senza incorrere in operazioni orfane... nel caso di cui sopra, ad esempio, ho fatto fallire l'operazione con un raiserror, 

    			IF (@valoreT2 > 50) BEGIN			
    				DECLARE @msg varchar(150);
    				DECLARE @key varchar(10);
    				SET @key = CONVERT(varchar(20), @id);
    				SET @msg = 'Impossibile inserire riga con Riferimento = [%s] in  [%s].' + CHAR(10) + 'Transazione annullata.';
    				RAISERROR (@msg, 16, 1, @key, 'tabella T2');
    				END;
    

    ma la medesima cosa puo' essere fatta senza sollevare l'eccezione ma semplicemente effettuando in sua sostituzione un rollback nel flusso del codice... personalmente preferisco solitamente sollevare un'eccezione in quanto si tratta a tutti gli effetti di un "fallimento" nell'operazione DML, a prescindere che questo sia fisico o logico, ma questo e' un altro aspetto :)

    saluti 


    http://www.hotelsole.com/asql/index.php - DbaMgr2k - DbaMgr and further SQL Tools http://www.hotelsole.com/

    • Proposto come risposta _ Luca Gaspari lunedì 13 maggio 2013 15:46
    • Proposta come risposta annullata _ Luca Gaspari lunedì 13 maggio 2013 15:46
    • Contrassegnato come risposta DkSw martedì 21 maggio 2013 13:09
    lunedì 13 maggio 2013 15:32
    Moderatore
  • Vorrei alcuni chiarimenti riguardo le transaction.

    Mentre googlavo la mia situazione e vedevo qualcuno parlare di transaction ho letto approposito a possibili problemi con:

     - Auto Increment Primary Key

     - Timeout del server

    Questa soluzione è considerata best-practice/sicura/affidabile?


    • Modificato DkSw martedì 14 maggio 2013 12:56
    lunedì 13 maggio 2013 15:32
  • salve,

    non mi sovvengono problemi relativi a time-out... e lo stesso relativamente alle colonne con proprieta' identity, se non ovviamente legate al fatto che si produrranno "buchi" nelle sequenze... dopo un fallimento di inserimento, l'ID=2 sara' stato gia' utilizzato ed il prossimo rilasciato dal server sara' il 3, quindi tra 1 e 3 ci sara' un cosidetto buco (gap), ma questo e' "by design" e non e' affatto un problema... e' solo sbagliata la logica di utilizzo della proprieta' identity o di una sequence...

    saluti


    http://www.hotelsole.com/asql/index.php - DbaMgr2k - DbaMgr and further SQL Tools http://www.hotelsole.com/

    lunedì 13 maggio 2013 15:45
    Moderatore
  • eh cribbio...

    oramai l'ho scritta e, anche se in ritardo causa telefono squillante, la posto comunque, anche se non "cambia" niente rispetto al discorso di Luca :)

    [quindi, scusa Luca :) ]

    Volevo quotarti e ho premuto su "suggerisci come risposta".. lol 

    Credo che non ci sia altro modo se non combattere al tramonto per decidere quale sia la risposta migliore :D

    A parte gli scherzi Dk, puoi tenere per buone tutte e due.. sicuramente l'approccio che ti ha fornito Andrea è più esteso mentre il codice postato da me è il minimo che avevi richiesto tu.. entrambi funzionanti, stà a te applicare quella più o meno complessa in base a ciò che vuoi ottenere dalla tua applicazione.

    Sicuramente però, in caso di fallimento dell'operazione ti conviene mettere nella catch, dopo il rollback qualche tipo di codice per segnalazioni. Per task delicati a volte mi piace inserire segnalazioni via mail circa l'errore riscontrato, utile e di sicuro effetto in quanto ti permette di sistemare l'eventuale errore prima che te lo segnali il cliente.

    Un saluto!

    lunedì 13 maggio 2013 15:51
  • Innanzitutto grazie ad entrambi, tutte e 2 le soluzioni sono di grande utilità e vorrei votarle entrambe.

    Il gap degli ID non è un problema, è lo stesso che si avrebbe se qualcuno cancella dei record.

    Per quanto riguarda la tua soluzione Andrea avrei qualche domanda:

    - Come mai la "BEGIN TRAN" sta prima del "BEGIN TRY", ma la "COMMIT" sta prima dell' "END TRY"?

    - Nel RAISEERROR mi sembra tu utilizzi la seguente struttura "RAISERROR ( {msg_str}{ ,severity ,state }[ ,argument [ ,...n ] ] )", quindi severity=16 e state=1 cosa significano?

    - RETURN -100 è un tuo codice di errore o il -100 ha qualche significato particolare?

    - Dentro il CATCH dichiari un nuovo errore, ci metti il contenuto di quello ricevuto e lo raisi di nuovo...non è possibile fare direttamente RAISEERROR(ERROR_MESSAGE(),16,1)?

    - Come mai ci sono ";" dappertutto?

    martedì 14 maggio 2013 10:06
  • salve,

    e scusa il ritardo ma ho un progettino molto triste da fare, visto che anche il nostro comune ha approvato la tassa di soggiorno :(

    BEGIN TRAN solitamente lo metto anche fuori da un check degli errori in quanto non puo' sollevare eccezioni, e comunque va messo dove logicamente risulta necessario... nel caso in esame puo' stare benissimo dove l'ho messo, ma anche prima della prima INSERT...

    al contrario, COMMIT risiede al termine delle operazioni DML dentro il check dell'errore, visto che in caso di eccezione gestibile, sollevata dal codice come anche dal servizio, automaticamente il flusso di esecuzione entrera' nel  NEGIN CATCH, che come prima operazione effettua il ROLLBACK di quanto eventualmente modificato...

    severity e state sono 2 parametri utilizzabili nel throw di eccezioni... la severity 16 e' nel range delle severity gestibili e solitamente si applica ai retrhow applicati dal codice utente... una guida e' disponibile presso http://msdn.microsoft.com/en-us/library/ms164086.aspx ... similarmente per gli states utilizzabili, il range va da 0 a 255 e sarebbe carino utilizzare anche questi in maniera consistente per meglio identificare la logica dell'errore sollevato... e purtroppo non c'e' una guida o un riferimento in questo senso... 

    utilizzo RAISEERROR perche' consente la sostituzione di token, come presente nel codice in esame, ed anche perche' a mio parere e' superiore in funzionalita' rispetto il TRHOW...

    RETURN -100; e' un codice arbitrario che ho messo io, anche questo da usare in maniera consistente.. :)

    il ";" e' il terminatore di comando, definito come standard a livello ANSI... quindi sarebbe buona norma utilizzarlo sempre per aderire anche alle best practices di pretty printing del codice... ovviamente ognuno puo' fare come meglio crede, ma e' sicuramente bene adottare uno stile di scrittura/codifica e mantenerlo costante... questo vale per le indentazioni del codice, la "capitalizzazione" (maiuscolo/minuscolo), posto di dichiarazioni delle variabili, commenti e quant'altro... ovviamente vale anche per la sintassi, ad esempio deprecando la vecchia sintassi di join ANSI 89 in favore della meglio leggibile ANSI 92... in questo senso specifico, aderire allo standard ANSI 92 e' per SQL Server 2012 un obbligo per gli OUTER JOIN non piu' supportati nella vecchia sintassi ANSI 89... quindi e' bene aderire allo standard... tornando al ";" come terminatore di comando, ancora questo non e' "necessario" a meno che il codice non includa una common table expression... in tal caso, il comando precedente alla definizione della CTE "deve" essere correttamente terminato con ";"... per la maggior parte, al momento, e' ancora una questione di stile, ma visto che si inizia a richiederlo (a livello sintattico), a mio parere e' bene cominciare ad usarlo come da specifica... avendo cominciato ad usarlo con le CTE di SQL Server 2005, oramai mi e' di fatto diventato "automatico" :) ...

    come vedi, ad esempio, Luca ed io abbiamo uno stile in alcuni casi "simile"... entrambi scriviamo le keywords in maiuscolo, ma ad esempio io scrivo i "tipi di dato" (int, varchar, ...) in minuscolo... e c'era anche il motivo, dipendente dal fatto che con magari il database master in collation binaria o case sensitive potesse fallire il recupero del metadato relativo al tipo di dato in quanto nel database di sistema questo e' scritto in minuscolo... non ho piu' provato dai tempi in cui il fatto fu riscontrato, e sono passati tanti anni, ma il mio stile continua a mantenerlo...

    ad ogni modo ed a prescindere, definire uno stile e perseguirlo con fermezza e' chiaramente d'obbligo

    saluti


    http://www.hotelsole.com/asql/index.php - DbaMgr2k - DbaMgr and further SQL Tools http://www.hotelsole.com/


    venerdì 17 maggio 2013 01:17
    Moderatore
  • Grazie infinite, nel mio caso anche la risposta di Luca era più che sufficiente, ma colgo l'occasione per migliorare la mia conoscenza in ambito SQL e per iniziare a scrivere codice più completo.

    Buon Lavoro!

    martedì 21 maggio 2013 13:36