Triggers for audit trails
-
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
-
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
-
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.
-
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 -
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply