The Insert statement looks like this:

DECLARE @cSyBaseSQL nvarchar(4000)
DECLARE @cSQL nvarchar(4000)
	
declare @tLastUpdate datetime = (SELECT COALESCE(MAX(Created), Cast(Cast(getDate() as Date) as DateTime)) From dbo.EventsLog)

select (@tLastUpdate)

declare @cSource varchar(10) = (SELECT cast(PK as varchar(10)) FROM dbo.Sources WHERE Source = 'SomeSource')


SET @cSyBaseSQL =
	'
		SELECT		CS.equip_id as equip_id,
					CS.created As created_llt,
					CS.set_flag set_flag,
					str(CS.event + (256 * set_flag)) as event_id
			FROM	syBaseStatus CS
			JOIN	syBaseCodes SC on CS.event + (256 * CS.set_flag) = SC.event
			where	CS.created >= "' + convert(char(19), @tLastUpdate, 20) + '"				
	'
-- This next line I put and remove for testing and it does not seem to make a difference
--			order	by 1, 2 asc

SET @cSQL = 
	'
		INSERT INTO dbo.EventsLog
			(
				OperationalDay,
				fkDevices,
				Created,
				fkEventDefinitions,
				fkSources,
				setFlag
			) 
		SELECT
					dbo.getOperationDate(Source.created_llt) as OperationDay,
					Devices.PK as fkDevices,
					Source.created_llt,
					ED.PK as fkEventDefinitions,
					' + @cSource + ' as fkSources,
					Source.set_flag
			FROM	OpenQuery(SYBASEDB, ''' + @cSyBaseSQL + ''') Source
			JOIN	dbo.Devices ON Source.Equip_ID = Devices.EquipID and Devices.fkSources = ' + @cSource + '
			JOIN	dbo.EventDefinitions ED ON ED.EventID = Source.Event_id and ED.fkSources = ' + @cSource + '
			ORDER	By Source.created_llt
	'

EXEC (@cSQL)

dbo.EventsLog has an insert trigger like this

/****** Object:  Trigger [dbo].[InsertFaults]    Script Date: 09/28/2009 11:54:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		RAN02
-- Create date: 2009-09-26
-- Description:	Trigger to insert a record in the FaultsLog table when the Event being logged is a Fault (the flag isFault of EventsDefinition is True)
-- =============================================
ALTER TRIGGER [dbo].[InsertFaults] 
   ON  [dbo].[EventsLog] 
   AFTER INSERT
AS 
BEGIN
	insert into dbo.FaultsLog
		(
			fkFaultLogStatuses,
			fkEventsLog,
			fkFaultLogModes,
			Title,
			[Description],
			fkLogTypes,
			fkSources,
			fkDevices,
			fkStations,
			Created,
			fkContactGroups,
			fkFaultsLog
		)
	select		(select PK from FaultLogStatuses where status = case i.SetFlag when 1 then 'Open' else 'Closed' end),
			i.PK,
			(select PK from FaultLogModes where Mode = 'Auto'),
			ED.[Description],
			'This is an automated entry',
			(select PK from LogTypes where LogType = case i.SetFlag when 1 then 'Creation' else 'Status Change' end),
			i.fkSources,
			i.fkDevices,
			(select Stations.PK from Devices join Stations on Stations.PK = Devices.fkStations where Devices.PK = i.fkDevices),
			getDate(),
			ED.fkContactGroups,
			case i.SetFlag when 1 then null else (dbo.getClearedEventFaultsLogPK(dbo.getClearedEvent(ED.PK), i.fkDevices)) end
		from	inserted i
		join	dbo.EventDefinitions ED on i.fkEventDefinitions = ED.PK and ED.isFault = 1
		order	by i.Created
END

The only field of interest is fkFaultsLog which should be the parent record of an opened fault (when it is being closed which is determined by i.SetFlag = 0) the two stored prcedures getClearedEventFaultsLogPK (Horrible name I came up on a Sunday when I was very tired) and getClearedEvent() both seem to work fine when I ran them from outside, but they always return null from the trigger, that is why I thought it was a problem with the commit