USE tempdb;
GO

CREATE TABLE dbo.ASSOCIAZIONI_AORMANATT(
  IDASSOCIAZIONE int IDENTITY(1,1) NOT NULL,
  IDAREA int NOT NULL,
  IDMANSIONE int NOT NULL,
  IDATTIVITA int NOT NULL,
  USERID varchar(50) NULL CONSTRAINT DF_ASSOCIAZIONI_AORMANATT_USERID  DEFAULT ('master'),
  [TIMESTAMP] datetime NOT NULL CONSTRAINT DF_ASSOCIAZIONI_AORMANATT_TIMESTAMP  DEFAULT (getdate()),
  RECORDELIMINATO bit NOT NULL CONSTRAINT DF_ASSOCIAZIONI_AORMANATT_RECORDELIMINATO  DEFAULT ((0)),
  LASTUPDATE datetime NOT NULL CONSTRAINT DF_ASSOCIAZIONI_AORMANATT_LASTUPDATE  DEFAULT (getdate()),
  CONSTRAINT PK_ASSOCIAZIONI_AORMANATT PRIMARY KEY (IDASSOCIAZIONE)
);
GO

CREATE TABLE dbo.ASSOCIAZIONI_ENTITA(
  IDASSOCIAZIONEENTITA int IDENTITY(1,1) NOT NULL,
  IDASSOCIAZIONE int NOT NULL,
  IDENTITA int NOT NULL,
  USERID varchar(50) NULL CONSTRAINT DF_ASSOCIAZIONI_ENTITA_USERID  DEFAULT ('master'),
  [TIMESTAMP] datetime NOT NULL CONSTRAINT DF_ASSOCIAZIONI_ENTITA_TIMESTAMP  DEFAULT (getdate()),
  RECORDELIMINATO bit NOT NULL CONSTRAINT DF_ASSOCIAZIONI_ENTITA_RECORDELIMINATO  DEFAULT ((0)),
  CONSTRAINT PK_ASSOCIAZIONI_ENTITA PRIMARY KEY (IDASSOCIAZIONE, IDENTITA),
  CONSTRAINT FK_ASSOCIAZIONI_ENTITA_ASSOCIAZIONI_AORMANATT FOREIGN KEY(IDASSOCIAZIONE)
  REFERENCES dbo.ASSOCIAZIONI_AORMANATT (IDASSOCIAZIONE)
);
GO

CREATE VIEW dbo.VIEWSOSTANZAASSOCIAZIONI
AS
SELECT
      A.IDAREA
    , A.IDMANSIONE
    , A.IDATTIVITA
    , A.IDASSOCIAZIONE
    , E.IDENTITA AS IDSOSTANZA
    , E.RECORDELIMINATO
    , E.USERID
    , E.TIMESTAMP
    , E.IDASSOCIAZIONEENTITA

FROM dbo.ASSOCIAZIONI_AORMANATT AS A
JOIN dbo.ASSOCIAZIONI_ENTITA AS E
ON A.IDASSOCIAZIONE = E.IDASSOCIAZIONE
GO

CREATE TRIGGER dbo.tr_ViewSostanzaAssociazioni
ON dbo.VIEWSOSTANZAASSOCIAZIONI
INSTEAD OF INSERT
AS
DECLARE 
      @IDAREA int
    , @IDMANSIONE int
    , @IDATTIVITA int
    , @IDASSOCIAZIONE int
    , @IDSOSTANZA int
    , @RECORDELIMINATO bit
    , @USERID varchar(50)
    , @TIMESTAMP datetime 
    , @IDASSOCIAZIONEENTITA int

DECLARE curInserted INSENSITIVE CURSOR 
FOR SELECT * FROM INSERTED
FOR READ ONLY

OPEN curInserted

-- Prima lettura
FETCH NEXT FROM curInserted 
INTO
      @IDAREA
    , @IDMANSIONE
    , @IDATTIVITA
    , @IDASSOCIAZIONE
    , @IDSOSTANZA
    , @RECORDELIMINATO
    , @USERID
    , @TIMESTAMP
    , @IDASSOCIAZIONEENTITA

