none
Trigger per gestire Insert

    Domanda

  • Ciao

    ho una tabella nel db in cui diversi utenti, anche (forse) contemporaneamente, potrebbero inserire record.

    Questa tabella contiene una colonna il cui valore deve essere calcolato in base al valore di un'altra colonna dello stesso record (deve essere un progressivo).

    Ho pensato ad un trigger, ma, sinceramente, non so come crearlo.

    Grazie

    domenica 4 marzo 2012 09:15

Risposte

  • Sinceramente, salvo casi molto particolari, non vedo un ordinamento come un campo persistente.

    Nei casi in cui serva un semplice ordine di inserimento "ignorante" il campo identity va più che bene (con l'uscita di SQL Server 2012 gli oggetti SEQUENCE sono un'ottima alternativa), altrimenti un campo Data/Ora automatico fornisce un'informazione un po' più consistente.

    Combinando in fase di ORDER BY questi campi con altri attributi del record otterresti poi quello che serve a te, cioè un ordinamento principale ed uno secondario.

    Se invece ti serivisse un ordinamento progressivo senza "buchi" dovresti utilizzare, in fase di estrazione, una tecnica tipo quella indicata da Andrea basata su windowing functions come il ROW_NUMBER partizionato. La base di calcolo del ROW_NUMBER può essere "casuale", cioè di volta in volta lo calcoli secondo l'ordinamento del campo stesso (in questo caso hai un progressivo che ha significato solo in quella particolare estrazione, in quanto potrebbe cambiare in quella successiva), oppure lo basi sul campo identity/datetime di cui sopra (in questo caso il progressivo che ottieni è basato sull'ordine di inserimento ed è predittivo).

    Più che altro scrivere il progressivo a priori ti obbliga a dover gestire anche eventuali update oppure insert che non avvengano in coda ad altri record già ordinati. Magari non è il tuo caso, ma è un fattore importante da tenere in considerazione.

    Tutte queste considerazioni portano alla conclusione che esistono metodi comunque efficaci per produrre il risultato che desideri senza appesantire la fase di inserimento, in particolare con un trigger che, sebbene sia più leggero di una query linq2sql, rappresenta una componente in più da manutenere e tenere in considerazione.

    Ciao!


    Francesco Milano // .NET & SQL Server Consultant // blog // twitter


    lunedì 5 marzo 2012 15:46
  • salve,

    >Vero, ma inserendola in una transazione dovrei evitare il danno.

    che c'entra? ti piace "tirare all'inzecca"? :) il "sistema" che hai impiantato e' fallace per sua natura, e lo sai in partenza... e poi, perche' "tentare" un'operazione lato client (che in caso di errore ti necessita di reiterare magari piu' e piu' volte l'operazione) quando lo strumento deputato all'azione e' gia' disponibile lato server (visto che lo puoi fare in un trigger o in una stored procedure)?

    in questo forum [ma solo in questo :)] potrei dire che "stai ragionando lato client tipicamente da DEV :)"

    ed a questa affermazione mi aggrappo rispondendoti circa

    >Forse sono OT ma ad es. usi linq2sql (che forse eviti come la peste), EF o nessuno dei due?

    nessuno dei 2... non mi appoggio assolutamente su ORM o simili... il codice SQL eseguito, nel bene e nel male, e' tutto mio, e sta (solitamente) tutto dentro stored procedures.. :) ... e con questo non voglio assolutamente aprire una guerra di religione, ma ho i miei "pregiudizi" ben saldi :)

    saluti


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

    • Contrassegnato come risposta Cracken66 giovedì 8 marzo 2012 09:16
    mercoledì 7 marzo 2012 23:30

