November 29, 2007 at 8:07 pm
I am generating triggers to enable a cross database synchronization mechanism (see symmetricds.org).
I am very close to having a SQL Server 2005 dialect implemented, but have run into a stumbling block with text and image columns.
Because text and image columns aren't available in the inserted table in the trigger, I need to go back to the original table to get the data.
There are two tables the trigger inserts into: sym_data (records the data in csv format) and sym_data_event (records who the data is being sent to). I need to somehow get 'text' data in it's proper place in the csv string (which is inserted into row_data).
What would be the most efficient way to do this? The following is an example generated trigger. Please feel free to find bugs with the approach/code and make suggestions. 🙂
create trigger ON_I_TO_TST_TRGRS_TBL on CHENSON.TEST_TRIGGERS_TABLE after insert as
begin
declare @TransactionId varchar(1000)
declare @SyncEnabled varbinary(128)
if (@@TRANCOUNT > 0) begin
execute sp_getbindtoken @TransactionId output;
end
select @SyncEnabled = context_info from master.dbo.sysprocesses where spid=@@SPID
if (@SyncEnabled <> 0x1) begin
insert into chenson.sym_data (table_name, channel_id, event_type, trigger_hist_id, transaction_id, row_data, create_time)
(select 'TEST_TRIGGERS_TABLE','testchannel','I', 1, @TransactionId,
coalesce(cast(inserted.id as char), '') +','+
case when inserted.string_One_Value is null then '' else '"' + replace(replace(inserted.string_One_Value,'\','\\'),'"','\"') + '"' end +','+
case when inserted.string_Two_Value is null then '' else '"' + replace(replace(inserted.string_Two_Value,'\','\\'),'"','\"') + '"' end +','+
-- text column goes here
','+
case when inserted.time_Value is null then '' else ('"' + convert(varchar,inserted.time_Value,121) + '"') end +','+
case when inserted.date_Value is null then '' else ('"' + convert(varchar,inserted.date_Value,121) + '"') end +','+
coalesce(cast(inserted.boolean_Value as char), '') +','+
coalesce(cast(inserted.bigInt_Value as char), '') +','+
coalesce(cast(inserted.decimal_Value as char), '') , current_timestamp from inserted where 1=1);
if (@@ROWCOUNT > 0) begin
insert into chenson.sym_data_event (node_id, data_id) (select node_id, @@IDENTITY from chenson.sym_node c where
c.node_group_id='test-node-group' and c.sync_enabled=1 );
end
end
end
December 2, 2007 at 12:20 pm
I ended up detecting if one of the columns is a CLOB (text) or a BLOB (image) and joined in the original table to select the CLOB or BLOB data.
December 2, 2007 at 12:32 pm
I think that's the best approach and thanks for the update.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply