Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: monitoring user activity

    Hi all I have a trigger on the People_tbl but I dont know allot about triggers. this is the trigger I have. I dont know allot about Triggers but I want to monitor the [parent ID] column and the [Student First Name] and [Student Last Name]

    Code:
    CREATE TRIGGER trg_People_tbl ON dbo.People_tbl
    FOR INSERT, DELETE, UPDATE
    AS
    BEGIN
    
    	INSERT INTO tUserActivity (Name, AffectedPeopleID, BeforeStudentID, AfterStudentID)
    	SELECT SUSER_SNAME() AS Name, ISNULL(D.PeopleID, I.PeopleID) AS AffectedPeopleID,
    			D.StudentID AS BeforeStudentID, I.StudentID AS AfterStudentID
    	FROM INSERTED AS I
    	FULL JOIN DELETED AS D ON I.PeopleID = D.PeopleID
    	
    END
    
    GO
    its on this table

    Code:
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    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) NULL,
    	[Student Last Name] [nvarchar](50) NULL,
    	[Student SS#] [nvarchar](50) NULL,
    	[Student Sex] [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](100) NULL,
    	[ScannedDocuments] [nvarchar](50) NULL,
    	[ParentRelease] [nvarchar](50) NULL,
    	[StudentRelease] [nvarchar](50) NULL,
    	[DateReleased] [nvarchar](50) NULL,
    	[TribalAfflilation] [nvarchar](50) NULL,
    	[StateStudentID] [int] 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,
    	[ReferralDate] [datetime] NULL,
    	[FamilyHrID] [int] NULL,
    	[Pictures] [image] NULL,
    	[ScairCaseWorker] [nvarchar](50) NULL,
    	[ScairHoursOnly] [bit] NULL CONSTRAINT [DF_People_tbl_ScairHoursOnly]  DEFAULT ('0'),
    	[FamilyCodes] [nvarchar](50) NULL,
    	[StateParticipant] [nvarchar](50) NULL,
    	[StateActive_Inactive] [nvarchar](50) NULL,
    	[Counselor] [nvarchar](50) NULL,
    	[TtlCountableMnths] [nvarchar](50) NULL,
    	[Manzanita] [nvarchar](50) NULL,
     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] TEXTIMAGE_ON [PRIMARY]
    
    GO
    Last edited by desireemm; 09-25-10 at 18:18.

  2. #2
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    nevermind got it all taken care of accept I need to incorporate the time and date also do I need a seperate table to record the deletes??

    Code:
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER TRIGGER [dbo].[trg_People_tbl] ON [dbo].[People_tbl]
    FOR INSERT, DELETE, UPDATE
    AS
    BEGIN
    
    	INSERT INTO tUserActivity (Name, AffectedPeopleID, BeforeStudentID, AfterStudentID, [Parent ID], [Student First Name], [Student Last Name])
    	SELECT SUSER_SNAME() AS Name, ISNULL(D.PeopleID, I.PeopleID) AS AffectedPeopleID,
    			D.StudentID AS BeforeStudentID, I.StudentID AS AfterStudentID, I.[Parent ID] As AfterParentID, I.[Student First Name] AS AfterStudentFirstName, I.[Student Last Name] As AfterStudentLastName
    	FROM INSERTED AS I
    	FULL JOIN DELETED AS D ON I.PeopleID = D.PeopleID
    
    END

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •