I have the following trigger

CREATE TRIGGER trLogActivity
ON ClassesTest
FOR INSERT, UPDATE, DELETE
AS
insert into TblLogActiveTable (TableName, DeleteOrTruncateDateTime)
Values ('tablename', getdate())

What I want to do is to log the activity of the ClassesTest table. My question is how can I log the table name?

Hope my question is clear to you all.
Thanks.

Hi

You could hard code it. You have created a trigger on ClassesTest table therefore whenever that trigger is actioned then it will always be performed on that table.

e.g.
insert into TblLogActiveTable (TableName, DeleteOrTruncateDateTime)
Values ('ClassesTest', getdate())

Nickie

Additional question please. Can I know what kind of activity happen? Ex: Update or delete or insert have been done to the table that make the trigger fires?

Hi

There are 2 ways I can think of :-
1) Is to have separate triggers for each action e.g.
CREATE TRIGGER trLogActivity
ON ClassesTest
FOR INSERT
AS

CREATE TRIGGER trLogActivity
ON ClassesTest
FOR UPDATE
AS

CREATE TRIGGER trLogActivity
ON ClassesTest
FOR DELETE
AS

Then you know which has been fired and you can record the action.

2) If it is a requirement to have 1 trigger for INSERT, UPDATE and DELETE then you could perform a row count.

e.g. FOR INSERT you can check the inserted table - IF (select count(*) from inserted) > 0........record an INSERT action
FOR UPDATE check for count > 0 in both the inserted and deleted tables
FOR DELETE check for count > 0 in deleted table.

There maybe a better way to do this and possibly somebody else may have something to add to this.

Nickie

I will use your idea for now then. Thanks for your helps.