-- Loop
WHILE @@FETCH_STATUS = 0 
BEGIN
    IF NOT EXISTS(
        SELECT *
        FROM dbo.ASSOCIAZIONI_AORMANATT
        WHERE IDAREA = @IDAREA
        AND IDMANSIONE = @IDMANSIONE
        AND IDATTIVITA = @IDATTIVITA
    ) BEGIN
        -- Inserisco una nuova riga in dbo.ASSOCIAZIONI_AORMANATT
        INSERT dbo.ASSOCIAZIONI_AORMANATT(IDAREA, IDMANSIONE, IDATTIVITA)
        VALUES (@IDAREA, @IDMANSIONE, @IDATTIVITA)
    
        SET @IDASSOCIAZIONE = SCOPE_IDENTITY()

        -- Inserisco una nuova riga in dbo.ASSOCIAZIONI_ENTITA
        INSERT dbo.ASSOCIAZIONI_ENTITA(IDASSOCIAZIONE, IDENTITA, RECORDELIMINATO, USERID, [TIMESTAMP])
        VALUES(
              @IDASSOCIAZIONE
            , @IDSOSTANZA
            , COALESCE(@RECORDELIMINATO, 0)
            , COALESCE(@USERID, 'master')
            , COALESCE(@TIMESTAMP, CURRENT_TIMESTAMP)
        )
    END

    -- Letture successive
    FETCH NEXT FROM curInserted 
    INTO
          @IDAREA
        , @IDMANSIONE
        , @IDATTIVITA
        , @IDASSOCIAZIONE
        , @IDSOSTANZA
        , @RECORDELIMINATO
        , @USERID
        , @TIMESTAMP
        , @IDASSOCIAZIONEENTITA
END

CLOSE curInserted
DEALLOCATE curInserted
GO

-- Inserimento singolo
INSERT dbo.VIEWSOSTANZAASSOCIAZIONI(IDAREA, IDMANSIONE, IDATTIVITA, IDSOSTANZA)
VALUES (1, 2, 3, 4);

SELECT *
FROM dbo.VIEWSOSTANZAASSOCIAZIONI;

/* Output:

IDAREA      IDMANSIONE  IDATTIVITA  IDASSOCIAZIONE IDSOSTANZA  RECORDELIMINATO USERID  TIMESTAMP               IDASSOCIAZIONEENTITA
----------- ----------- ----------- -------------- ----------- --------------- ------- ----------------------- --------------------
1           2           3           1              4           0               master  2008-06-17 18:10:59.437 1

(1 row(s) affected)

*/

-- Inserimento multiplo
INSERT dbo.VIEWSOSTANZAASSOCIAZIONI(IDAREA, IDMANSIONE, IDATTIVITA, IDSOSTANZA)
SELECT 5, 6, 7, 8
UNION ALL
SELECT 9, 10, 11, 12
UNION ALL
SELECT 13, 14, 15, 16;

SELECT *
FROM dbo.VIEWSOSTANZAASSOCIAZIONI;

/* Output:

IDAREA      IDMANSIONE  IDATTIVITA  IDASSOCIAZIONE IDSOSTANZA  RECORDELIMINATO USERID  TIMESTAMP               IDASSOCIAZIONEENTITA
----------- ----------- ----------- -------------- ----------- --------------- ------- ----------------------- --------------------
1           2           3           1              4           0               master  2008-06-17 18:10:59.437 1
5           6           7           2              8           0               master  2008-06-17 18:12:53.280 2
9           10          11          3              12          0               master  2008-06-17 18:12:53.280 3
13          14          15          4              16          0               master  2008-06-17 18:12:53.280 4

(4 row(s) affected)

*/

-- Inserimento con tutte le colonne (ad esclusione di IDASSOCIAZIONE e IDASSOCIAZIONEENTITA che sono IDENTITY)
INSERT dbo.VIEWSOSTANZAASSOCIAZIONI(
      IDAREA
    , IDMANSIONE
    , IDATTIVITA
    , IDSOSTANZA
    , RECORDELIMINATO
    , USERID
    , [TIMESTAMP]
)
VALUES (17, 18, 19, 20, 1, 'Lorenzo', CURRENT_TIMESTAMP);

SELECT *
FROM dbo.VIEWSOSTANZAASSOCIAZIONI;

/* Output:

IDAREA      IDMANSIONE  IDATTIVITA  IDASSOCIAZIONE IDSOSTANZA  RECORDELIMINATO USERID   TIMESTAMP               IDASSOCIAZIONEENTITA
----------- ----------- ----------- -------------- ----------- --------------- -------- ----------------------- --------------------
1           2           3           1              4           0               master   2008-06-17 18:10:59.437 1
5           6           7           2              8           0               master   2008-06-17 18:12:53.280 2
9           10          11          3              12          0               master   2008-06-17 18:12:53.280 3
13          14          15          4              16          0               master   2008-06-17 18:12:53.280 4
17          18          19          5              20          1               Lorenzo  2008-06-17 18:19:13.340 5

(5 row(s) affected)

*/

DROP VIEW dbo.VIEWSOSTANZAASSOCIAZIONI;
DROP TABLE dbo.ASSOCIAZIONI_ENTITA, dbo.ASSOCIAZIONI_AORMANATT;