Databases
StackOverflow2013
Postsdbo
POSQL Trigger firing incorrectly on a multi-row insert
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Trigger firing incorrectly on a multi-row insert
    text
    copied!<p>I have been wrestling with this one for a few days now. I have a table trigger in place that creates an audit record for each Insert, Update, or Delete action and places it in a corresponding audit table in a neighboring database. This new audit record contains the source tables primary key value, the action type, an XML snapshot of the record, current date, and current user. </p> <p>Each of the above listed pieces of information are assigned\calculated from the INSERTED \ DELETED tables and assigned to parameter values, which are then passed to a stored procedure that executes a piece of dynamic SQL to INSERT the new record. For the sake of this question, the dynamic SQL is a simple INSERT statement (shown below):</p> <pre><code> INSERT INTO [340bAudit].[aud].[TableName] ( RecordID ,ActionType ,xml_snapshot ,ModifiedDate ,ModifiedBy ) VALUES (@RecordPK ,@action ,@data ,GETDATE() ,SYSTEM_USER ) </code></pre> <p>This trigger appear to work fine as long as there is only a record being Updated, but once a situation occurs where multiple records are being updated in a single statement, I get this:</p> <blockquote> <p>Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, &lt;, &lt;= , >, >= or when the subquery is used as an expression. The statement has been terminated.</p> </blockquote> <p>The trigger looks like this:</p> <pre><code>ALTER TRIGGER [dbo].[RollOver_onUpdate] ON [dbo].[RollOver] AFTER INSERT, update, DELETE FOR EACH ROW AS BEGIN /******** Audit *******/ DECLARE @TableName varchar(50) ,@RecordPK varchar(10) ,@action char(1) ,@data xml SET @TableName = 'RollOver' SET @RecordPK = (SELECT DISTINCT RollOverID FROM INSERTED) SET @action = 'I'; -- Set Action to Insert by default. IF EXISTS (SELECT * FROM DELETED) BEGIN SET @action = CASE WHEN EXISTS (SELECT * FROM INSERTED) THEN 'U' -- Set Action to Updated. ELSE 'D' -- Set Action to Deleted. END END SET @data = CASE WHEN @action &lt;&gt; 'D' THEN (SELECT'&lt;rows&gt;' + (SELECT * FROM INSERTED i FOR xml PATH) + '&lt;/rows&gt;') ELSE (SELECT'&lt;rows&gt;' + (SELECT * FROM DELETED d FOR xml PATH) + '&lt;/rows&gt;') END --Execute Audit Record Creation EXECUTE sp_CreateAuditRecord @Table = @TableName ,@RecordID = @RecordPK ,@ActionType = @action ,@XML = @data END </code></pre> <p>Can I alter this trigger to execute by row in this case? And if not, where do I go from here? </p> <p>I would very much like to retain the flexibility I have with the dyanamic SQL, as every table in my database uses this same audit logic and this is the first and only table that appears to have issue, which I believe is due to the 'use' of this table. </p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload