Sql trigger for updating one table from another

Hi Readers, I had a requirement where I had to copy data from one SQL Server located in U. (Source Server) to SQL Server located in India (Destination Server).The copy has to be triggered when data is inserted at Source Server table..action_button.action_button:active.action_button:hover.action_button:focus.action_button:hover.action_button:focus .count.action_button:hover .count.action_button:focus .count:before.action_button:hover .count:before.u-margin-left--sm.u-flex.u-flex-auto.u-flex-none.bullet. Error Banner.fade_out.modal_overlay.modal_overlay .modal_wrapper.modal_overlay [email protected](max-width:630px)@media(max-width:630px).modal_overlay .modal_fixed_close.modal_overlay .modal_fixed_close:before.modal_overlay .modal_fixed_close:before.modal_overlay .modal_fixed_close:before.modal_overlay .modal_fixed_close:hover:before.

With every Insert in Employee Details table in local server, we want the records to be inserted in destination server table as well. Enable network DTC access, allow inbound and outbound communication with no authentication required as shown in screenshot below:- I had different collations in columns of source table and destination table in actual production scenario.

OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[FACILITIES]') AND TYPE IN (N'U')) DROP TABLE [DBO].[FACILITIES] GO CREATE TABLE FACILITIES( FACILITYID NCHAR(10) NOT NULL PRIMARY KEY, PROJECTID NCHAR(10) NULL, NAME VARCHAR(50) NULL, ADDRESS VARCHAR(50) NULL) IF EXISTS (SELECT * FROM SYS. FACILITYID IN (SELECT FACILITYID FROM INSERTED) END END Here you go.... if the name and address for the project and facilities are supposed to be the same then you should not be storing the columns on both tables...

TRIGGERS WHERE PARENT_CLASS = 1 AND NAME = 'UPDATEFACILITIES') DROP TRIGGER UPDATEFACILITIES; GO CREATE TRIGGER UPDATEFACILITIES ON FACILITIES AFTER INSERT, UPDATE AS BEGIN --CHECK IF PROJECTID EXISTS IN PROJECTS AND UPDATE FACILITIES IF EXISTS(SELECT PROJECTS.* FROM PROJECTS WHERE PROJECTS. CREATE TABLE PROJECTS( PROJECTID NCHAR(10) NOT NULL PRIMARY KEY, NAME VARCHAR(50) NULL, ADDRESS VARCHAR(50) NULL) CREATE TABLE FACILITIES( FACILITYID NCHAR(10) NOT NULL PRIMARY KEY, PROJECTID NCHAR(10) NOT NULL REFERENCES PROJECTS(PROJECTID), NAME VARCHAR(50) NULL, ADDRESS VARCHAR(50) NULL) CREATE TRIGGER UPDATEFACILITIES ON FACILITIES AFTER INSERT AS BEGIN DECLARE @NAME VARCHAR(50) DECLARE @ADDRESS VARCHAR(50) DECLARE @PROJECTID NCHAR(10) IF EXISTS(SELECT PROJ.* FROM PROJECTS PROJ, INSERTED INS WHERE PROJ. rather decide which entity the name and address should belong to and just maintain it on the correct entity...

The trigger need to fire up when the Project ID is inserted or changed in the Facilities table.

Also, if the Project ID is removed in Facilities or the Project ID does not existing in Projects table, the Name and Address in the Facilities table should be null.

Leave a Reply