Skip to content

Instantly share code, notes, and snippets.

@jjradha

jjradha/the_trigger_creation.xml

Created June 16, 2022 20:13
Show Gist options
  • Save jjradha/a7d4917901cc28bc7965ecb0e9fefab3 to your computer and use it in GitHub Desktop.
Save jjradha/a7d4917901cc28bc7965ecb0e9fefab3 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
the_trigger_creation.xml
<Krishna>IF 'my_server' &lt;&gt; @@ServerName THROW 50001, 'Wrong Server!!!',1 </Krishna>
<Krishna>GO</Krishna>
<Krishna>use [DB_02]</Krishna>
<Krishna>GO</Krishna>
<Krishna>IF EXISTS (SELECT 'Radhe' FROM sys.triggers t wHERE t.[name] = 'tgr_repl_AuPair_Insert' AND CASE WHEN t.parent_id = 0 THEN t.parent_id ELSE object_id('[sub].[repl_Aupair]') END = t.parent_id )
</Krishna>
<Krishna>EXEC('BEGIN DROP TRIGGER [sub].[tgr_repl_AuPair_Insert] END') </Krishna>
<Krishna>GO</Krishna>
<Krishna></Krishna>
<Krishna>CREATE TRIGGER [sub].[tgr_repl_AuPair_Insert]</Krishna>
<Krishna>ON [sub].[repl_Aupair]</Krishna>
<Krishna>FOR INSERT, UPDATE</Krishna>
<Krishna>AS</Krishna>
<Krishna>BEGIN</Krishna>
<Krishna>SET NOCOUNT ON</Krishna>
<Krishna></Krishna>
<Krishna>-- Declarations</Krishna>
<Krishna>DECLARE @PHASE TINYINT</Krishna>
<Krishna>DECLARE @ApplicationId INT</Krishna>
<Krishna></Krishna>
<Krishna>-- establish what applicationID we are talking about...</Krishna>
<Krishna>SELECT @ApplicationId = My_Database_one_applicationID</Krishna>
<Krishna>FROM inserted</Krishna>
<Krishna></Krishna>
<Krishna>-- we are assuming that as we are moving TO P4, if it exists in the P4 database then it will have been migrated..</Krishna>
<Krishna>SET @PHASE = CASE</Krishna>
<Krishna>WHEN EXISTS(</Krishna>
<Krishna>SELECT 1 FROM</Krishna>
<Krishna>[DB_02].app.application</Krishna>
<Krishna>WHERE applicationId = @ApplicationId</Krishna>
<Krishna>)</Krishna>
<Krishna>THEN 4</Krishna>
<Krishna>ELSE 3</Krishna>
<Krishna>END</Krishna>
<Krishna></Krishna>
<Krishna>/****** Below is the copy-paste code for Phase 3 *******/</Krishna>
<Krishna>IF(@PHASE = 3)</Krishna>
<Krishna>BEGIN</Krishna>
<Krishna>IF( UPDATE( APStatusDate ) )</Krishna>
<Krishna>BEGIN</Krishna>
<Krishna>INSERT INTO My_Database_one.dbo.replicationUpdateControl (applicationId, StatusChange, ModifyDate)</Krishna>
<Krishna>SELECT inserted.My_Database_one_ApplicationId,</Krishna>
<Krishna>1,</Krishna>
<Krishna>CURRENT_TIMESTAMP</Krishna>
<Krishna>FROM inserted</Krishna>
<Krishna>INNER JOIN My_Database_one.dbo.application AS app ON app.applicationId = inserted.My_Database_one_ApplicationId</Krishna>
<Krishna>LEFT JOIN deleted AS old ON old.My_Database_one_ApplicationId = inserted.My_Database_one_ApplicationId</Krishna>
<Krishna>WHERE old.APStatusDate IS NULL</Krishna>
<Krishna>OR old.APStatusDate &lt;&gt; inserted.APStatusDate</Krishna>
<Krishna>END</Krishna>
<Krishna></Krishna>
<Krishna>IF( UPDATE(IB_DEPARTURE_FLIGHT#) )</Krishna>
<Krishna>BEGIN</Krishna>
<Krishna>--Insert data into the tmp_AtlasFlightChange table as used in the placement reports</Krishna>
<Krishna>--bear in mind, for inserts, the deleted column will be null!</Krishna>
<Krishna>INSERT INTO My_Database_one.repl.flightChangesHistory</Krishna>
<Krishna>(</Krishna>
<Krishna>applicationID,</Krishna>
<Krishna>oldFlightReference,</Krishna>
<Krishna>newFlightReference,</Krishna>
<Krishna>importDate</Krishna>
<Krishna>)</Krishna>
<Krishna>SELECT ap.My_Database_one_ApplicationId,</Krishna>
<Krishna>old.flightReference,</Krishna>
<Krishna>new.flightReference,</Krishna>
<Krishna>CURRENT_TIMESTAMP</Krishna>
<Krishna>FROM inserted AS ap</Krishna>
<Krishna>INNER JOIN My_Database_one.dbo.vw_repl_Transportation AS new ON new.FLIGHT# = ap.IB_DEPARTURE_FLIGHT#</Krishna>
<Krishna>LEFT JOIN deleted AS d ON d.AuPairID = ap.AuPairId</Krishna>
<Krishna>INNER JOIN My_Database_one.dbo.vw_repl_Transportation AS old ON old.FLIGHT# = d.IB_DEPARTURE_FLIGHT#</Krishna>
<Krishna>WHERE d.IB_DEPARTURE_FLIGHT# IS NULL</Krishna>
<Krishna>OR d.IB_DEPARTURE_FLIGHT# &lt;&gt; ap.IB_DEPARTURE_FLIGHT#</Krishna>
<Krishna></Krishna>
<Krishna>INSERT INTO My_Database_one.dbo.replicationUpdateControl (applicationId, FlightChange, ModifyDate)</Krishna>
<Krishna>SELECT inserted.My_Database_one_ApplicationId,</Krishna>
<Krishna>1,</Krishna>
<Krishna>CURRENT_TIMESTAMP</Krishna>
<Krishna>FROM inserted</Krishna>
<Krishna>INNER JOIN My_Database_one.dbo.application AS app ON app.applicationId = inserted.My_Database_one_ApplicationId</Krishna>
<Krishna>LEFT JOIN deleted AS old ON old.My_Database_one_ApplicationId = inserted.My_Database_one_ApplicationId</Krishna>
<Krishna>WHERE old.IB_DEPARTURE_FLIGHT# IS NULL</Krishna>
<Krishna>OR old.IB_DEPARTURE_FLIGHT# &lt;&gt; inserted.IB_DEPARTURE_FLIGHT#</Krishna>
<Krishna>END</Krishna>
<Krishna></Krishna>
<Krishna>IF( UPDATE( US_COMMENTS ) )</Krishna>
<Krishna>BEGIN</Krishna>
<Krishna>INSERT INTO My_Database_one.dbo.replicationUpdateControl (applicationId, USCommentAdded, ModifyDate)</Krishna>
<Krishna>SELECT inserted.My_Database_one_ApplicationId,</Krishna>
<Krishna>1,</Krishna>
<Krishna>CURRENT_TIMESTAMP</Krishna>
<Krishna>FROM inserted</Krishna>
<Krishna>INNER JOIN My_Database_one.dbo.application AS app ON app.applicationId = inserted.My_Database_one_ApplicationId</Krishna>
<Krishna>LEFT JOIN deleted AS old ON old.AuPairId = inserted.AuPairId</Krishna>
<Krishna>WHERE inserted.US_COMMENTS IS NOT NULL</Krishna>
<Krishna>AND (</Krishna>
<Krishna>old.US_COMMENTS IS NULL</Krishna>
<Krishna>OR old.US_COMMENTS &lt;&gt; inserted.US_COMMENTS</Krishna>
<Krishna>)</Krishna>
<Krishna>END</Krishna>
<Krishna></Krishna>
<Krishna>IF( UPDATE( AP_STATUS_REASON ) )</Krishna>
<Krishna>BEGIN</Krishna>
<Krishna>INSERT INTO My_Database_one.dbo.replicationUpdateControl (applicationId, StatusCommentAdded, ModifyDate)</Krishna>
<Krishna>SELECT inserted.My_Database_one_ApplicationId,</Krishna>
<Krishna>1,</Krishna>
<Krishna>CURRENT_TIMESTAMP</Krishna>
<Krishna>FROM inserted</Krishna>
<Krishna>INNER JOIN My_Database_one.dbo.application AS app ON app.applicationId = inserted.My_Database_one_ApplicationId</Krishna>
<Krishna>LEFT JOIN deleted AS old ON old.AuPairId = inserted.AuPairId</Krishna>
<Krishna>WHERE inserted.AP_STATUS_REASON IS NOT NULL</Krishna>
<Krishna>AND (</Krishna>
<Krishna>old.AP_STATUS_REASON IS NULL</Krishna>
<Krishna>OR old.AP_STATUS_REASON &lt;&gt; inserted.AP_STATUS_REASON</Krishna>
<Krishna>)</Krishna>
<Krishna>END</Krishna>
<Krishna></Krishna>
<Krishna>/* Merged into the replicationUpdateControl table logic for consistency - also in prep for phase 4 integration */</Krishna>
<Krishna>IF( UPDATE( AP_STATUS_REASON_CODE ) )</Krishna>
<Krishna>BEGIN</Krishna>
<Krishna>INSERT INTO My_Database_one.dbo.replicationUpdateControl (applicationId, [StatusReasonCodeChanged], ModifyDate)</Krishna>
<Krishna>SELECT inserted.My_Database_one_ApplicationId,</Krishna>
<Krishna>1,</Krishna>
<Krishna>CURRENT_TIMESTAMP</Krishna>
<Krishna>FROM inserted</Krishna>
<Krishna>INNER JOIN My_Database_one.dbo.application AS app ON app.applicationId = inserted.My_Database_one_ApplicationId</Krishna>
<Krishna>LEFT JOIN deleted AS old ON old.AuPairId = inserted.AuPairId</Krishna>
<Krishna>WHERE inserted.AP_STATUS_REASON_CODE IS NOT NULL</Krishna>
<Krishna>AND (</Krishna>
<Krishna>old.AP_STATUS_REASON_CODE IS NULL</Krishna>
<Krishna>OR old.AP_STATUS_REASON_CODE &lt;&gt; inserted.AP_STATUS_REASON_CODE</Krishna>
<Krishna>)</Krishna>
<Krishna>END</Krishna>
<Krishna></Krishna>
<Krishna>IF( UPDATE( OB_DEPARTURE ) )</Krishna>
<Krishna>BEGIN</Krishna>
<Krishna>INSERT INTO My_Database_one.dbo.replicationUpdateControl (applicationId, [OutboundFlightDateChanged], ModifyDate)</Krishna>
<Krishna>SELECT inserted.My_Database_one_ApplicationId,</Krishna>
<Krishna>1,</Krishna>
<Krishna>CURRENT_TIMESTAMP</Krishna>
<Krishna>FROM inserted</Krishna>
<Krishna>INNER JOIN My_Database_one.dbo.application AS app ON app.applicationId = inserted.My_Database_one_ApplicationId</Krishna>
<Krishna>LEFT JOIN deleted AS old ON old.AuPairId = inserted.AuPairId</Krishna>
<Krishna>WHERE inserted.OB_DEPARTURE IS NOT NULL</Krishna>
<Krishna>AND (</Krishna>
<Krishna>old.OB_DEPARTURE IS NULL</Krishna>
<Krishna>OR old.OB_DEPARTURE &lt;&gt; inserted.OB_DEPARTURE</Krishna>
<Krishna>)</Krishna>
<Krishna>END</Krishna>
<Krishna></Krishna>
<Krishna>IF( UPDATE( Sevis_ID ) )</Krishna>
<Krishna>BEGIN</Krishna>
<Krishna>INSERT INTO My_Database_one.dbo.replicationUpdateControl (applicationId, [SevisIdChanged], ModifyDate)</Krishna>
<Krishna>SELECT inserted.My_Database_one_ApplicationId,</Krishna>
<Krishna>1,</Krishna>
<Krishna>CURRENT_TIMESTAMP</Krishna>
<Krishna>FROM inserted</Krishna>
<Krishna>INNER JOIN My_Database_one.dbo.application AS app ON app.applicationId = inserted.My_Database_one_ApplicationId</Krishna>
<Krishna>LEFT JOIN deleted AS old ON old.AuPairId = inserted.AuPairId</Krishna>
<Krishna>WHERE inserted.Sevis_ID IS NOT NULL</Krishna>
<Krishna>AND (</Krishna>
<Krishna>old.Sevis_ID IS NULL</Krishna>
<Krishna>OR old.Sevis_ID &lt;&gt; inserted.Sevis_ID</Krishna>
<Krishna>)</Krishna>
<Krishna>END</Krishna>
<Krishna></Krishna>
<Krishna>IF( UPDATE( ArrivalDate ) )</Krishna>
<Krishna>BEGIN</Krishna>
<Krishna>INSERT INTO My_Database_one.dbo.replicationUpdateControl (applicationId, [ArrivalDateChanged], ModifyDate)</Krishna>
<Krishna>SELECT inserted.My_Database_one_ApplicationId,</Krishna>
<Krishna>1,</Krishna>
<Krishna>CURRENT_TIMESTAMP</Krishna>
<Krishna>FROM inserted</Krishna>
<Krishna>INNER JOIN My_Database_one.dbo.application AS app ON app.applicationId = inserted.My_Database_one_ApplicationId</Krishna>
<Krishna>LEFT JOIN deleted AS old ON old.AuPairId = inserted.AuPairId</Krishna>
<Krishna>WHERE inserted.ArrivalDate IS NOT NULL</Krishna>
<Krishna>AND (</Krishna>
<Krishna>old.ArrivalDate IS NULL</Krishna>
<Krishna>OR old.ArrivalDate &lt;&gt; inserted.ArrivalDate</Krishna>
<Krishna>)</Krishna>
<Krishna>END</Krishna>
<Krishna>END</Krishna>
<Krishna></Krishna>
<Krishna>IF(@PHASE = 4)</Krishna>
<Krishna>BEGIN</Krishna>
<Krishna>/****** Application Status Date change *******/</Krishna>
<Krishna>IF( UPDATE( APStatusDate ) )</Krishna>
<Krishna>BEGIN</Krishna>
<Krishna>INSERT INTO repl.replicationUpdateControl (applicationId, statusChange, modifyDate, phase)</Krishna>
<Krishna>SELECT inserted.My_Database_one_applicationID, 1, CURRENT_TIMESTAMP, @PHASE</Krishna>
<Krishna>FROM inserted</Krishna>
<Krishna>INNER JOIN [DB_02].app.application AS app ON app.applicationId = inserted.My_Database_one_applicationID</Krishna>
<Krishna>LEFT JOIN deleted AS old ON old.My_Database_one_applicationID = inserted.My_Database_one_applicationID</Krishna>
<Krishna>WHERE old.APStatusDate IS NULL</Krishna>
<Krishna>OR old.APStatusDate &lt;&gt; inserted.APStatusDate</Krishna>
<Krishna>END</Krishna>
<Krishna></Krishna>
<Krishna>/****** Departure Flight Information *******/</Krishna>
<Krishna>IF( UPDATE(IB_DEPARTURE_FLIGHT#) )</Krishna>
<Krishna>BEGIN</Krishna>
<Krishna>--Insert data into the tmp_AtlasFlightChange table as used in the placement reports</Krishna>
<Krishna>--bear in mind, for inserts, the deleted column will be null!</Krishna>
<Krishna>INSERT INTO repl.flightChangesHistory</Krishna>
<Krishna>(</Krishna>
<Krishna>applicationID,</Krishna>
<Krishna>oldFlightReference,</Krishna>
<Krishna>newFlightReference,</Krishna>
<Krishna>importDate</Krishna>
<Krishna>)</Krishna>
<Krishna>SELECT ap.My_Database_one_applicationID,</Krishna>
<Krishna>old.flightReference,</Krishna>
<Krishna>new.flightReference,</Krishna>
<Krishna>CURRENT_TIMESTAMP</Krishna>
<Krishna>FROM inserted AS ap</Krishna>
<Krishna>INNER JOIN repl.vw_repl_transportation AS new ON new.FLIGHT# = ap.IB_DEPARTURE_FLIGHT#</Krishna>
<Krishna>LEFT JOIN deleted AS d ON d.AuPairID = ap.AuPairID</Krishna>
<Krishna>INNER JOIN [DB_02].app.vw_repl_transportation AS old ON old.FLIGHT# = d.IB_DEPARTURE_FLIGHT#</Krishna>
<Krishna>WHERE d.IB_DEPARTURE_FLIGHT# IS NULL</Krishna>
<Krishna>OR d.IB_DEPARTURE_FLIGHT# &lt;&gt; ap.IB_DEPARTURE_FLIGHT#</Krishna>
<Krishna></Krishna>
<Krishna>-- Insert a relevant control value into the control table</Krishna>
<Krishna>INSERT INTO repl.replicationUpdateControl (applicationId, flightChange, modifyDate, phase)</Krishna>
<Krishna>SELECT inserted.My_Database_one_applicationID, 1, CURRENT_TIMESTAMP, @PHASE</Krishna>
<Krishna>FROM inserted</Krishna>
<Krishna>INNER JOIN [DB_02].app.application AS app ON app.applicationId = inserted.My_Database_one_applicationID</Krishna>
<Krishna>LEFT JOIN deleted AS old ON old.My_Database_one_applicationID = inserted.My_Database_one_applicationID</Krishna>
<Krishna>WHERE old.IB_DEPARTURE_FLIGHT# IS NULL</Krishna>
<Krishna>OR old.IB_DEPARTURE_FLIGHT# &lt;&gt; inserted.IB_DEPARTURE_FLIGHT#</Krishna>
<Krishna>END</Krishna>
<Krishna></Krishna>
<Krishna>/****** Comments come from the USA...ooOOOooooh *******/</Krishna>
<Krishna>IF( UPDATE( US_COMMENTS ) )</Krishna>
<Krishna>BEGIN</Krishna>
<Krishna>INSERT INTO repl.replicationUpdateControl (applicationId, USCommentAdded, ModifyDate, phase)</Krishna>
<Krishna>SELECT inserted.My_Database_one_applicationID, 1, CURRENT_TIMESTAMP, @PHASE</Krishna>
<Krishna>FROM inserted</Krishna>
<Krishna>INNER JOIN [DB_02].app.application AS app ON app.applicationId = inserted.My_Database_one_applicationID</Krishna>
<Krishna>LEFT JOIN deleted AS old ON old.My_Database_one_applicationID = inserted.My_Database_one_applicationID</Krishna>
<Krishna>WHERE inserted.US_COMMENTS IS NOT NULL</Krishna>
<Krishna>AND (</Krishna>
<Krishna>old.US_COMMENTS IS NULL</Krishna>
<Krishna>OR old.US_COMMENTS &lt;&gt; inserted.US_COMMENTS</Krishna>
<Krishna>)</Krishna>
<Krishna>END</Krishna>
<Krishna></Krishna>
<Krishna>/****** Status Reason Change *******/</Krishna>
<Krishna>IF( UPDATE( AP_STATUS_REASON ) )</Krishna>
<Krishna>BEGIN</Krishna>
<Krishna>INSERT INTO repl.replicationUpdateControl (applicationId, statusCommentAdded, modifyDate, phase)</Krishna>
<Krishna>SELECT inserted.My_Database_one_applicationID, 1, CURRENT_TIMESTAMP, @PHASE</Krishna>
<Krishna>FROM inserted</Krishna>
<Krishna>INNER JOIN [DB_02].app.application AS app ON app.applicationId = inserted.My_Database_one_applicationID</Krishna>
<Krishna>LEFT JOIN deleted AS old ON old.My_Database_one_applicationID = inserted.My_Database_one_applicationID</Krishna>
<Krishna>WHERE inserted.AP_STATUS_REASON IS NOT NULL</Krishna>
<Krishna>AND (</Krishna>
<Krishna>old.AP_STATUS_REASON IS NULL</Krishna>
<Krishna>OR old.AP_STATUS_REASON &lt;&gt; inserted.AP_STATUS_REASON</Krishna>
<Krishna>)</Krishna>
<Krishna>END</Krishna>
<Krishna></Krishna>
<Krishna>/****** Status Reason CODE Change *******/</Krishna>
<Krishna>IF( UPDATE( AP_STATUS_REASON_CODE ) )</Krishna>
<Krishna>BEGIN</Krishna>
<Krishna>INSERT INTO repl.replicationUpdateControl (applicationId, statusReasonCodeChanged, modifyDate, phase)</Krishna>
<Krishna>SELECT inserted.My_Database_one_applicationID, 1, CURRENT_TIMESTAMP, @PHASE</Krishna>
<Krishna>FROM inserted</Krishna>
<Krishna>INNER JOIN [DB_02].app.application AS app ON app.applicationId = inserted.My_Database_one_applicationID</Krishna>
<Krishna>LEFT JOIN deleted AS old ON old.AuPairID = inserted.AuPairID</Krishna>
<Krishna>WHERE inserted.AP_STATUS_REASON_CODE IS NOT NULL</Krishna>
<Krishna>AND (</Krishna>
<Krishna>old.AP_STATUS_REASON_CODE IS NULL</Krishna>
<Krishna>OR old.AP_STATUS_REASON_CODE &lt;&gt; inserted.AP_STATUS_REASON_CODE</Krishna>
<Krishna>)</Krishna>
<Krishna>END</Krishna>
<Krishna></Krishna>
<Krishna>/****** Outbound Departure change *******/</Krishna>
<Krishna>IF( UPDATE( OB_DEPARTURE ) )</Krishna>
<Krishna>BEGIN</Krishna>
<Krishna>INSERT INTO repl.replicationUpdateControl (applicationId, outboundFlightDateChanged, modifyDate, phase)</Krishna>
<Krishna>SELECT inserted.My_Database_one_applicationID, 1, CURRENT_TIMESTAMP, @PHASE</Krishna>
<Krishna>FROM inserted</Krishna>
<Krishna>INNER JOIN [DB_02].app.application AS app ON app.applicationID = inserted.My_Database_one_applicationID</Krishna>
<Krishna>LEFT JOIN deleted AS old ON old.AuPairID = inserted.AuPairID</Krishna>
<Krishna>WHERE inserted.OB_DEPARTURE IS NOT NULL</Krishna>
<Krishna>AND (</Krishna>
<Krishna>old.OB_DEPARTURE IS NULL</Krishna>
<Krishna>OR old.OB_DEPARTURE &lt;&gt; inserted.OB_DEPARTURE</Krishna>
<Krishna>)</Krishna>
<Krishna>END</Krishna>
<Krishna></Krishna>
<Krishna>/****** SEVIS ID Change *******/</Krishna>
<Krishna>IF( UPDATE( Sevis_ID ) )</Krishna>
<Krishna>BEGIN</Krishna>
<Krishna>INSERT INTO repl.replicationUpdateControl (applicationId, sevisIdChanged, modifyDate, phase)</Krishna>
<Krishna>SELECT inserted.My_Database_one_applicationID, 1, CURRENT_TIMESTAMP, @PHASE</Krishna>
<Krishna>FROM inserted</Krishna>
<Krishna>INNER JOIN [DB_02].app.application AS app ON app.applicationID = inserted.My_Database_one_applicationID</Krishna>
<Krishna>LEFT JOIN deleted AS old ON old.AuPairID = inserted.AuPairID</Krishna>
<Krishna>WHERE inserted.Sevis_ID IS NOT NULL</Krishna>
<Krishna>AND (</Krishna>
<Krishna>old.Sevis_ID IS NULL</Krishna>
<Krishna>OR old.Sevis_ID &lt;&gt; inserted.Sevis_ID</Krishna>
<Krishna>)</Krishna>
<Krishna>END</Krishna>
<Krishna></Krishna>
<Krishna>/****** Arrival Date Change *******/</Krishna>
<Krishna>IF( UPDATE( ArrivalDate ) )</Krishna>
<Krishna>BEGIN</Krishna>
<Krishna>INSERT INTO repl.replicationUpdateControl (applicationId, arrivalDateChanged, modifyDate, phase)</Krishna>
<Krishna>SELECT inserted.My_Database_one_applicationID, 1, CURRENT_TIMESTAMP, @PHASE</Krishna>
<Krishna>FROM inserted</Krishna>
<Krishna>INNER JOIN [DB_02].app.application AS app ON app.applicationID = inserted.My_Database_one_applicationID</Krishna>
<Krishna>LEFT JOIN deleted AS old ON old.AuPairID = inserted.AuPairID</Krishna>
<Krishna>WHERE inserted.ArrivalDate IS NOT NULL</Krishna>
<Krishna>AND (</Krishna>
<Krishna>old.ArrivalDate IS NULL</Krishna>
<Krishna>OR old.ArrivalDate &lt;&gt; inserted.ArrivalDate</Krishna>
<Krishna>)</Krishna>
<Krishna>END</Krishna>
<Krishna></Krishna>
<Krishna>/****** IB Flight booked Change *******/</Krishna>
<Krishna>IF( UPDATE( [IB_flight_booked] ) )</Krishna>
<Krishna>BEGIN</Krishna>
<Krishna>INSERT INTO repl.replicationUpdateControl (applicationId, inboundFlightBookedChanged, modifyDate, phase)</Krishna>
<Krishna>SELECT inserted.My_Database_one_applicationID, 1, CURRENT_TIMESTAMP, @PHASE</Krishna>
<Krishna>FROM inserted</Krishna>
<Krishna>INNER JOIN [DB_02].app.application AS app ON app.applicationID = inserted.My_Database_one_applicationID</Krishna>
<Krishna>LEFT JOIN deleted AS old ON old.AuPairID = inserted.AuPairID</Krishna>
<Krishna>WHERE inserted.IB_flight_booked IS NOT NULL</Krishna>
<Krishna>AND (</Krishna>
<Krishna>old.IB_flight_booked IS NULL</Krishna>
<Krishna>OR old.IB_flight_booked &lt;&gt; inserted.IB_flight_booked</Krishna>
<Krishna>)</Krishna>
<Krishna>END</Krishna>
<Krishna>END</Krishna>
<Krishna>END</Krishna>
<Krishna>GO</Krishna>
<Krishna>GO</Krishna>
<Krishna>use [DB_02]</Krishna>
<Krishna>GO</Krishna>
<Krishna>IF EXISTS (SELECT 'Radhe' FROM sys.triggers t wHERE t.[name] = 'tgr_repl_AuPair_status_change' AND CASE WHEN t.parent_id = 0 THEN t.parent_id ELSE object_id('[sub].[repl_AuPair_Status_H2]') END = t.parent_id )
</Krishna>
<Krishna>EXEC('BEGIN DROP TRIGGER [sub].[tgr_repl_AuPair_status_change] END') </Krishna>
<Krishna>GO</Krishna>
<Krishna></Krishna>
<Krishna></Krishna>
<Krishna>CREATE TRIGGER [sub].[tgr_repl_AuPair_status_change]</Krishna>
<Krishna>ON [sub].[repl_AuPair_Status_H2]</Krishna>
<Krishna>FOR INSERT</Krishna>
<Krishna>AS</Krishna>
<Krishna>BEGIN</Krishna>
<Krishna>SET NOCOUNT ON</Krishna>
<Krishna></Krishna>
<Krishna>INSERT INTO repl.replicationStatusControl</Krishna>
<Krishna>(</Krishna>
<Krishna>applicationid,</Krishna>
<Krishna>oldStatus,</Krishna>
<Krishna>newStatus,</Krishna>
<Krishna>oldStatusReasonCode,</Krishna>
<Krishna>newStatusReasonCode,</Krishna>
<Krishna>oldStatusReason,</Krishna>
<Krishna>newStatusReason,</Krishna>
<Krishna>processed,</Krishna>
<Krishna>loggedDate</Krishna>
<Krishna>)</Krishna>
<Krishna>SELECT</Krishna>
<Krishna>rap.My_Database_one_applicationID,</Krishna>
<Krishna>i.OldAPStatusCode,</Krishna>
<Krishna>i.NewAPStatusCode,</Krishna>
<Krishna>i.OldAP_Status_Reason_Code,</Krishna>
<Krishna>i.NewAP_Status_Reason_Code,</Krishna>
<Krishna>i.OldAP_Status_Reason,</Krishna>
<Krishna>i.NewAP_Status_Reason,</Krishna>
<Krishna>0,</Krishna>
<Krishna>CURRENT_TIMESTAMP</Krishna>
<Krishna>FROM inserted i</Krishna>
<Krishna>INNER JOIN dbo.repl_Aupair rap ON rap.AuPairID = i.AuPairID</Krishna>
<Krishna>WHERE i.OldAPStatusCode &lt;&gt; i.NewAPStatusCode</Krishna>
<Krishna></Krishna>
<Krishna>END</Krishna>
<Krishna>GO</Krishna>
<Krishna>GO</Krishna>
<Krishna>use [DB_02]</Krishna>
<Krishna>GO</Krishna>
<Krishna>IF EXISTS (SELECT 'Radhe' FROM sys.triggers t wHERE t.[name] = 'tgr_repl_Match_UpdateInsert' AND CASE WHEN t.parent_id = 0 THEN t.parent_id ELSE object_id('[sub].[repl_Match]') END = t.parent_id )
</Krishna>
<Krishna>EXEC('BEGIN DROP TRIGGER [sub].[tgr_repl_Match_UpdateInsert] END') </Krishna>
<Krishna>GO</Krishna>
<Krishna></Krishna>
<Krishna></Krishna>
<Krishna>CREATE TRIGGER [sub].[tgr_repl_Match_UpdateInsert]</Krishna>
<Krishna>ON [sub].[repl_Match]</Krishna>
<Krishna>FOR UPDATE, INSERT</Krishna>
<Krishna>AS</Krishna>
<Krishna>BEGIN</Krishna>
<Krishna>SET NOCOUNT ON</Krishna>
<Krishna></Krishna>
<Krishna>-- Declarations</Krishna>
<Krishna>DECLARE @PHASE TINYINT</Krishna>
<Krishna>DECLARE @ApplicationId INT</Krishna>
<Krishna></Krishna>
<Krishna>-- establish what applicationID we are talking about...</Krishna>
<Krishna>SELECT @ApplicationId = ( SELECT a.My_Database_one_applicationID</Krishna>
<Krishna>FROM inserted AS i</Krishna>
<Krishna>INNER JOIN repl_Aupair AS a ON a.AuPairID = i.AuPairId</Krishna>
<Krishna>)</Krishna>
<Krishna></Krishna>
<Krishna>-- we are assuming that as we are moving TO P4, if it exists in the P4 database then it will have been migrated..</Krishna>
<Krishna>SET @PHASE = CASE</Krishna>
<Krishna>WHEN EXISTS(</Krishna>
<Krishna>SELECT 1 FROM</Krishna>
<Krishna>[DB_02].app.application</Krishna>
<Krishna>WHERE applicationId = @ApplicationId</Krishna>
<Krishna>)</Krishna>
<Krishna>THEN 4</Krishna>
<Krishna>ELSE 3</Krishna>
<Krishna>END</Krishna>
<Krishna></Krishna>
<Krishna>/****** Below is the copy-paste code for Phase 3 *******/</Krishna>
<Krishna>IF(@PHASE = 3)</Krishna>
<Krishna>BEGIN</Krishna>
<Krishna>IF( UPDATE( MatchStartDate ) OR UPDATE ( MatchStatusCode ) )</Krishna>
<Krishna>BEGIN</Krishna>
<Krishna>INSERT INTO My_Database_one.dbo.replicationUpdateControl (applicationId, MatchDateChange, ModifyDate)</Krishna>
<Krishna>SELECT DISTINCT a.My_Database_one_ApplicationId,</Krishna>
<Krishna>1,</Krishna>
<Krishna>getdate()</Krishna>
<Krishna>FROM inserted AS i</Krishna>
<Krishna>INNER JOIN repl_AuPair AS a ON a.AupairId = i.AupairId</Krishna>
<Krishna>LEFT JOIN deleted AS u ON u.Match_id = i.Match_id</Krishna>
<Krishna>WHERE (u.MatchStartDate IS NULL OR u.MatchStartDate &lt;&gt; i.MatchStartDate)</Krishna>
<Krishna>OR (u.MatchStatusCode IS NULL OR u.MatchStatusCode &lt;&gt; i.MatchStatusCode)</Krishna>
<Krishna>END</Krishna>
<Krishna></Krishna>
<Krishna>IF( UPDATE( STATUS_REASON ) )</Krishna>
<Krishna>BEGIN</Krishna>
<Krishna>INSERT INTO My_Database_one.dbo.replicationUpdateControl (applicationId, MatchCommentAdded, ModifyDate)</Krishna>
<Krishna>SELECT app.My_Database_one_ApplicationId,</Krishna>
<Krishna>1,</Krishna>
<Krishna>getdate()</Krishna>
<Krishna>FROM inserted AS new</Krishna>
<Krishna>INNER JOIN repl_AuPair AS app ON app.AuPairId = new.AuPairId</Krishna>
<Krishna>LEFT JOIN deleted AS old ON old.match_id = new.match_id</Krishna>
<Krishna>WHERE new.STATUS_REASON IS NOT NULL</Krishna>
<Krishna>AND (</Krishna>
<Krishna>old.STATUS_REASON IS NULL</Krishna>
<Krishna>OR old.STATUS_REASON &lt;&gt; new.STATUS_REASON</Krishna>
<Krishna>)</Krishna>
<Krishna>END</Krishna>
<Krishna></Krishna>
<Krishna>INSERT INTO My_Database_one.dbo.matchHistory</Krishna>
<Krishna>(</Krishna>
<Krishna>matchID,</Krishna>
<Krishna>changeDate,</Krishna>
<Krishna>startDate,</Krishna>
<Krishna>endDate,</Krishna>
<Krishna>programID,</Krishna>
<Krishna>matchStatus,</Krishna>
<Krishna>matchStatusReason,</Krishna>
<Krishna>extension,</Krishna>
<Krishna>extensionType</Krishna>
<Krishna>)</Krishna>
<Krishna>SELECT match_id,</Krishna>
<Krishna>CASE WHEN modify_date IS NULL THEN create_date ELSE modify_date END,</Krishna>
<Krishna>matchStartDate,</Krishna>
<Krishna>matchEndDate,</Krishna>
<Krishna>matchProgramCode,</Krishna>
<Krishna>matchStatusCode,</Krishna>
<Krishna>STATUS_REASON,</Krishna>
<Krishna>CASE Y2_MATCH_TYPE</Krishna>
<Krishna>WHEN 'EN' THEN 1</Krishna>
<Krishna>WHEN 'ES' THEN 1</Krishna>
<Krishna>WHEN 'RX' THEN 1</Krishna>
<Krishna>WHEN 'Y1' THEN 0</Krishna>
<Krishna>ELSE 0</Krishna>
<Krishna>END,</Krishna>
<Krishna>Y2_MATCH_TYPE</Krishna>
<Krishna>FROM inserted</Krishna>
<Krishna>WHERE inserted.MatchStatusCode &lt;&gt; 'PND'</Krishna>
<Krishna>END</Krishna>
<Krishna></Krishna>
<Krishna>IF(@PHASE = 4)</Krishna>
<Krishna>BEGIN</Krishna>
<Krishna>IF( UPDATE( MatchStartDate ) OR UPDATE ( MatchStatusCode ) )</Krishna>
<Krishna>BEGIN</Krishna>
<Krishna>INSERT INTO repl.replicationUpdateControl (applicationId, MatchDateChange, ModifyDate, phase)</Krishna>
<Krishna>SELECT DISTINCT a.My_Database_one_ApplicationId,</Krishna>
<Krishna>1,</Krishna>
<Krishna>getdate(),</Krishna>
<Krishna>@PHASE</Krishna>
<Krishna>FROM inserted AS i</Krishna>
<Krishna>INNER JOIN repl_AuPair AS a ON a.AupairId = i.AupairId</Krishna>
<Krishna>LEFT JOIN deleted AS u ON u.Match_id = i.Match_id</Krishna>
<Krishna>WHERE (u.MatchStartDate IS NULL OR u.MatchStartDate &lt;&gt; i.MatchStartDate)</Krishna>
<Krishna>OR (u.MatchStatusCode IS NULL OR u.MatchStatusCode &lt;&gt; i.MatchStatusCode)</Krishna>
<Krishna>END</Krishna>
<Krishna></Krishna>
<Krishna>IF( UPDATE( STATUS_REASON ) )</Krishna>
<Krishna>BEGIN</Krishna>
<Krishna>INSERT INTO repl.replicationUpdateControl (applicationId, MatchCommentAdded, ModifyDate, phase)</Krishna>
<Krishna>SELECT app.My_Database_one_ApplicationId,</Krishna>
<Krishna>1,</Krishna>
<Krishna>getdate(),</Krishna>
<Krishna>@PHASE</Krishna>
<Krishna>FROM inserted AS new</Krishna>
<Krishna>INNER JOIN repl_AuPair AS app ON app.AuPairId = new.AuPairId</Krishna>
<Krishna>LEFT JOIN deleted AS old ON old.match_id = new.match_id</Krishna>
<Krishna>WHERE new.STATUS_REASON IS NOT NULL</Krishna>
<Krishna>AND (</Krishna>
<Krishna>old.STATUS_REASON IS NULL</Krishna>
<Krishna>OR old.STATUS_REASON &lt;&gt; new.STATUS_REASON</Krishna>
<Krishna>)</Krishna>
<Krishna>END</Krishna>
<Krishna></Krishna>
<Krishna>INSERT INTO repl.matchHistory</Krishna>
<Krishna>(</Krishna>
<Krishna>matchID,</Krishna>
<Krishna>changeDate,</Krishna>
<Krishna>startDate,</Krishna>
<Krishna>endDate,</Krishna>
<Krishna>programID,</Krishna>
<Krishna>matchStatus,</Krishna>
<Krishna>matchStatusReason,</Krishna>
<Krishna>extension,</Krishna>
<Krishna>extensionType</Krishna>
<Krishna>)</Krishna>
<Krishna>SELECT match_id,</Krishna>
<Krishna>CASE WHEN modify_date IS NULL THEN create_date ELSE modify_date END,</Krishna>
<Krishna>matchStartDate,</Krishna>
<Krishna>matchEndDate,</Krishna>
<Krishna>matchProgramCode,</Krishna>
<Krishna>matchStatusCode,</Krishna>
<Krishna>STATUS_REASON,</Krishna>
<Krishna>CASE Y2_MATCH_TYPE</Krishna>
<Krishna>WHEN 'EN' THEN 1</Krishna>
<Krishna>WHEN 'ES' THEN 1</Krishna>
<Krishna>WHEN 'RX' THEN 1</Krishna>
<Krishna>WHEN 'Y1' THEN 0</Krishna>
<Krishna>ELSE 0</Krishna>
<Krishna>END,</Krishna>
<Krishna>Y2_MATCH_TYPE</Krishna>
<Krishna>FROM inserted</Krishna>
<Krishna>WHERE inserted.MatchStatusCode &lt;&gt; 'PND'</Krishna>
<Krishna>END</Krishna>
<Krishna></Krishna>
<Krishna>END</Krishna>
<Krishna>GO</Krishna>
<Krishna>GO</Krishna>
<Krishna>use [DB_02]</Krishna>
<Krishna>GO</Krishna>
<Krishna>IF EXISTS (SELECT 'Radhe' FROM sys.triggers t wHERE t.[name] = 'tgr_repl_Proposal_Insert' AND CASE WHEN t.parent_id = 0 THEN t.parent_id ELSE object_id('[sub].[repl_Proposal]') END = t.parent_id )
</Krishna>
<Krishna>EXEC('BEGIN DROP TRIGGER [sub].[tgr_repl_Proposal_Insert] END') </Krishna>
<Krishna>GO</Krishna>
<Krishna></Krishna>
<Krishna></Krishna>
<Krishna>CREATE TRIGGER [sub].[tgr_repl_Proposal_Insert]</Krishna>
<Krishna>ON [sub].[repl_Proposal]</Krishna>
<Krishna>FOR INSERT</Krishna>
<Krishna>AS</Krishna>
<Krishna>BEGIN</Krishna>
<Krishna>SET NOCOUNT ON</Krishna>
<Krishna></Krishna>
<Krishna>-- Declarations</Krishna>
<Krishna>DECLARE @PHASE TINYINT</Krishna>
<Krishna>DECLARE @ApplicationId INT</Krishna>
<Krishna></Krishna>
<Krishna>-- establish what applicationID we are talking about...</Krishna>
<Krishna>SELECT @ApplicationId = ( SELECT a.My_Database_one_applicationID</Krishna>
<Krishna>FROM inserted AS i</Krishna>
<Krishna>INNER JOIN repl_Aupair AS a ON a.AuPairID = i.AuPairId</Krishna>
<Krishna>)</Krishna>
<Krishna></Krishna>
<Krishna>-- we are assuming that as we are moving TO P4, if it exists in the P4 database then it will have been migrated..</Krishna>
<Krishna>SET @PHASE = CASE</Krishna>
<Krishna>WHEN EXISTS(</Krishna>
<Krishna>SELECT 1 FROM</Krishna>
<Krishna>[DB_02].app.application</Krishna>
<Krishna>WHERE applicationId = @ApplicationId</Krishna>
<Krishna>)</Krishna>
<Krishna>THEN 4</Krishna>
<Krishna>ELSE 3</Krishna>
<Krishna>END</Krishna>
<Krishna></Krishna>
<Krishna>/****** Below is the copy-paste code for Phase 3 *******/</Krishna>
<Krishna>IF(@PHASE = 3)</Krishna>
<Krishna>BEGIN</Krishna>
<Krishna>IF( UPDATE( OpenStatusCode ) OR UPDATE ( ProposalSubstatusCode ) )</Krishna>
<Krishna>BEGIN</Krishna>
<Krishna>INSERT INTO My_Database_one.dbo.replicationUpdateControl (applicationId, ProposalAdded, ProposalId, ModifyDate)</Krishna>
<Krishna>SELECT DISTINCT a.My_Database_one_ApplicationId,</Krishna>
<Krishna>1,</Krishna>
<Krishna>proposalId,</Krishna>
<Krishna>getdate()</Krishna>
<Krishna>FROM inserted AS i</Krishna>
<Krishna>INNER JOIN repl_AuPair AS a ON a.AupairId = i.AupairId</Krishna>
<Krishna>WHERE i.openStatusCode = 'O'</Krishna>
<Krishna>AND i.proposalSubStatusCode = 'INT'</Krishna>
<Krishna>END</Krishna>
<Krishna>END</Krishna>
<Krishna></Krishna>
<Krishna>IF(@PHASE = 4)</Krishna>
<Krishna>BEGIN</Krishna>
<Krishna>IF( UPDATE( OpenStatusCode ) OR UPDATE ( ProposalSubstatusCode ) )</Krishna>
<Krishna>BEGIN</Krishna>
<Krishna>INSERT INTO repl.replicationUpdateControl (applicationId, [proposalAdded], [proposalId], modifyDate, phase)</Krishna>
<Krishna>SELECT DISTINCT a.My_Database_one_applicationID,</Krishna>
<Krishna>1,</Krishna>
<Krishna>ProposalId,</Krishna>
<Krishna>CURRENT_TIMESTAMP,</Krishna>
<Krishna>@PHASE</Krishna>
<Krishna>FROM inserted AS i</Krishna>
<Krishna>INNER JOIN repl_Aupair AS a ON a.AuPairID = i.AuPairId</Krishna>
<Krishna>WHERE i.OpenStatusCode = 'O'</Krishna>
<Krishna>AND i.ProposalSubstatusCode = 'INT'</Krishna>
<Krishna>END</Krishna>
<Krishna>END</Krishna>
<Krishna></Krishna>
<Krishna>END</Krishna>
<Krishna>GO</Krishna>
<Krishna>GO</Krishna>
<Krishna>use [DB_02]</Krishna>
<Krishna>GO</Krishna>
<Krishna>IF EXISTS (SELECT 'Radhe' FROM sys.triggers t wHERE t.[name] = 'tgr_repl_Transportation_UpdateInsert' AND CASE WHEN t.parent_id = 0 THEN t.parent_id ELSE object_id('[sub].[repl_Transportation]') END = t.parent_id )
</Krishna>
<Krishna>EXEC('BEGIN DROP TRIGGER [sub].[tgr_repl_Transportation_UpdateInsert] END') </Krishna>
<Krishna>GO</Krishna>
<Krishna></Krishna>
<Krishna></Krishna>
<Krishna>CREATE TRIGGER [sub].[tgr_repl_Transportation_UpdateInsert]</Krishna>
<Krishna>ON [sub].[repl_Transportation]</Krishna>
<Krishna>FOR UPDATE, INSERT</Krishna>
<Krishna>AS</Krishna>
<Krishna>BEGIN</Krishna>
<Krishna>SET NOCOUNT ON</Krishna>
<Krishna></Krishna>
<Krishna></Krishna>
<Krishna>/****** Departure Flight Information *******/</Krishna>
<Krishna>IF( UPDATE( PNR_NUMBERS ) )</Krishna>
<Krishna>BEGIN</Krishna>
<Krishna>INSERT INTO repl.replicationUpdateControl (applicationId, FlightChange, ModifyDate, phase)</Krishna>
<Krishna>SELECT a.My_Database_one_ApplicationId, 1, CURRENT_TIMESTAMP, 4</Krishna>
<Krishna>FROM inserted</Krishna>
<Krishna>INNER JOIN repl_Aupair AS a ON a.[IB_DEPARTURE_FLIGHT#] = inserted.[FLIGHT#]</Krishna>
<Krishna>INNER JOIN DB_02.app.application AS app ON app.applicationId = a.My_Database_one_applicationID</Krishna>
<Krishna>LEFT JOIN deleted AS old ON old.[FLIGHT#] = inserted.[FLIGHT#]</Krishna>
<Krishna>WHERE (inserted.PNR_NUMBERS IS NOT NULL AND inserted.PNR_NUMBERS &lt;&gt; '')</Krishna>
<Krishna>AND (old.PNR_NUMBERS IS NULL OR old.PNR_NUMBERS &lt;&gt; inserted.PNR_NUMBERS)</Krishna>
<Krishna>END</Krishna>
<Krishna></Krishna>
<Krishna></Krishna>
<Krishna>END</Krishna>
<Krishna>GO</Krishna>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment