-- My content table
CREATE TABLE [Content](
	[ContentGuid] [uniqueidentifier] NOT NULL PRIMARY KEY DEFAULT (newid()),
	[Data] [nvarchar](4000) NOT NULL,
	[ChangedBy] [nchar](10) NOT NULL,
	[MaybeNull] [int] NULL
)

-- My history table
CREATE TABLE [History](
	[ChangedTable] [nvarchar](50) NOT NULL,
	[ReferenceGuid] [uniqueidentifier] NOT NULL,
	[ChangedBy] [nchar](10) NOT NULL,
	[ChangedOn] [datetime] NOT NULL DEFAULT (getutcdate()),
	[IsDelete] [bit] NOT NULL DEFAULT ((0)),
	[Changes] [xml] NOT NULL
) ON [PRIMARY]

-- My insert/update trigger
CREATE TRIGGER [RecordChangeOnInsertUpdate]
   ON [Content]
   AFTER INSERT,UPDATE
AS 
BEGIN
	SET NOCOUNT ON;

	DECLARE @Changes xml
	SET @Changes = (select * from [inserted] for xml raw ('Content'), elements xsinil)

	INSERT INTO [History]
			   ([ChangedTable]
			   ,[ReferenceGuid]
			   ,[ChangedBy]
			   ,[ChangedOn]
			   ,[IsDelete]
			   ,[Changes])
	SELECT 'Content', ContentGuid, ChangedBy, getutcdate(), 0, @Changes
	FROM [inserted]
END

-- My delete trigger
CREATE TRIGGER [RecordChangeOnDelete]
   ON [Content]
   AFTER DELETE
AS 
BEGIN
	SET NOCOUNT ON;

	DECLARE @Changes xml
	SET @Changes = (select * from [deleted] for xml raw ('Content'), elements xsinil)

	INSERT INTO [History]
			   ([ChangedTable]
			   ,[ReferenceGuid]
			   ,[ChangedBy]
			   ,[ChangedOn]
			   ,[IsDelete]
			   ,[Changes])
	SELECT 'Content', ContentGuid, ChangedBy, getutcdate(), 1, @Changes
	FROM [deleted]
END

quote:

---

Can the inserted/deleted table change between...

---

quote:

---

If I were to (hypothetically) perform a "UPDATE [Content] SET [Data] = 'something'", not only is my update trigger called once for each row updated....

---

quote:

---

...but the XML added to the inserted row in the history table ([History].[Changes]) represent all the rows updated in the batch update.

---

quote:

---

False ... you trigger will run once

---

quote:

---

True ... you will insert one row into [History]. If you want one row in [History] for each row in INSERTED/DELETED, you'll need to either loop through INSERTED/DELETED (weak) or write you insert into [History] in a set based manner (the true path).

---

update [Content] set [Data] = 'something'

quote:

---

Originally posted by Page47

If you have 10 rows in [Content] and you run:

update [Content] set [Data] = 'something'

...your trigger will run 1 time, not 10 times.

---

update [Content] set [Data] = 'something'

WITH ChangedContent (ContentGuid, Changes)
AS 
(
... this is where I get stuck. I need to call 
(select * from [inserted] for xml raw ('Content'), elements xsinil) 
for each row in [inserted] such that my temp ChangedContent table 
contains ContentGuid and associated xml representation of the row
)
INSERT INTO [History]
([ChangedTable]
,[ReferenceGuid]
,[ChangedBy]
,[ChangedOn]
,[IsDelete]
,[Changes])
SELECT 'Content', ContentGuid, ChangedBy, getutcdate(), 0, @Changes
FROM [inserted] JOIN ChangedContent ON [inserted].ContentGuid == ChangedContent.ContentGuid

with ChangeTable (TableName, ContentGuid, ChangedBy, ChangedOn, IsDelete, Changes)
as (select 'Content', c2.ContentGuid, c2.ChangedBy, getutcdate(), 0, (select * from [inserted] c1 where c1.ContentGuid = c2.ContentGuid for xml raw ('Content'), elements xsinil) as Changes
	from [inserted] c2
	)
insert into [History]
	   ([ChangedTable]
	   ,[ReferenceGuid]
	   ,[ChangedBy]
	   ,[ChangedOn]
	   ,[IsDelete]
	   ,[Changes])
select TableName, ContentGuid, ChangedBy, ChangedOn, Isdelete, Changes
from ChangeTable

SELECT 
	c2.ContentGuid,
	(SELECT * FROM [Content] c1 WHERE c1.ContentGuid = c2.ContentGuid FOR XML RAW ('Content'), ELEMENTS XSINIL)
FROM [Content] c2;