Trigger per replicare da un db a un altro ............

USE tempdb;

CREATE TABLE dbo.Students1(
StudentID int NOT NULL IDENTITY PRIMARY KEY,
FirstName varchar(10) NOT NULL,
LastName varchar(10) NOT NULL
);

CREATE TABLE dbo.Students2(
StudentID int NOT NULL IDENTITY PRIMARY KEY,
FirstName varchar(10) NOT NULL,
LastName varchar(10) NOT NULL
);
GO

CREATE TRIGGER dbo.trI_AlignStudents
ON dbo.Students1
FOR INSERT
AS
IF NOT EXISTS(
    SELECT *
    FROM INSERTED AS I
    JOIN dbo.Students2 AS S2
    ON I.StudentID = S2.StudentID
)
BEGIN
    INSERT dbo.Students2(FirstName, LastName)
    SELECT FirstName, LastName
    FROM INSERTED
END
GO

CREATE TRIGGER dbo.trD_AlignStudents
ON dbo.Students1
FOR DELETE
AS
DELETE S2
FROM dbo.Students2 AS S2
JOIN DELETED AS D 
ON D.StudentID = S2.StudentID
GO

CREATE TRIGGER dbo.trU_AlignStudents
ON dbo.Students1
FOR UPDATE
AS
UPDATE S2
SET FirstName = I.FirstName, 
    LastName = I.LastName
FROM INSERTED AS I
JOIN dbo.Students2 AS S2
ON I.StudentID = S2.StudentID
GO

/* Inserimento singolo */
INSERT dbo.Students1 VALUES ('Lorenzo', 'Benaglia');

/* Inserimento multiplo */
INSERT dbo.Students1 
SELECT 'Luca', 'Bianchi'
UNION ALL
SELECT 'Andrea', 'Montanari'
UNION ALL
SELECT 'Gianluca', 'Hotz';

SELECT *
FROM dbo.Students2;

/* Output:

StudentID   FirstName  LastName
----------- ---------- ----------
1           Lorenzo    Benaglia
2           Gianluca   Hotz
3           Andrea     Montanari
4           Luca       Bianchi

(4 row(s) affected)

*/

/* Aggiornamento singolo */
UPDATE dbo.Students1
SET FirstName = 'Alessandro',
    LastName = 'Alpi'
WHERE StudentID = 2;

/* Aggiornamento multiplo */
UPDATE dbo.Students1
SET FirstName = 'Davide',
    LastName = 'Mauri'
WHERE StudentID IN(3, 4);

SELECT *
FROM dbo.Students2;

/* Output:

StudentID   FirstName  LastName
----------- ---------- ----------
1           Lorenzo    Benaglia
2           Alessandro Alpi
3           Davide     Mauri
4           Davide     Mauri

(4 row(s) affected)

*/

/* Eliminazione singola */
DELETE dbo.Students1
WHERE StudentID = 4;

/* Eliminazione multipla */
DELETE dbo.Students1
WHERE StudentID IN(2, 3);

SELECT *
FROM dbo.Students2;

/* Output:

StudentID   FirstName  LastName
----------- ---------- ----------
1           Lorenzo    Benaglia

(1 row(s) affected)

*/

DROP TABLE dbo.Students1, dbo.Students2;