org.jumpmind.symmetric.db.mssql.MsSqlTriggerTemplate Maven / Gradle / Ivy
/**
* Licensed to JumpMind Inc under one or more contributor
* license agreements. See the NOTICE file distributed
* with this work for additional information regarding
* copyright ownership. JumpMind Inc licenses this file
* to you under the GNU General Public License, version 3.0 (GPLv3)
* (the "License"); you may not use this file except in compliance
* with the License.
*
* You should have received a copy of the GNU General Public License,
* version 3.0 (GPLv3) along with this library; if not, see
* ();
sqlTemplates.put("insertTriggerTemplate" ,
"create trigger $(triggerName) on $(schemaName)$(tableName) after insert as " +
" begin " +
" declare @NCT int " +
" set @NCT = @@OPTIONS & 512 " +
" set nocount on " +
" declare @TransactionId varchar(1000) " +
" declare @DataRow varchar(max) " +
" $(declareNewKeyVariables) " +
" if (@@TRANCOUNT > 0) begin " +
" select @TransactionId = convert(VARCHAR(1000),transaction_id) from sys.dm_exec_requests where session_id=@@SPID and open_transaction_count > 0 " +
" end " +
" if ($(syncOnIncomingBatchCondition)) begin " +
" declare DataCursor cursor local for " +
" $(if:containsBlobClobColumns) " +
" select $(columns) $(newKeyNames) from inserted inner join $(schemaName)$(tableName) $(origTableAlias) on $(tableNewPrimaryKeyJoin) where $(syncOnInsertCondition)" +
" $(else:containsBlobClobColumns) " +
" select $(columns) $(newKeyNames) from inserted where $(syncOnInsertCondition) " +
" $(end:containsBlobClobColumns) " +
" open DataCursor " +
" fetch next from DataCursor into @DataRow $(newKeyVariables) " +
" while @@FETCH_STATUS = 0 begin " +
" insert into $(defaultCatalog)$(defaultSchema)$(prefixName)_data (table_name, event_type, trigger_hist_id, row_data, channel_id, transaction_id, source_node_id, external_data, create_time) " +
" values('$(targetTableName)','I', $(triggerHistoryId), @DataRow, '$(channelName)', $(txIdExpression), $(defaultCatalog)dbo.sym_node_disabled(), $(externalSelect), current_timestamp) " +
" fetch next from DataCursor into @DataRow $(newKeyVariables) " +
" end " +
" close DataCursor " +
" deallocate DataCursor " +
" end " +
" $(custom_on_insert_text) " +
" if (@NCT = 0) set nocount off " +
" end " );
sqlTemplates.put("updateTriggerTemplate" ,
"create trigger $(triggerName) on $(schemaName)$(tableName) after update as " +
" begin " +
" declare @NCT int " +
" set @NCT = @@OPTIONS & 512 " +
" set nocount on " +
" declare @TransactionId varchar(1000) " +
" declare @DataRow varchar(max) " +
" declare @OldPk varchar(2000) " +
" declare @OldDataRow varchar(max) " +
" $(declareOldKeyVariables) " +
" $(declareNewKeyVariables) " +
" if (@@TRANCOUNT > 0) begin " +
" select @TransactionId = convert(VARCHAR(1000),transaction_id) from sys.dm_exec_requests where session_id=@@SPID and open_transaction_count > 0 " +
" end " +
" if ($(syncOnIncomingBatchCondition)) begin " +
" declare DataCursor cursor local for " +
" $(if:containsBlobClobColumns) " +
" select $(columns), $(oldKeys), $(oldColumns) $(oldKeyNames) $(newKeyNames) from inserted inner join $(schemaName)$(tableName) $(origTableAlias) on $(tableNewPrimaryKeyJoin) inner join deleted on $(oldNewPrimaryKeyJoin) where $(syncOnUpdateCondition)" +
" $(else:containsBlobClobColumns) " +
" select $(columns), $(oldKeys), $(oldColumns) $(oldKeyNames) $(newKeyNames) from inserted inner join deleted on $(oldNewPrimaryKeyJoin) where $(syncOnUpdateCondition) " +
" $(end:containsBlobClobColumns) " +
" open DataCursor " +
" fetch next from DataCursor into @DataRow, @OldPk, @OldDataRow $(oldKeyVariables) $(newKeyVariables) " +
" while @@FETCH_STATUS = 0 begin " +
" insert into $(defaultCatalog)$(defaultSchema)$(prefixName)_data (table_name, event_type, trigger_hist_id, row_data, pk_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) " +
" values('$(targetTableName)','U', $(triggerHistoryId), @DataRow, @OldPk, @OldDataRow, '$(channelName)', $(txIdExpression), $(defaultCatalog)dbo.sym_node_disabled(), $(externalSelect), current_timestamp)" +
" fetch next from DataCursor into @DataRow, @OldPk, @OldDataRow $(oldKeyVariables) $(newKeyVariables) " +
" end " +
" close DataCursor " +
" deallocate DataCursor " +
" end " +
" $(custom_on_update_text) " +
" if (@NCT = 0) set nocount off " +
" end " );
sqlTemplates.put("updateHandleKeyUpdatesTriggerTemplate" ,
"create trigger $(triggerName) on $(schemaName)$(tableName) after update as " +
" begin " +
" declare @NCT int " +
" set @NCT = @@OPTIONS & 512 " +
" set nocount on " +
" declare @TransactionId varchar(1000) " +
" declare @OldPk varchar(2000) " +
" declare @OldDataRow varchar(max) " +
" declare @DataRow varchar(max) " +
" $(declareOldKeyVariables) " +
" $(declareNewKeyVariables) " +
" " +
" if (@@TRANCOUNT > 0) begin " +
" select @TransactionId = convert(VARCHAR(1000),transaction_id) from sys.dm_exec_requests where session_id=@@SPID and open_transaction_count > 0 " +
" end " +
" if ($(syncOnIncomingBatchCondition)) begin " +
" declare DeleteCursor cursor local for " +
" select $(oldKeys), $(oldColumns) $(oldKeyNames) from deleted where $(syncOnDeleteCondition) " +
" declare InsertCursor cursor local for " +
" $(if:containsBlobClobColumns) " +
" select $(columns) $(newKeyNames) from inserted inner join $(schemaName)$(tableName) $(origTableAlias) on $(tableNewPrimaryKeyJoin) where $(syncOnInsertCondition)" +
" $(else:containsBlobClobColumns) " +
" select $(columns) $(newKeyNames) from inserted where $(syncOnInsertCondition) " +
" $(end:containsBlobClobColumns) " +
" open DeleteCursor " +
" open InsertCursor " +
" fetch next from DeleteCursor into @OldPk, @OldDataRow $(oldKeyVariables) " +
" fetch next from InsertCursor into @DataRow $(newKeyVariables) " +
" while @@FETCH_STATUS = 0 begin " +
" insert into $(defaultCatalog)$(defaultSchema)$(prefixName)_data (table_name, event_type, trigger_hist_id, row_data, pk_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) " +
" values('$(targetTableName)','U', $(triggerHistoryId), @DataRow, @OldPk, @OldDataRow, '$(channelName)', $(txIdExpression), $(defaultCatalog)dbo.sym_node_disabled(), $(externalSelect), current_timestamp)" +
" fetch next from DeleteCursor into @OldPk, @OldDataRow $(oldKeyVariables) " +
" fetch next from InsertCursor into @DataRow $(newKeyVariables) " +
" end " +
" close DeleteCursor " +
" close InsertCursor " +
" deallocate DeleteCursor " +
" deallocate InsertCursor " +
" end " +
" $(custom_on_update_text) " +
" if (@NCT = 0) set nocount off " +
" end " );
sqlTemplates.put("deleteTriggerTemplate" ,
"create trigger $(triggerName) on $(schemaName)$(tableName) after delete as " +
" begin " +
" declare @NCT int " +
" set @NCT = @@OPTIONS & 512 " +
" set nocount on " +
" declare @TransactionId varchar(1000) " +
" declare @OldPk varchar(2000) " +
" declare @OldDataRow varchar(max) " +
" $(declareOldKeyVariables) " +
" if (@@TRANCOUNT > 0) begin " +
" select @TransactionId = convert(VARCHAR(1000),transaction_id) from sys.dm_exec_requests where session_id=@@SPID and open_transaction_count > 0 " +
" end " +
" if ($(syncOnIncomingBatchCondition)) begin " +
" declare DataCursor cursor local for " +
" select $(oldKeys), $(oldColumns) $(oldKeyNames) from deleted where $(syncOnDeleteCondition) " +
" open DataCursor " +
" fetch next from DataCursor into @OldPk, @OldDataRow $(oldKeyVariables) " +
" while @@FETCH_STATUS = 0 begin " +
" insert into $(defaultCatalog)$(defaultSchema)$(prefixName)_data (table_name, event_type, trigger_hist_id, pk_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) " +
" values('$(targetTableName)','D', $(triggerHistoryId), @OldPk, @OldDataRow, '$(channelName)', $(txIdExpression), $(defaultCatalog)dbo.sym_node_disabled(), $(externalSelect), current_timestamp)" +
" fetch next from DataCursor into @OldPk,@OldDataRow $(oldKeyVariables) " +
" end " +
" close DataCursor " +
" deallocate DataCursor " +
" end " +
" $(custom_on_delete_text) " +
" if (@NCT = 0) set nocount off " +
" end " );
sqlTemplates.put("initialLoadSqlTemplate" ,
"select $(columns) from $(schemaName)$(tableName) t where $(whereClause) " );
}
@Override
protected String replaceTemplateVariables(DataEventType dml, Trigger trigger,
TriggerHistory history, Channel channel, String tablePrefix, Table originalTable, Table table,
String defaultCatalog, String defaultSchema, String ddl) {
ddl = super.replaceTemplateVariables(dml, trigger, history, channel, tablePrefix, originalTable, table,
defaultCatalog, defaultSchema, ddl);
Column[] columns = table.getPrimaryKeyColumns();
ddl = FormatUtils.replace("declareOldKeyVariables",
buildKeyVariablesDeclare(columns, "old"), ddl);
ddl = FormatUtils.replace("declareNewKeyVariables",
buildKeyVariablesDeclare(columns, "new"), ddl);
return ddl;
}
@Override
protected boolean requiresEmptyLobTemplateForDeletes() {
return true;
}
}