HI everyone I have a question I need to track users activity in the database.
ok so if I want to create a trigger on a databae i have called SCAIR, would I create a table inside of the database along with a trigger to track all the updates, deletes ...etc I want it to track users that enter data on the People_tbl all the child tables associated with it

would I put this table inside fo the SCAIR database to track along with the trigger


Code:
SET

 

ANSI_NULLS ON

GO

SET

 

QUOTED_IDENTIFIER ON

GO

SET

 

ANSI_PADDING ON

GO

CREATE

 

TABLE [dbo].[EvtLog](

[PostTime] [datetime]

NULL,

[LoginName] [varchar]

(100) NULL,

[EventType] [varchar]

(1000) NULL,

[TSQLCommand] [varchar]

(max) NULL,

[DataBaseName] [varchar]

(50) NULL 

)

 


ON [PRIMARY] 

GO


SET

 

ANSI_PADDING OFF





to track these tables




  

CREATE

 

TABLE [dbo].[People_tbl](

[Parent ID] [nvarchar]

(50) NOT NULL,

[Family ID] [nvarchar]

(50) NULL,

[StudentID] [nvarchar]

(50) NULL,

[Referral Location] [nvarchar]

(50) NULL,

[Referral Date] [nvarchar]

(50) NULL,

[Registration Date] [nvarchar]

(50) NULL,

[Student First Name] [nvarchar]

(50) NOT NULL,

[Student Last Name] [nvarchar]

(50) NULL,

[Student SS#] [nvarchar]

(50) NULL,

[Student ____] [nvarchar]

(50) NULL,

[Grade] [nvarchar]

(50) NULL,

[Age] [nvarchar]

(50) NULL,

[Student Date of Birth] [nvarchar]

(50) NULL,

[Event ID] [nvarchar]

(50) NULL,

[TANFreferral] [nvarchar]

(255) NULL,

[Enrollmentstatus] [nvarchar]

(255) NULL,

[EmailAddress] [nvarchar]

(50) NULL,

[ScannedDocuments] [nvarchar]

(50) NULL,

[ParentRelease] [nvarchar]

(50) NULL,

[StudentRelease] [nvarchar]

(50) NULL,

[DateReleased] [nvarchar]

(50) NULL,

[StudentAddress] [nvarchar]

(50) NULL,

[StudentZip] [nvarchar]

(50) NULL,

[StudentState] [nvarchar]

(50) NULL,

[StudentCity] [nvarchar]

(50) NULL,

[TribalAfflilation] [nvarchar]

(50) NULL,

[StateStudentID] [int]

NULL,

[Phone] [nvarchar]

(50) NULL,

[Message] [nvarchar]

(50) NULL,

[TanfStaffMakeReferal] [nvarchar]

(50) NULL,

[TypeOfParticipant] [nvarchar]

(50) NULL,

[RequiredHours] [nvarchar]

(50) NULL,

[SpecialInstructions] [nvarchar]

(50) NULL,

[PntID] [int]

NULL,

[Adult_Child] [nvarchar]

(50) NULL,

[State] [nvarchar]

(50) NULL,

[SSID] [nvarchar]

(50) NULL,

[PeopleID] [int]

IDENTITY(1,1) NOT NULL,

[TANF] [nvarchar]

(50) NULL,

[ServiceArea] [nvarchar]

(50) NULL,

[Weekly] [int]

NULL,

[Month] [int]

NULL,

[ReferralStatus] [nvarchar]

(50) NULL,

[MaritalStatus] [nvarchar]

(50) NULL,

[Vetran] [nvarchar]

(50) NULL,

[TanfSpouseName] [nvarchar]

(50) NULL,

[TanfSpouseSocial] [nvarchar]

(50) NULL,

[Pictures] [nvarchar]

(50) NULL CONSTRAINT [DF_People_tbl_Pictures] DEFAULT (N'Client Picture'),

 

CONSTRAINT [PK_People_tbl] PRIMARY KEY CLUSTERED 

(

[Parent ID]

ASC

)

 

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

)

 

ON [PRIMARY]

and the trigger

Code:
CREATE trigger [DDL_Structure_Audit]
on database
for alter_table, drop_table, create_table,alter_procedure,create_procedure,drop_procedure
as

--set nocount on
--    RAISERROR (N'You have not permission to perform this action.', -- Message text.
--                        15, -- Severity,
--                            1 -- State,
--                        )
--    Rollback transaction

declare @data xml
declare @vcapture    varchar(max)
declare @vUserName    varchar(25) 
declare @vEventType    varchar(50)
declare @vDatabaseName    varchar(50)
set @data = EVENTDATA()

Select     @vcapture = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)') ,
        @vUserName = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(25)') ,
        @vEventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)') ,
        @vDatabaseName = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(50)')



if PATINDEX('%PROCEDURE%', upper(@vcapture)) > 0 and upper(@vUserName) = 'JEFF'
    Begin
        Begin Transaction
        IF PATINDEX('%DROP%', upper(@vcapture)) > 0
        Begin
            RAISERROR (N'You have not permission to perform this action.', -- Message text.
                        15, -- Severity,
                            1 -- State,
                        )
            Rollback transaction    
        END    
        ELSE
        BEGIN
            INSERT INTO EventLogForDB.dbo.EvtLog  (PostTime ,LoginName , EventType ,TSQLCommand,DatabaseName )
            values (getdate(),@vUserName,@vEventType,@vcapture,@vDatabaseName)        
            Commit Transaction
        END
    END
ELSE
    INSERT INTO EventLogForDB.dbo.EvtLog  (PostTime ,LoginName , EventType ,TSQLCommand,DatabaseName )
    values (getdate(),@vUserName,@vEventType,@vcapture,@vDatabaseName)