Hi, Naomi
I use the following: I create a database AUDIT that has the same tables the one you want to audit , and some extra fields in each table : type of change, date of change and who changed. With a trigger like the above you can select the action to audit and if you only want to audit for a certain period of time you can disable the trigger.
CREATE TRIGGER [dbo].[TRG_audit_produto]
ON [dbo].[produto]
FOR DELETE,UPDATE
AS
DECLARE @ACT CHAR(6)
DECLARE @DEL BIT
DECLARE @INS BIT
DECLARE @SQLSTRING VARCHAR(2000)
SET @DEL = 0
SET @INS = 0
IF EXISTS (SELECT TOP 1 1 FROM DELETED) SET @DEL=1
IF EXISTS (SELECT TOP 1 1 FROM INSERTED) SET @INS = 1
IF @INS = 1 AND @DEL = 1 SET @ACT = 'UPDATE'
IF @INS = 1 AND @DEL = 0 SET @ACT = 'INSERT'
IF @DEL = 1 AND @INS = 0 SET @ACT = 'DELETE'
IF @INS = 0 AND @DEL = 0 RETURN
IF @ACT = 'INSERT' INSERT [AUDITL4].[DBO].[AUDIT_PRODUTO] SELECT *,'INSERT' ,GETDATE(),HOST_NAME() FROM INSERTED
IF @ACT = 'DELETE' INSERT [AUDITL4].[DBO].[AUDIT_PRODUTO] SELECT *,'DELETE' ,GETDATE(),HOST_NAME() FROM DELETED
IF @ACT = 'UPDATE' INSERT [AUDITL4].[DBO].[AUDIT_PRODUTO] SELECT *,'UPDATE' ,GETDATE(),HOST_NAME() FROM DELETED
Hope it helps