Instead Of Trigger e Scope_identity()

martedì 17 giugno 2008 - 15.34

lolic Profilo | Newbie

Ciao a tutti.
Ho una view con associati degli instead of trigger per le operazioni di insert / delete / update scritti in c#.
Il mio problema è che nella query di inserimento dei dati nell'applicazione la query di "Select Scope_Identity()" posta dopo la query di insert mi ritorna null. Come posso fare per impostare all'interno del trigger il valore della variabile relativa allo scope_identity ?

lbenaglia Profilo | Guru

>Ho una view con associati degli instead of trigger per le operazioni
>di insert / delete / update scritti in c#.
>Il mio problema è che nella query di inserimento dei dati nell'applicazione
>la query di "Select Scope_Identity()" posta dopo la query di
>insert mi ritorna null. Come posso fare per impostare all'interno
>del trigger il valore della variabile relativa allo scope_identity
>?

Ciao Andrea,

puoi postare un esempio semplificato con i comandi DDL della struttura della tabella, della vista e del trigger?

Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org

lolic Profilo | Newbie

ASSOCIAZIONI_AORMANATT e ASSOCIAZIONI_ENTITA sono le due tabelle base per la view e sono in correlazione uno a molti. VIEWSOSTANZAASSOCIAZIONI è la view.

La logica del trigger è la seguente: se nella tabella master ASSOCIAZIONI_AORMANATT non è presente la combinazione IDAREA,IDMANSIONE,IDATTIVITA viene creato un nuovo record. Quindi esegue successivamente un INSERT nella tabella ASSOCIAZIONI_ENTITA utilizzando l'IDASSOCIAZIONE appena creato e l'IDSOSTANZA di input. L'ID valore di ritorno della query SELECT Scope_Identity() eseguita nell'applicazione dovrebbe essere l'ID creato dall'Identity della tabella ASSOCIAZIONI_ENTITA, quindi l'IDASSOCIAZIONEENTITA.



Il codice sorgente non è stato renderizzato qui
perchè non c'è sufficiente spazio.
Clicca qui per visualizzarlo in una nuova finestra


Il codice sorgente non è stato renderizzato qui
perchè non c'è sufficiente spazio.
Clicca qui per visualizzarlo in una nuova finestra


Questa invece è la view

Il codice sorgente non è stato renderizzato qui
perchè non c'è sufficiente spazio.
Clicca qui per visualizzarlo in una nuova finestra


Ecco il trigger scritto in c#:

Il codice sorgente non è stato renderizzato qui
perchè non c'è sufficiente spazio.
Clicca qui per visualizzarlo in una nuova finestra

lbenaglia Profilo | Guru

...
>FROM dbo.ASSOCIAZIONI_AORMANATT INNER JOIN
>dbo.AREE ON dbo.ASSOCIAZIONI_AORMANATT.IDAREA = dbo.AREE.IDAREA
>INNER JOIN
>dbo.ATTIVITA ON dbo.ASSOCIAZIONI_AORMANATT.IDATTIVITA = dbo.ATTIVITA.IDATTIVITA
>INNER JOIN
>dbo.MANSIONI ON dbo.ASSOCIAZIONI_AORMANATT.IDMANSIONE = dbo.MANSIONI.IDMANSIONE
>INNER JOIN
>dbo.ASSOCIAZIONI_ENTITA ON dbo.ASSOCIAZIONI_AORMANATT.IDASSOCIAZIONE
>= dbo.ASSOCIAZIONI_ENTITA.IDASSOCIAZIONE
Mancano i comandi DDL delle tabelle AREE, ATTIVITA e MANSIONI.


>Ecco il trigger scritto in c#:
Perché in C#?!
Non potevi definire un comune trigger in T-SQL?

Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org

lolic Profilo | Newbie

Scusa semplificando la view non ho modificato la clausola di where. I DDL di Aree Mansioni e Attivita non servono, ti ri-posto la view

Il codice sorgente non è stato renderizzato qui
perchè non c'è sufficiente spazio.
Clicca qui per visualizzarlo in una nuova finestra

Ho usato c# causa tempi di consegna visto che il linguaggio lo conosco e poca esperienza con il T-SQL. Sicuramente di certo in T-SQL sarebbe meglio anche parlando di prestazioni. Come al solito a mali estremi, estremi rimedi.

