>Immagina ora che volessi aggiungere alle 2 constraint FK_tbl_utenti_tbl_comuni,FK_tbl_utenti_tbl_comuni1
>il CASCADING nel ONDELETE e ONUPDATE
I Books Online riportano: "The series of cascading referential actions triggered by a single DELETE or UPDATE must form a tree that contains no circular references. No table can appear more than one time in the list of all cascading referential actions that result from the DELETE or UPDATE. Also, the tree of cascading referential actions must not have more than one path to any specified table".
"Cascading Referential Integrity Constraints"
http://technet.microsoft.com/en-us/library/ms186973.aspx
In alternativa puoi ricorrere ad un trigger di DELETE ed uno di UPDATE sulla tabella padre per simulare le operazioni CASCADE, avendo cura di rimuovere i constraint (dato che scatterebbero prima del trigger, annullandone l'esecuzione), ed un trigger di INSERT, UPDATE sulla tabella figlia per verificare che esista il corrispondente valore nella tabella padre:
USE tempdb;
CREATE TABLE dbo.tbl_comuni(
codice char(4) NOT NULL,
nome varchar(10) NOT NULL,
CONSTRAINT PK_tbl_comuni PRIMARY KEY(codice)
);
CREATE TABLE dbo.tbl_utenti(
[login] varchar(10) NOT NULL,
cognome varchar(10) NOT NULL,
cod_comune_nascita char(4) NOT NULL,
cod_comune_residenza char(4) NOT NULL,
CONSTRAINT PK_tbl_utenti PRIMARY KEY(login)
);
ALTER TABLE dbo.tbl_utenti
ADD CONSTRAINT FK_tbl_utenti_tbl_comuni
FOREIGN KEY(cod_comune_nascita)
REFERENCES dbo.tbl_comuni(codice)
ON DELETE CASCADE
ON UPDATE CASCADE;
ALTER TABLE dbo.tbl_utenti
ADD CONSTRAINT FK_tbl_utenti_tbl_comuni1
FOREIGN KEY(cod_comune_residenza)
REFERENCES dbo.tbl_comuni(codice)
ON DELETE CASCADE
ON UPDATE CASCADE;
GO
/* Output:
Msg 1785, Level 16, State 0, Line 1
Introducing FOREIGN KEY constraint 'FK_tbl_utenti_tbl_comuni1'
on table 'tbl_utenti' may cause cycles or multiple cascade paths.
Specify ON DELETE NO ACTION or ON UPDATE NO ACTION,
or modify other FOREIGN KEY constraints.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
*/
ALTER TABLE dbo.tbl_utenti
DROP CONSTRAINT FK_tbl_utenti_tbl_comuni;
GO
/* Aggiungo una colonna IDENTITY alla tabella dbo.trU_Comuni
** che mi servirà nel trigger di UPDATE per correlare le
** tabelle virtuali INSERTED e DELETED
*/
ALTER TABLE dbo.tbl_comuni
ADD ID int NOT NULL IDENTITY;
GO
/* Definisco il trigger di UPDATE
** sulla tabella dbo.tbl_comuni
*/
CREATE TRIGGER dbo.trU_Comuni
ON dbo.tbl_comuni
FOR UPDATE
AS
IF UPDATE(codice)
BEGIN
UPDATE dbo.tbl_utenti
SET cod_comune_nascita = I.codice
FROM INSERTED AS I
JOIN DELETED AS D
ON I.ID = D.ID
JOIN dbo.tbl_utenti U
ON D.codice = U.cod_comune_nascita;
UPDATE dbo.tbl_utenti
SET cod_comune_residenza = I.codice
FROM INSERTED AS I
JOIN DELETED AS D
ON I.ID = D.ID
JOIN dbo.tbl_utenti U
ON D.codice = U.cod_comune_residenza;
END;
GO
/* Definisco il trigger di DELETE
** sulla tabella dbo.tbl_comuni
*/
CREATE TRIGGER dbo.trD_Comuni
ON dbo.tbl_comuni
FOR DELETE
AS
DELETE dbo.tbl_utenti
FROM dbo.tbl_utenti AS U
JOIN DELETED AS D
ON U.cod_comune_nascita = D.codice;
DELETE dbo.tbl_utenti
FROM dbo.tbl_utenti AS U
JOIN DELETED AS D
ON U.cod_comune_residenza = D.codice;
GO
/* Definisco il trigger di INSERT, UPDATE
** sulla tabella dbo.tbl_utenti
*/
CREATE TRIGGER dbo.trIU_Utenti
ON dbo.tbl_utenti
FOR INSERT, UPDATE
AS
DECLARE @Column sysname;
DECLARE @ErrorSwitch bit;
SET @ErrorSwitch = 0;
IF UPDATE(cod_comune_nascita)
AND NOT EXISTS(
SELECT *
FROM dbo.tbl_comuni AS C
JOIN INSERTED AS I
ON C.codice = I.cod_comune_nascita
)
BEGIN
SET @ErrorSwitch = 1;
SET @Column = N'cod_comune_nascita';
END;
IF UPDATE(cod_comune_residenza)
AND NOT EXISTS(
SELECT *
FROM dbo.tbl_comuni AS C
JOIN INSERTED AS I
ON C.codice = I.cod_comune_residenza
)
BEGIN
SET @ErrorSwitch = 1;
SET @Column = N'cod_comune_residenza';
END;
IF @ErrorSwitch = 1
BEGIN
RAISERROR(
N'Si è cercato di valorizzare la colonna ''%s'' con un comune non valido.
L''operazione verrà annullata.', 10, 1, @Column);
ROLLBACK TRAN;
END;
GO
INSERT dbo.tbl_comuni VALUES('COD1', 'Milano');
INSERT dbo.tbl_comuni VALUES('COD2', 'Roma');
INSERT dbo.tbl_comuni VALUES('COD3', 'Brescia');
INSERT dbo.tbl_utenti VALUES('Login 1', 'Benaglia', 'COD1', 'COD1');
INSERT dbo.tbl_utenti VALUES('Login 2', 'Bianchi', 'COD2', 'COD2');
INSERT dbo.tbl_utenti VALUES('Login 3', 'De Giacomi', 'COD3', 'COD1');
/* Provo ad aggionare la tabella dbo.tbl_utenti
** con un comune non valido
*/
UPDATE dbo.tbl_utenti
SET cod_comune_nascita = 'COD9'
GO
/* Output:
Si è cercato di valorizzare la colonna 'cod_comune_nascita' con un comune non valido.
L'operazione verrà annullata.
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
*/
UPDATE dbo.tbl_utenti
SET cod_comune_residenza = 'COD7'
WHERE cognome = 'Benaglia';
GO
/* Output:
Si è cercato di valorizzare la colonna 'cod_comune_residenza' con un comune non valido.
L'operazione verrà annullata.
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
*/
/* Aggiorno la tabella dbo.tbl_utenti con un comune valido */
UPDATE dbo.tbl_utenti
SET cod_comune_residenza = 'COD2'
WHERE cognome = 'Benaglia';
GO
/* Output:
(1 row(s) affected)
*/
SELECT *
FROM dbo.tbl_utenti;
/* Output:
login cognome cod_comune_nascita cod_comune_residenza
---------- ---------- ------------------ --------------------
Login 1 Benaglia COD1 COD2
Login 2 Bianchi COD2 COD2
Login 3 De Giacomi COD3 COD1
(3 row(s) affected)
*/
/* Aggiorno la tabella dbo.tbl_comuni
** modificando il codice di Roma da 'COD2' a 'COD5'
*/
UPDATE dbo.tbl_comuni
SET codice = 'COD5'
WHERE codice = 'COD2';
/* Output:
(1 row(s) affected)
(2 row(s) affected)
(1 row(s) affected)
*/
SELECT *
FROM dbo.tbl_utenti;
/* Output:
login cognome cod_comune_nascita cod_comune_residenza
---------- ---------- ------------------ --------------------
Login 1 Benaglia COD1 COD5
Login 2 Bianchi COD5 COD5
Login 3 De Giacomi COD3 COD1
(3 row(s) affected)
*/
/* Elimino il comune di Roma */
DELETE dbo.tbl_comuni
WHERE nome = 'Roma';
/* Output:
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
*/
SELECT *
FROM dbo.tbl_utenti;
/* Output:
login cognome cod_comune_nascita cod_comune_residenza
---------- ---------- ------------------ --------------------
Login 3 De Giacomi COD3 COD1
(1 row(s) affected)
*/
DROP TABLE dbo.tbl_utenti, dbo.tbl_comuni;
Se hai ulteriori dubbi fai riferimento ai Books Online.
Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org