Triggers for audit trails

Question

Post reply

Triggers for audit trails

Charlottecb

Hall of Fame

Points: 3932
More actions
May 16, 2005 at 3:50 am

#89001

Hi all.
SQLServer 2000
I am using triggers for an audit trail. I have one for insert and one for update and delete.
The autit table is exactly the sam as the table to be audited except it has an extra id field (auditID) and an strAction field to hold either Insert, Delete or Update so it is easier to view the log.
The insert trigger works fine. The update and delete are sharing one trigger just becuase there is lee to maintain that way. How do I know within this trigger if the user has done an update or a delete so I can insert the either 'Update' or 'Delete') in the strAction field.
My update / delete trigger looks as follows: -
CREATE TRIGGER contact_update_delete_audit ON dbo.tbl1Contact
FOR UPDATE, DELETE
AS
Declare @id int
Declare @type int
Declare @date datetime
Declare changed_cursor Cursor For Select intContactID, intRecordType, dtDueDate From Inserted
Open changed_cursor
Fetch Next From changed_cursor InTo @id, @type, @date -- Perform the first fetch.
WHILE @@FETCH_STATUS = 0    -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
Begin        -- This is executed as long as the previous fetch succeeds.
    If @type = 2        -- Contact Record so copy due date to done date --
      Begin
        Update tbl1Contact
        Set dtDoneDate = @Date
        From Inserted
        where tbl1Contact.intContactID = @id
      End
    Fetch Next From changed_cursor InTo @id, @type, @date
End
Close changed_cursor
Deallocate changed_cursor
-- Add before image of rows (strRemarks, strEmailPrintHistory not audited (text columns) --
Insert tbl1Contact_audit
(intContactID,
intRelationshipID,
dtDueDate,
dtDoneDate,
intContactType,
intContactMethod,
intStaffID,
strBriefDesc,
intPreviousContactID,
intDaysWait,
intLetterID,
intRecordType,
intOrder,
intAddedBy,
intViewByAll,
intTaskID,
intPreviousTaskID,
strChangedByUser,
dtChangedDate)
Select intContactID,
intRelationshipID,
dtDueDate,
dtDoneDate,
intContactType,
intContactMethod,
intStaffID,
strBriefDesc,
intPreviousContactID,
intDaysWait,
intLetterID,
intRecordType,
intOrder,
intAddedBy,
intViewByAll,
intTaskID,
intPreviousTaskID,
User,
GetDate()
From Deleted
My insert trigger (for reference) is as follows: -
CREATE TRIGGER contact_insert_update_done_date ON dbo.tbl1Contact
FOR INSERT
AS
If @@RowCount = 0 Return
declare @type int
set @type = (select intRecordType from Inserted)
If @type = 2 -- Contact Record so copy due date to done date --
Begin
    Update tbl1Contact
    Set dtDoneDate = Inserted.dtDueDate
    From Inserted
    where tbl1Contact.intContactID = Inserted.intContactID
End

Thanks.
CCB

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Andy Painter SSCommitted Points: 1821 More actions · Answer 1

Can't you just use what's in the Inserted/Deleted tables?

If it's in both it's an update. If it's only in Deleted, it's a delete

Andy

Ste_P SSCarpal Tunnel Points: 4430 More actions · Answer 2

Hi All,

Been off for a week.......

Is this thread still live?

Have fun

Steve

We need men who can dream of things that never were.

noeld SSC Guru Points: 96590 More actions · Answer 3

Well if it is alive here you go:

declare @ICnt int , @DCnt int

select @ICnt = count(*) from inserted

select @DCnt = count(*) from deleted

if @ICnt > 0 and @DCnt > 0

begin

-- Update code Here

end

Else If @ICnt > 0 and @DCnt = 0

begin

-- Insert code Here

end

Else If @ICnt = 0 and @DCnt > 0

begin

-- Delete code Here

end

* Noel

Ray M SSC-Insane Points: 21093 More actions · Answer 4

This will work, but I would explicitly define the Update, and Delete triggers separately.

Then you know the action by the type of trigger that is firing. You are not saving anything by combining into 1 trigger.