March 17, 2010 at 9:24 am
Hi,
I want to do the following is an aproximate log of database changes, any suggestion is welcome.
I want to implement this: http://www.4guysfromrolla.com/webtech/041807-1.shtml
code:
CREATE TRIGGER td_EmployeesAudit ON dbo.Employees
FOR DELETE
AS
INSERT INTO EmployeesHistory(ChangedTable, ChangedBy, ChangedOn, Deleted, Changes)
How do I to select the changes in XML format and insert it in the database? same as
ConsolidatedHistoryTable
ChangedTable ChangedBy ChangedOn Deleted Changes
Products Scott 2007-04-18 14:34:04 0 <Product>
<ProductID>1</ProductID>
<ProductName>Acme Tea</ProductName>
<UnitPrice>$4.50</UnitPrice>
</Product>
Products Scott 2007-04-19 11:07:13 0 <Product>
<ProductID>1</ProductID>
<ProductName>Acme Tea</ProductName>
<UnitPrice>$3.95</UnitPrice>
</Product>
Products Scott 2007-04-30 09:52:40 1 <Product>
<ProductID>1</ProductID>
<ProductName>Acme Tea</ProductName>
<UnitPrice>$3.95</UnitPrice>
</Product>
thank you for reading.
March 17, 2010 at 9:32 am
Hi,
you need something like this:
CREATE TRIGGER td_EmployeesAudit ON dbo.Employees
FOR INSERT, UPDATE, DELETE
AS
INSERT INTO EmployeesHistory (ChangedTable, ChangedBy, ChangedOn, Deleted, Changes)
SELECT 'Employees', USER, getdate(), 1, <XML_Column_Name_In_Table_Employees>
FROM DELETED
INSERT INTO EmployeesHistory (ChangedTable, ChangedBy, ChangedOn, Deleted, Changes)
SELECT 'Employees', USER, getdate(), 0, <XML_Column_Name_In_Table_Employees>
FROM INSERTED
GO
As you can see, you can use the same trigger for both insert, update and delete and thus only need one trigger per table. The data deleted and/or inserted is found in the special "in-trigger tables" DELETED and INSERTED. An update will look as a delete and an insert done in one step.
Good luck!
/Markus
March 17, 2010 at 10:11 am
thank you very much
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply