Foreign keys constraints@ mssql 2005

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;