Results 1 to 5 of 5

Thread: Trigger Update

  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Trigger Update

    Hi guys, I am trying to make sure I am creating this trigger correctly

    I am trying to create it on the people_tbl so that when the users updates an address or phone that the old address will be sent to the
    PeopleUpdate_tbl

    People_tbl
    Code:
    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] [DATETIME](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] [INT](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,


    PeopleUpdate_tbl

    Code:
    CREATE TABLE [dbo].[PeopleUpdate_tbl](
    	[Parent ID] [nvarchar](50) NULL,
    	[NewTelephone] [nvarchar](50) NULL,
    	[NewMessage] [nvarchar](50) NULL,
    	[NewAddress] [nvarchar](50) NULL,
    	[NewCity] [nvarchar](50) NULL,
    	[NewState] [nvarchar](50) NULL,
    	[NewZip] [nvarchar](50) NULL,
    	[NewEmailAddress] [nvarchar](50) NULL,
    	[DateOfUpdate] [datetime] NULL,
    	[UpdateID] [int] IDENTITY(1,1) NOT NULL,
    	[OldTelephone] [nvarchar](50) NULL,
    	[PeopleID] [int] NULL,
    	[OldMessage] [nvarchar](50) NULL,
    	[OldAddress] [nvarchar](50) NULL,
    	[OldCity] [nvarchar](50) NULL,
    	[OldState] [nvarchar](50) NULL,
    	[OldZip] [nvarchar](50) NULL,
    	[OldEmailAddress] [nvarchar](50) NULL,
     CONSTRAINT [PK_PeopleUpdate_tbl] PRIMARY KEY CLUSTERED 
    (
    	[UpdateID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]



    Trigger
    Code:
    CREATE TRIGGER trig_UpdateAddress
    ON People_tbl
    AFTER UPDATE
    AS
    BEGIN
    	INSERT INTO PeopleUpdate_tbl
    	SELECT 
    		d.UpdateID,
    		CASE WHEN UPDATE(StudentAddress) THEN d.StudentAddress ELSE NULL END,
    		CASE WHEN UPDATE(StudentCity) THEN d.StudentCity ELSE NULL END,
    		CASE WHEN UPDATE(StudentState) THEN d.StudentState ELSE NULL END,
    		CASE WHEN UPDATE(StudentZip) THEN i.StudentZip ELSE NULL END,
    		CASE WHEN UPDATE(Phone) THEN i.Phone ELSE NULL END,		
    		CASE WHEN UPDATE(Message) THEN i.Message ELSE NULL END
    	FROM inserted i
    	INNER JOIN deleted d
    		ON i.EmpId = d.EmpId
    	WHERE UPDATE(StudentAddress) OR UPDATE(StudentCity) OR UPDATE(StudentState) OR UPDATE (StudentZip) Or UPDATE (Phone) OR UPDATE (Message)
    END
    GO

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You need to list your columns in the destination table (PeopleUpdate_tbl) on the INSERT statement.

    Also, I'm not hugely familiar with UPDATE() but I'm not sure you've used it right. Won't that insert ALL updated records even if only one of the rows has a change to those fields?

    Also, add a SET NOCOUNT ON in the trigger.
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    is there a question?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Don't use the UPDATE function.

    Do a comparison instead, such as:
    WHERE inserted.[COLUMN]<>deleted.[COLUMN] or ....

    Using UPDATE will yield results in cases where no update actually occurred.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Quote Originally Posted by Thrasymachus View Post
    is there a question?



    yes did I create the trigger correctly??

Posting Permissions

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