Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Trigger won't fire using SQLXML 3.0 when INSERT into table

Status
Not open for further replies.

Jimdeb03

IS-IT--Management
Dec 10, 2002
89
0
0
US
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.
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?
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top