Tutte le risposte

  • Ti posto un paio di link che possono esserti utili:

    BOL: http://msdn.microsoft.com/it-it/library/ms189799.aspx

    CodeProject: http://www.codeproject.com/Articles/25600/Triggers-Sql-Server

    Prima di pensare però subito ad un trigger, pensi di non riuscire a creare una colonna calcolata, dato che dici che questo valore è generato in base ad un valore del record? Che tipo di relazione c'è tra il campo in questione e il progressivo che devi creare?

    Ciao!


    Francesco Milano // .NET & SQL Server Consultant // blog // twitter

    domenica 4 marzo 2012 12:22
  • Grazie per i links.

    Il campo di controllo conterrà un char che utilizzo come termine di filtro (e per assemblare un codice) per il calcolo del progressivo.

    Ti faccio un esempio:

    CampoA    CampoB

    A               1

    A               2

    A               3

    B               1

    B               2

    B               3

    e via dicendo.

    Sinceramente non credo che una colonna calcolata faccia al caso mio.

    Per tornare al trigger il mio dubbio era che, facendo riferimento alla tabella inserted avrei recuperato il record appena inserito?

    E se nello stesso istante vengo eseguiti più comandi insert?

    Ciao



    • Modificato Cracken66 domenica 4 marzo 2012 15:54
    domenica 4 marzo 2012 15:53
  • salve,

    ancora non hai indicato come devi generare il valore, oltre che le condizioni che lo influenzano... 

    ma l'indicazione di Francesco circa il campo calcolato, nel tuo scenario CampoA e CampoB mi pare resti comunque viabile...

    saluti


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

    domenica 4 marzo 2012 16:22
  • Ho la mia tabella con i due campi:

    select max(campoB)+1 from tabella where CampoA='B'.

    Usando il campo calcolato, come dovrei impostare il tutto?

    Ciao

    domenica 4 marzo 2012 17:33
  • salve,

    personalmente non mi piace gestire queste cose nei trigger... sempre personalmente, non acconsento mai (o quasi mai) ad eseguire operazioni DML dirette sulle tabelle e gestisco solitamente il tutto in stored procedure apposite... li' non avresti alcun problema, in quanto ti calcoleresti il tuo MAX+1 riga per riga senza battere ciglio..

    se proprio vuoi acconsentire al DML esplicito autorizzato sulle tabelle allora potresti vedere di un trigger simile a 

    SET NOCOUNT ON;
    USE tempdb;
    GO
    CREATE TABLE dbo.t (
    	colA char(1) NOT NULL,
    	colB int NOT NULL,
    	Data varchar(10) NOT NULL
    	);
    GO
    CREATE FUNCTION dbo.udf_GetMaxCol (
    	@colA char(1)
    	) RETURNS int
    AS BEGIN
    	
    	RETURN (SELECT ISNULL(MAX(t.ColB), 0)
    				FROM dbo.t
    				WHERE t.ColA = @colA);
    END;
    GO
    CREATE TRIGGER tr_t_I__ ON dbo.t
    	INSTEAD OF INSERT
    AS BEGIN
    	IF (@@ROWCOUNT = 0) RETURN;
    
    	WITH cteColA AS (
    		SELECT DISTINCT i.colA
    			FROM inserted i
    		),
    	cteAfull AS (
    		SELECT a.colA, dbo.udf_GetMaxCol(a.colA) AS [bLastValue]
    			FROM cteColA a
    		),
    	cte AS (
    		SELECT i.colA, i.colB, i.Data, ROW_NUMBER() OVER (PARTITION BY i.ColA ORDER BY i.colA) AS [r]
    			FROM inserted i				
    		)
    	INSERT INTO dbo.t (colA, colB, Data)
    		SELECT i.colA, a.bLastValue + i.r, i.Data
    			FROM cte i
    				JOIN cteAfull a ON a.colA = i.colA
    			ORDER BY i.colA, i.r
    		
    END;
    GO
    INSERT INTO dbo.t 
    	VALUES ( 'A', 0, 'a1'), 
    		('B', 0, 'b1'), ('B', 0, 'b2'), 
    		('C', 0, 'c1'), ('C', 0, 'c2'), ('C', 0, 'c3');
    GO
    SELECT *
    	FROM dbo.t;
    GO
    DROP FUNCTION dbo.udf_GetMaxCol ;
    DROP TABLE dbo.t;
    --<------------
    colA colB        Data
    ---- ----------- ----------
    A    1           a1
    B    1           b1
    B    2           b2
    C    1           c1
    C    2           c2
    C    3           c3
    

    nell'instead of trigger di insert, prima ho ottenuto "l'elenco" delle tipologie di riga (char(1)), quindi ho ottenuto il MAX di ogni tipologia.. successivamente ho partizionato le righe di inserted aggiungendo al resultset il ROW_NUMBER di partizione, quindi aggiungendo al vecchio MAX presente in tabella il ROW_NUMBER posso avere il progressivo corrente di riga, il tutto senza ciclare con un cursore...

    se invece vuoi solo il campo calcolato, puoi sempre basarti su ROW_NUMBER OVER PARTITION, ma il valore di colB non sara' serializzato ma sempre dinamico in base alla selezione che vorrai fare, quindi puo' risultare un po' piu' impegnativo in alcune circostanze di estrazione magari di un estratto delle righe e non di tutto il pacchetto, ma e' solitamente il sistema che si usa per esplosioni ordinali di elenchi, similramente a

    SET NOCOUNT ON;
    USE tempdb;
    GO
    DECLARE @t TABLE (colA char(1), Data varchar(10), otherData int, colDate date);
    INSERT INTO @t
    	VALUES ('A', 'a1', 2, '20120101'),
    		('B', 'a2', 2, '20120201'), ('B', 'a1', 2, '20120101'),
    		('C', 'c3', 3, '20120201'), ('C', 'c2', 2, '20120201'), ('C', 'c1', 1, '20120101');
    
    SELECT t.colA, 
    	ROW_NUMBER() OVER (PARTITION BY t.colA ORDER BY t.ColA, t.colDate, t.otherData) AS [r],
    	t.Data, t.otherData, t.colDate	
    	FROM @t t
    	ORDER BY t.colA;
    	--<---------
    	colA r                    Data       otherData   colDate
    ---- -------------------- ---------- ----------- ----------
    A    1                    a1         2           2012-01-01
    B    1                    a1         2           2012-01-01
    B    2                    a2         2           2012-02-01
    C    1                    c1         1           2012-01-01
    C    2                    c2         2           2012-02-01
    C    3                    c3         3           2012-02-01
    

    saluti


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

    lunedì 5 marzo 2012 00:10
  • Io attualmente utilizzo una funzione che richiamo da .net in una transazione tramite linq2sql.

    Pensavo che il trigger potesse essere più efficiente.

    Perchè "aborri" l'uso dei trigger per casi come questi?

    Ciao e grazie per l'aiuto

    lunedì 5 marzo 2012 08:33
  • Sinceramente, salvo casi molto particolari, non vedo un ordinamento come un campo persistente.

    Nei casi in cui serva un semplice ordine di inserimento "ignorante" il campo identity va più che bene (con l'uscita di SQL Server 2012 gli oggetti SEQUENCE sono un'ottima alternativa), altrimenti un campo Data/Ora automatico fornisce un'informazione un po' più consistente.

    Combinando in fase di ORDER BY questi campi con altri attributi del record otterresti poi quello che serve a te, cioè un ordinamento principale ed uno secondario.

    Se invece ti serivisse un ordinamento progressivo senza "buchi" dovresti utilizzare, in fase di estrazione, una tecnica tipo quella indicata da Andrea basata su windowing functions come il ROW_NUMBER partizionato. La base di calcolo del ROW_NUMBER può essere "casuale", cioè di volta in volta lo calcoli secondo l'ordinamento del campo stesso (in questo caso hai un progressivo che ha significato solo in quella particolare estrazione, in quanto potrebbe cambiare in quella successiva), oppure lo basi sul campo identity/datetime di cui sopra (in questo caso il progressivo che ottieni è basato sull'ordine di inserimento ed è predittivo).

    Più che altro scrivere il progressivo a priori ti obbliga a dover gestire anche eventuali update oppure insert che non avvengano in coda ad altri record già ordinati. Magari non è il tuo caso, ma è un fattore importante da tenere in considerazione.

    Tutte queste considerazioni portano alla conclusione che esistono metodi comunque efficaci per produrre il risultato che desideri senza appesantire la fase di inserimento, in particolare con un trigger che, sebbene sia più leggero di una query linq2sql, rappresenta una componente in più da manutenere e tenere in considerazione.

    Ciao!


    Francesco Milano // .NET & SQL Server Consultant // blog // twitter


    lunedì 5 marzo 2012 15:46
  • Innazitutto grazie per la spiegazione e per avermi aperto orizzonti che non avevo mai considerato.

    Tuttavia il valore che attualmente imposto in fase di inserimento sarà parte di un codice identificati che verrà richiamato abbastanza spesso e, sinceramente, dover ad ogni chiamata ricalcolarlo (ho pensato), avrebbe potuto appesantire la procedura.

    Inoltre, nelle mie elucubrazioni, ho pensato che si sarebbe anche potuto verificare che nello stesso istante due utenti inserissero un nuovo record.

    Il campo identity non lo uso perchè se inserisco un record che è l'ultimo attualmente presente e poi lo cancello, perdo un valore per l'inserimento successivo e quindi, almeno in questo caso, non mi sembra l'ideale.

    Grazie

    lunedì 5 marzo 2012 18:17
  • salve,

    >Io attualmente utilizzo una funzione che richiamo da .net in una transazione tramite linq2sql.

    non e' colpa mia :)

    inoltre e' pericoloso in quanto ogni postazione client procede per proprio conto a questa generazione pseudo casuale..

    >Perchè "aborri" l'uso dei trigger per casi come questi?

    di nuovo, perche' "non mi servono" :) nei miei database non c'e' mai (ok, quasi mai) accesso diretto alle base table... gli inserimenti passano attraverso stored procedures che contengono tutta la logica di business o di protezione del dato necessaria ad ottenere il risultato desiderato :)

    ottengo cosi' svariati benefici e limito i punti di accesso ai dati..

    saluti


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

    martedì 6 marzo 2012 00:25
  • inoltre e' pericoloso in quanto ogni postazione client procede per proprio conto a questa generazione pseudo casuale..

    Vero, ma inserendola in una transazione dovrei evitare il danno.

    Comunque il tuo modo di operare mi fa riflettere su come attualmente intervengo nelle varie operazioni.

    Forse sono OT ma ad es. usi linq2sql (che forse eviti come la peste), EF o nessuno dei due?

    Ciao e grazie

    martedì 6 marzo 2012 08:26
  • salve,

    >Vero, ma inserendola in una transazione dovrei evitare il danno.

    che c'entra? ti piace "tirare all'inzecca"? :) il "sistema" che hai impiantato e' fallace per sua natura, e lo sai in partenza... e poi, perche' "tentare" un'operazione lato client (che in caso di errore ti necessita di reiterare magari piu' e piu' volte l'operazione) quando lo strumento deputato all'azione e' gia' disponibile lato server (visto che lo puoi fare in un trigger o in una stored procedure)?

    in questo forum [ma solo in questo :)] potrei dire che "stai ragionando lato client tipicamente da DEV :)"

    ed a questa affermazione mi aggrappo rispondendoti circa

    >Forse sono OT ma ad es. usi linq2sql (che forse eviti come la peste), EF o nessuno dei due?

    nessuno dei 2... non mi appoggio assolutamente su ORM o simili... il codice SQL eseguito, nel bene e nel male, e' tutto mio, e sta (solitamente) tutto dentro stored procedures.. :) ... e con questo non voglio assolutamente aprire una guerra di religione, ma ho i miei "pregiudizi" ben saldi :)

    saluti


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

    • Contrassegnato come risposta Cracken66 giovedì 8 marzo 2012 09:16
    mercoledì 7 marzo 2012 23:30
  • Grazie ad entrambi per avermi spinto ad osservare questo aspetto da una prospettiva che non avevo considerato.

    Ciao

    giovedì 8 marzo 2012 09:19