Audit table

Hi All
I'm looking for some code which create audit table and build triggers which
register all changes on table.
Maybe someone from you know where can I find it?
Regards
MichalChapter 6: Audit Logging in the book "Transact-SQL Cookbook" discuses audit
logging using triggers in detail:
http://vyaskn.tripod.com/transact-sql_cookbook.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Michal" <michalb77@.gazeta.pl> wrote in message
news:dfk13j$5hk$1@.atlantis.news.tpi.pl...
Hi All
I'm looking for some code which create audit table and build triggers which
register all changes on table.
Maybe someone from you know where can I find it?
Regards
Michal|||Hi,
Copy the Order table structure to AuditOrder table.
Select * into AuditOrders from Orders where 1= 2
Auditing trigger:-
--
CREATE TRIGGER Audittriiger_order ON Order
FOR INSERT, UPDATE, DELETE
AS
if @.@.rowcount = 0 return
DECLARE @.action char (7), @.inserted bit, @.deleted bit
set @.deleted = case when exists (select * from deleted) then 1 else 0 end
set @.inserted = case when exists (select * from inserted) then 1 else 0 end
set @.action = case when @.deleted = 1 and @.inserted = 1
then 'Before'
else 'DELETE'
end
if @.deleted = 1
begin
INSERT INTO AuditOrders ( operation, orderid, Order_name,
Order_qty,Order_date)
SELECT @.action AS F1,
RTRIM(SYSTEM_USER) AS F2,
[Order].Orderid,
[Order].Order_ name
FROM Deleted INNER JOIN Order ON Deleted.Orderid = Order.OrderID end
set @.action = case when @.deleted = 1 and @.inserted = 1
then 'After'
else 'INSERT'
end
if @.inserted = 1
begin
INSERT INTO AuditOrders( operation, orderid, Order_name,
Order_qty,Order_date)
SELECT @.action AS F1,
RTRIM(SYSTEM_USER) AS F2,
[Order].Orderid,
[Order].Order_ name
FROM Inserted INNER JOIN Order ON Inserted.Orderid = Order.OrderID
end
"Michal" <michalb77@.gazeta.pl> wrote in message
news:dfk13j$5hk$1@.atlantis.news.tpi.pl...
> Hi All
> I'm looking for some code which create audit table and build triggers
> which register all changes on table.
> Maybe someone from you know where can I find it?
> Regards
> Michal
>|||Michael may also want to have an additional date/time column in AuditOrder
that stores when the change took place (getdate) and perhaps another column
to store the system username (system_user) of who initiated the change.
"Hari Pra" <hari_pra_k@.hotmail.com> wrote in message
news:e5ZpZ5tsFHA.1168@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Copy the Order table structure to AuditOrder table.
> Select * into AuditOrders from Orders where 1= 2
> Auditing trigger:-
> --
> CREATE TRIGGER Audittriiger_order ON Order
> FOR INSERT, UPDATE, DELETE
> AS
> if @.@.rowcount = 0 return
> DECLARE @.action char (7), @.inserted bit, @.deleted bit
> set @.deleted = case when exists (select * from deleted) then 1 else 0 end
> set @.inserted = case when exists (select * from inserted) then 1 else 0
> end
> set @.action = case when @.deleted = 1 and @.inserted = 1
> then 'Before'
> else 'DELETE'
> end
> if @.deleted = 1
> begin
> INSERT INTO AuditOrders ( operation, orderid, Order_name,
> Order_qty,Order_date)
> SELECT @.action AS F1,
> RTRIM(SYSTEM_USER) AS F2,
> [Order].Orderid,
> [Order].Order_ name
> FROM Deleted INNER JOIN Order ON Deleted.Orderid = Order.OrderID end
>
> set @.action = case when @.deleted = 1 and @.inserted = 1
> then 'After'
> else 'INSERT'
> end
> if @.inserted = 1
> begin
> INSERT INTO AuditOrders( operation, orderid, Order_name,
> Order_qty,Order_date)
> SELECT @.action AS F1,
> RTRIM(SYSTEM_USER) AS F2,
> [Order].Orderid,
> [Order].Order_ name
> FROM Inserted INNER JOIN Order ON Inserted.Orderid = Order.OrderID
> end
>
> "Michal" <michalb77@.gazeta.pl> wrote in message
> news:dfk13j$5hk$1@.atlantis.news.tpi.pl...
>