lbenaglia Profilo | Guru

>Scusa semplificando la view non ho modificato la clausola di
>where. I DDL di Aree Mansioni e Attivita non servono, ti ri-posto
>la view
OK, ti allego una soluzione:

Il codice sorgente non è stato renderizzato qui
perchè non c'è sufficiente spazio.
Clicca qui per visualizzarlo in una nuova finestra

>Ho usato c# causa tempi di consegna visto che il linguaggio lo
>conosco e poca esperienza con il T-SQL. Sicuramente di certo
>in T-SQL sarebbe meglio anche parlando di prestazioni. Come al
>solito a mali estremi, estremi rimedi.
Capisco

Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org

lolic Profilo | Newbie

Grazie per il post.
Per vedere se il mio problema è risolto ho provato dopo aver creato le tabelle la view e i trigger ad eseguire la seguente query:

INSERT dbo.VIEWSOSTANZAASSOCIAZIONI(IDAREA, IDMANSIONE, IDATTIVITA, IDSOSTANZA)
VALUES (1, 2, 3, 4); SELECT SCOPE_IDENTITY();

Il problema rimane ovvero la query ritorna NULL e non l'id assegnato nella tabella ASSOCIAZIONI_ENTITA, ovvero IDASSOCIAZIONIENTITA. Essendo il primo record doveva ritornare 1 , invece ritorna NULL.
C'è qualcosa forse che sto sbagliando io? Oppure effettivamente lo scope è differente e quindi SCOPE_IDENTITY() è corretto ritorni NULL. E se questo è vero come posso fare per fargli ritornare il corretto ID?

P.s.: grazie per il trigger id DDL, ora ho anche un po' di cose da studiare.

lbenaglia Profilo | Guru

>Per vedere se il mio problema è risolto ho provato dopo aver
>creato le tabelle la view e i trigger ad eseguire la seguente
>query:
>
>INSERT dbo.VIEWSOSTANZAASSOCIAZIONI(IDAREA, IDMANSIONE, IDATTIVITA,
>IDSOSTANZA)
>VALUES (1, 2, 3, 4); SELECT SCOPE_IDENTITY();
>
>Il problema rimane ovvero la query ritorna NULL e non l'id assegnato
>nella tabella ASSOCIAZIONI_ENTITA, ovvero IDASSOCIAZIONIENTITA.
>Essendo il primo record doveva ritornare 1 , invece ritorna NULL.
>C'è qualcosa forse che sto sbagliando io?
I Books Online recitano: "SCOPE_IDENTITY() returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch".
Nel tuo esempio l'INSERT in ASSOCIAZIONI_ENTITA avviene nel contesto del trigger, quindi in uno "scope" differente rispetto a quello che esegue la SELECT SCOPE_IDENTITY().

>Oppure effettivamente
>lo scope è differente e quindi SCOPE_IDENTITY() è corretto ritorni
>NULL.
Esatto

>E se questo è vero come posso fare per fargli ritornare
>il corretto ID?
Per ottenere l'ultimo valore IDENTITY generato nel trigger (che in questo caso corrisponde proprio a quello di ASSOCIAZIONI_ENTITA) devi ricorrere ad una funzione "insensibile al contesto" come @IDENTITY il cui "scope" è a livello di sessione:

SELECT SCOPE_IDENTITY() AS ID1, @@IDENTITY AS ID2; /* Output: ID1 ID2 ----- ---- NULL 1 (1 row(s) affected) */

>P.s.: grazie per il trigger id DDL, ora ho anche un po' di cose
>da studiare.
E' un trigger DML, i DDL triggers vengono scatenati appunto da operazioni DDL (CREATE, ALTER, DROP,...)

Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org
Partecipa anche tu! Registrati!
Hai bisogno di aiuto ?
Perchè non ti registri subito?

Dopo esserti registrato potrai chiedere
aiuto sul nostro Forum oppure aiutare gli altri

Consulta le Stanze disponibili.

Registrati ora !
Copyright © dotNetHell.it 2002-2023
Running on Windows Server 2008 R2 Standard, SQL Server 2012 & ASP.NET 3.5