I'm using SQLXML 3.0 with VB.net to upload xml files to SQL Express. One XML uploads to multiple SQL tables. One table has a trigger to update fields in one of the other tables.
Unfortunately, the trigger won't fire. The trigger will fire with a simple T-SQL INSERT script but won't using SQLXML.
The UPDATE statement works fine.
Multi-row consideration is not an issue because the table recieves just one record during an xml upload
Even so, if the trigger was edited to account for multiple rows if @@ROWCOUNT <> 1, it still wouldn't fire. Even if the UPDATE statement was edited to deliberately fail, there is no response from the trigger.
Changing INSTEAD OF to FOR or AFTER has no affect either.
SQLXML 4.0 has a FireTriggers property that doesn't exist in 3.0. Even if I'm able to get version 4.0, which I understand is only available in SQL 2005, would this solve the problem?
Is there a similar property or method that can force a trigger to fire when using version 3.0 in vb.net?
Unfortunately, the trigger won't fire. The trigger will fire with a simple T-SQL INSERT script but won't using SQLXML.
Code:
ALTER TRIGGER [dbo_TEMP].[iAccountAreaHeader] ON [dbo_TEMP].[AccountArea.Header]
INSTEAD OF INSERT AS
BEGIN
DECLARE @Count int;
SET @Count = @@ROWCOUNT;
BEGIN
UPDATE [dbo_TEMP].[AccountArea.Area]
SET AA_RunID =
(SELECT AH_Run FROM dbo_TEMP.[AccountArea.Header] WHERE (AH_Run > 0))
END
END;
The UPDATE statement works fine.
Multi-row consideration is not an issue because the table recieves just one record during an xml upload
Even so, if the trigger was edited to account for multiple rows if @@ROWCOUNT <> 1, it still wouldn't fire. Even if the UPDATE statement was edited to deliberately fail, there is no response from the trigger.
Changing INSTEAD OF to FOR or AFTER has no affect either.
SQLXML 4.0 has a FireTriggers property that doesn't exist in 3.0. Even if I'm able to get version 4.0, which I understand is only available in SQL 2005, would this solve the problem?
Is there a similar property or method that can force a trigger to fire when using version 3.0 in vb.net?