Solved

Replace comma with semicolon procedure problems with table update trigger

Posted on 2014-04-20
8
651 Views
Last Modified: 2014-04-22
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
0
Comment
Question by:dblankman
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40011407
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

0
 
LVL 33

Expert Comment

by:ste5an
ID: 40011476
Take a look at Tracking Data Changes.
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 250 total points
ID: 40012586
Most likely the data has one or more of the characters "&", "<" and/or ">".

The only really effective way around that might be to gen the full column list and REPLACE those characters where appropriate.  

You could also dynamically generate the list at run time, but that will slow down the trigger which you don't want to do of course.  Triggers should be as efficient as possible because of their critical path.

Along those lines, we can touch up other aspects of the trigger, like so:


CREATE TRIGGER dbo.EnvironmentChangeAudit
ON dbo.environment
AFTER  UPDATE, DELETE
NOT FOR REPLICATION
AS
SET NOCOUNT ON;
DECLARE @deleted_found bit
DECLARE @inserted_found bit

IF EXISTS(SELECT 1 FROM deleted)
    SET @deleted_found = 1;
ELSE
    SET @deleted_found = 0;

IF EXISTS(SELECT 1 FROM inserted)
    SET @inserted_found = 1;
ELSE
    SET @inserted_found = 0;
   
-- Make sure at least one row was actually affected
IF @deleted_found = 1
OR @inserted_found = 1
BEGIN

    DECLARE @ActionType char(6)
    DECLARE @ActionXml xml;

    IF @deleted_found > 0
       IF @inserted_found > 0
           SET @ActionType = 'update'
       ELSE
           SET @ActionType = 'delete'
    ELSE
        SET @ActionType = 'insert'

    -- Use FOR XML AUTO to retrieve before and after snapshots of the changed
    -- data in XML format
    SELECT @ActionXml = COALESCE
    (
      (
        SELECT *
        FROM deleted
        FOR XML AUTO
      ), N'<deleted/>'
    ) + COALESCE
    (
      (
        SELECT *
        FROM inserted

                              
FOR XML AUTO
      ), N'<inserted/>'
    );

    -- Insert a row for the logged action in the audit logging table
    INSERT INTO dbo.DmlActionLog
    (
      SchemaName,
      TableName,
      ActionType,
      ActionXml,
      UserName,
      Spid,
      ActionDateTime
    )
    SELECT
      OBJECT_SCHEMA_NAME(@@PROCID, DB_ID()),
      OBJECT_NAME(t.parent_id, DB_ID()),
      @ActionType,
      @ActionXml,
      USER_NAME(),
      @@SPID,
      GETDATE()
    FROM sys.triggers t
    WHERE t.object_id = @@PROCID;

END;
GO
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:dblankman
ID: 40014401
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.
0
 
LVL 33

Expert Comment

by:ste5an
ID: 40014420
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.
0
 

Author Comment

by:dblankman
ID: 40014425
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.
0
 
LVL 33

Accepted Solution

by:
ste5an earned 250 total points
ID: 40014481
Caveat: You have here a semantic problem. One DML action is one UPDATE. One UPDATE can affect more than one rows.

E.g. based on the previous solutions, something like

INSERT  INTO dbo.DmlActionLog
        ( SchemaName ,
          TableName ,
          ActionType ,
          ActionXml ,
          UserName ,
          Spid ,
          ActionDateTime
        )
        SELECT  OBJECT_SCHEMA_NAME(@@PROCID, DB_ID()) ,
		OBJECT_NAME(t.parent_id, DB_ID()) ,
		@ActionType ,
		COALESCE((	SELECT   DI.*
					FROM     DELETED DI
					WHERE    DI.primaryKeyColumns = D.primaryKeyColumns
					FOR XML AUTO
		), N'<deleted/>') + 
		COALESCE((	SELECT  II.*
					FROM    INSERTED II
					WHERE   II.primaryKeyColumns = D.primaryKeyColumns
					FOR XML AUTO
					), N'<inserted/>') ,
		USER_NAME() ,
		@@SPID ,
		GETDATE()
        FROM    sys.triggers t
                CROSS JOIN DELETED D
                LEFT JOIN INSERTED I ON D.primaryKeyColumns = I.primaryKeyColumns
        WHERE   t.object_id = @@PROCID;

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40014724
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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Managing Columnstore Indexes 2 30
SQL Help 27 45
SQL Server 2012 r2 - Varible Table 3 24
SQL Improvement  ( Speed) 14 28
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question