I forum Ricerca FAQ
Fai una domanda
Ultimo messaggio 1 2
SqlBep
SqlBep
il 6 febbraio 2014 alle 18:48
11 messaggi dal 06 febbraio 2014
Buon giorno e grazie per l'accoglienza nel forum,
Sto mettendo le mani in un codice che non ho scritto io per cui anziché modificarlo cerco di lavorare esternamente (dove possibile) implementando dei trigger.
Il database gira su un'istanza SQL server 2008 R2.
C'è una tabella che contiene un centinaio di colonne (l'ho già trovata così).
Debbo inibire la modifica del contenuto di quasi tutte eccetto due o tre.
Con un trigger 'ON UPDATE' posso verificare se il contenuto dei campi consentiti è variato o meno interrogando le pseudotabelle 'Inserted' e 'Deleted'; in pratica verifico se si popola un select dal join delle due pseudotabelle con la clausola Where che verifica se il campo in questione è diverso tra una e l'altra.
Per verificare la modifica dei tre o quattro campi consentiti mi arrangio con qualche Where(....) or (...).
Ma per verificare tutti gli altri mi pare un po' troppo incasinato.
Qualcuno mi potrebbe suggerire una soluzione meglio performante che mi consenta di interrogare all'interno del trigger (o con una stored procedure, o una funzione chiamata) le due tabelle (Inserted e deleted) per tutti gli altri campi?
Spero di essermi ben spiegato
Grazie per l'attenzione
Beppe
RispondiQuoting
SqlBep
SqlBep
il 6 febbraio 2014 alle 21:52
11 messaggi dal 06 febbraio 2014
Risolto, caso mai a qualcuno dovesse interessare:
creo una variabile tipo tabella (ne ignoravo l'esistenza)
la popolo interrogando l'Information_schema (prima mi ero fossilizzato su sp_columns)
dopodiché la ciclo con un cursore.
Così:
declare @Tb table(NomeCol nvarchar(120));
insert into @Tb
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Nomedellatabella';
declare @ClName nvarchar(300);
declare Cur cursor
for select * from @Tb;
open Cur
fetch next from Cur into @Clname;
while (@@FETCH_STATUS <> -1)
begin
if @ClName =
print @clname
fetch next from cur into @clname
end

Va da sé che anziché print ci metto la verifica comparativa
See You
RispondiQuoting
SqlBep
SqlBep
il 6 febbraio 2014 alle 23:35
11 messaggi dal 06 febbraio 2014
.....e va da sé che qui ci si pianta, perché una volta arrivati a chiedere al sistema di controllare nella tabella x il campo (colonna) il cui nome sta in una variabile...tutto il gioco cade.
Qualcuno ha qualche idea
Grazie
Beppe
RispondiQuoting
Andrea Montanari
Andrea Montanari
il 7 febbraio 2014 alle 17:33
1.976 messaggi dal 27 luglio 2005
Contributi
salve,
si possono utilizzare le funzioni UPDATE() http://technet.microsoft.com/it-it/library/ms187326.aspx e COLUMNS_UPDATE() http://technet.microsoft.com/it-it/library/ms186329.aspx

gestire COLUMNS_UPDATE() puo' essere un po' disgustoso in quanto e' costituito da un bitmask, e personalmente, anche se piu' prolisso, penso di preferire UPDATE()...

SET NOCOUNT ON;
USE tempdb;
GO
CREATE TABLE dbo.t (
  Id int NOT NULL,  -- non modificabile
  col1 int NOT NULL,
  col2 int NOT NULL  -- non modificabile
  );
GO
CREATE TRIGGER tr_t_$_U_ ON dbo.t
  AFTER UPDATE
AS BEGIN
  IF @@ROWCOUNT = 0 RETURN;

  IF ( UPDATE (Id) OR UPDATE (Col2) )  BEGIN
    RAISERROR ('Impossibile modificare colonne di backward compatibility Id, Col2', 16, 10);
    ROLLBACK;
    END;
END;
GO
INSERT INTO dbo.t
  VALUES ( 1, 1, 1 );
GO
UPDATE dbo.t
  SET col1 = 2
  WHERE Id = 1;
GO
SELECT *
  FROM dbo.t
GO
UPDATE dbo.t
  SET col1 = 3
  , col2 = 2
  WHERE Id = 1;
GO
SELECT *
  FROM dbo.t

DROP TABLE dbo.t;
--<---------
Id          col1        col2
----------- ----------- -----------
1           2           1

Msg 50000, Level 16, State 10, Procedure tr_t_$_U_, Line 7
Impossibile modificare colonne di backward compatibility Id, Col2
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
Id          col1        col2
----------- ----------- -----------
1           2           1
Andrea Montanari
http://www.hotelsole.com - http://www.hotelsole.com/asql/index.php
RispondiQuoting
SqlBep
SqlBep
il 7 febbraio 2014 alle 17:54
11 messaggi dal 06 febbraio 2014
Grazie Andrea,
il codice che ha fatto l'update in realtà ha fatto l'update di tutte le colonne (<< Update Tabella set Colonna1 = @variabile, colonna2 =@variabile..... Ultima colonna= @variabile>>).
Così ricorrendo ad Update o al più macchinoso Columns_Update troverò che tutte le colonne sono state aggiornate :-(
Cercherei di eseguire una comparazione sulle tabelle Inserted e Deleted, ma la scansione ricorsiva con una variabile che contiene il nome di colonna da comparare non può funzionare (<< select @Nomecolonna from deleted >> e se provo a ricorrere ad un (deprecabile) "EXEC" non riesco a passargli le pseudotabelle Inserted e Deleted.
Mi trovo ad un punto cieco.
RispondiQuoting
SqlBep
SqlBep
il 7 febbraio 2014 alle 18:03
11 messaggi dal 06 febbraio 2014
Dimenticavo, anche se magari andiamo un pelino OT:
Nel codice del programma c'è un sistema d'intercettazione d'errore che però non gestisce molto bene i messaggi, dunque sto meditando di non sollevare alcuna eccezione. In tal caso, esiste il modo per rendere il ROLLBACK TRANS "silenzioso"? Possiamo anche tollerare che all'utente non arrivino messaggi di errore.
In alternativa pensavo di non fare ROLLBACK, ma di ricopiare i dati da Deleted nella tabella originale facendo un update da dentro il trigger stesso (non essendo ricorsivo..).
Sto cercando un metodo per lanciare l'Update senza nominare tutte le colonne e non posso lanciare un "Delete from tabella where ecc" perché il trigger di cancellazione me lo intercetterebbe.
Magari la soluzione c'è ed è lampante, ma credo di aver fuso qualche valvoletta qua e là e non riesco più a vedere nemmeno le cose più ovvie :-((
RispondiQuoting
Andrea Montanari
Andrea Montanari
il 7 febbraio 2014 alle 19:04
1.976 messaggi dal 27 luglio 2005
Contributi
salve,
... non ho capito niente, ma probabilmente non e' colpa tua :)

vediamo... intendi dire che l'aggiornamento avviene comunque per tutte le colonne (anche quelle da non aggiornare) E tu vuoi invece filtrare le colonne NON aggiornabili, rendendole statiche?
se e' cosi', bisognerebbe provare un trigger ISTEAD OF invece che AFTER UPDATE, e prendere solo le colonne a te interessanti... ora purtroppo Cinzia chiama per cena (e non voglio rischiare la vita), quindi non riesco a provare...
nel caso che tu domani dichiari di non esserci riuscito provo a darci un'occhiata :)
salutoni INSTEAD OF
Andrea Montanari
http://www.hotelsole.com - http://www.hotelsole.com/asql/index.php
RispondiQuoting
SqlBep
SqlBep
il 7 febbraio 2014 alle 21:16
11 messaggi dal 06 febbraio 2014
Provo a spiegarmi meglio.
L'applicazione che gestisce i dati è scritta da un altro, l'intento è quello di non mettere le mani in quel codice.
La tabella in questione ha la bellezza di 90 campi(non è un mio progetto e non val la pena discutere le scelte altrui).
In questa tabella esistono un certo numero di elementi (righe) i cui valori possono essere modificati a piacere dall'utente.
I dati di un'altra serie invece non debbono poter essere modificati, o meglio non tutte le colonne debbono poter essere modificate; Per esempio le note o il flag per renderli visibili debbono poter essere modificati, mentre invece i dati che attengono ad altre informazioni debbono mantenersi invariati.
A questo punto il trigger verifica che:
1) il record modificato appartenga al gruppo di quelli da non toccare
2) se ne fa parte controlla che la colonna modificata sia tra quelle modificabili (se i dati in deleted sono differenti da quelli in inserted)
3) DOVREBBE controllare se sono stati modificati i dati delle altre colonne (quelle intoccabili)
Se si sono variati solo i dati modificabili non accade nulla, se viceversa le modifiche coinvolgono anche (o esclusivamente) le altre colonne si annulla la modifica.
Ora la verifica al punto due la si fa senza problemi perché le colonne modificabili sono poche e me la cavo con qualche "OR" nella clausola "Where".
Di seguito un paio di esempi:

