asked on

Replace comma with semicolon procedure problems with table update trigger

I am creating a procedure (replaceCommaTest.sql) that will change instances of ',' (comma) with ';' (semicolon).
I have a trigger on the table (see createAuditTrigger.sql).

When I run the procedure I get this message:
Msg 9420, Level 16, State 1, Procedure EnvironmentChangeAudit, Line 46
XML parsing: line 1, character 5340932, illegal xml character

If I delete the trigger and run the procedure, every works fine.

Also, if I do a single insert statement with the trigger in place, the trigger works fine.

Any idea what is going on and how to fix the problem
replaceCommaTest.sql
createAuditTrigger.sql

Anthony Perkins

INSERTED will be empty when you do a DELETE, so you may have to qualify that. Also,
you may need to add a root node to that Xml document in order to make it well-formed.

But in order to troubleshoot it, I would add print statements to get the following value before you attempt to assign it to @ActionXml:

SELECT  COALESCE((SELECT    *
                  FROM      deleted
                 FOR
                  XML AUTO
                 ), N'<deleted/>') + 
		COALESCE((SELECT   *
                FROM     inserted
                FOR
                XML AUTO
                ), N'<inserted/>');

Open in new window

Also, the following code is pointless as it will always have at least one:

  -- Get number of affected rows
  DECLARE @Count int = @@ROWCOUNT;


-- Make sure at least one row was actually affected
  IF (@Count > 0)

Open in new window

ste5an

Take a look at Tracking Data Changes.

SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dblankman

ASKER

I have discovered something about the trigger. Instead of creating a new record in the log for each up, it creates a single record for all of the records changed. For example if 4 records are changed in one update query, then there is only one record created instead of 4. Is there a way to modify the trigger so that it fires for each record changed rather than all of the records at once.

ste5an

Create a XML for each row instead of the entire table. Use a sub-query in the INSERT INTO with an SELECT on the virtual tables.

But this will imho be a conceptual break with your DmlActionLog table, cause you're splitting one action into 4 actions.

dblankman

ASKER

I am not sure what you mean? How would the trigger be modified to create XML for each row. I want the DmlActionLog table to have a row for each separate row that is being changed. If 20 records are changed, I would want 20 rows created in the DmlActionLog table.

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

Is there a way to modify the trigger so that it fires for each record changed rather than all of the records at once.
No. For performance reasons a TRIGGER in SQL Server fires once per statement and not once per row.