Questo per verificare la condizione in 1)

         if (exists(select 1 from inserted where ALI_TABELLE='§-2#')) or (exists(select 1 from inserted where ALI_TABELLE='§-1#'))


Questo per la condizione in 2)

      
    if exists(select i.ali_codice from inserted as i join deleted as d
            on (d.ALI_CODICE=i.ALI_CODICE)
            where (d.ALI_NOTE<> i.ALI_NOTE)or (d.ALI_NOTE1<>i.ALI_NOTE1)or(d.FL_ATTIVO<>i.FL_ATTIVO)
            or(d.ALI_RICETTA<>i.ALI_RICETTA)or(d.ALI_PICCOLA<>i.ALI_PICCOLA)or(d.ALI_GRANDE<>i.ALI_GRANDE)
            or(d.ALI_MEDIA<>i.ALI_MEDIA))


Ma per la verifica 3) son dolori, impostare una novantina di "OR" mi pare di difficile implementazione e così pure scrivere per novanta e passa volte lo stesso codice dove cambio solo il nome del campo da mettere in comparazione.
L'applicazione che agisce su questi dati esegue un update dando un comando su tutti i campi per cui la funzione UPDATE nel trigger mi dà TRUE per tutte le colonne.
Diciamo che per il momento mi debbo rassegnare perché nemmeno nei testi sacri ho trovato traccia di una soluzione perseguibile.
Ho provato a creare una tabella che contiene i nomi di tutte le colonne della tabella inserted per scandirla con un cursore, ma quando ho il nome del campo in una variabile non lo posso usare per comporre il comando di verifica (non si accettano variabili per definire il nome della colonna da interrogare), né posso utilizzare la chiamata ad un EXEC dandogli in pasto la stringa di comando composta con le variabili, perché le tabelle da verificare sono le due pseudotabelle Inserted e Deleted che hanno visibilità limitata all'ambito del trigger e non riesco a passarle come parametri.
Insomma per ora rimango a sguazzare nella palta e mi debbo accontentare di un mezzo risultato.
Grazie per l'attenzione e... occhio all'armonia domestica veh!
Beppe
RispondiQuoting
1 2

Torna al forum | Feed RSS

ASPItalia.com non è responsabile per il contenuto dei messaggi presenti su questo servizio, non avendo nessun controllo sui messaggi postati nei propri forum, che rappresentano l'espressione del pensiero degli autori.