UPdate Trigger

Question

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 , tryign to make sure I created this Trigger correctly
PeopleUpdate_tbl

People_tbl

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 ____] [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

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]

Was wondering if I created this trigger correctly??

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

Answer 1

You can do the following for the trigger. However, there are few issues with the tables themselves. Are these just examples or that's how you will create them?

CREATE TRIGGER trig_UpdateAddress

ON People_tbl

AFTER UPDATE
AS
IF UPDATE(StudentAddress) OR UPDATE(StudentCity) OR UPDATE(StudentState) OR UPDATE (StudentZip) Or UPDATE (Phone) OR UPDATE (Message)
BEGIN
	INSERT INTO PeopleUpdate_tbl
	SELECT d.UpdateID,d.StudentAddress, d.StudentCity ,d.StudentState ,i.StudentZip ,i.Phone ,i.Message 

	FROM inserted i
	INNER JOIN deleted d
		ON i.EmpId = d.EmpId
END

GO

---

Abdallah, PMP, ITIL, MCTS

Answer 2

Check http://wiki.lessthandot.com/index.php/General_best_practices_for_SQL_Server_triggers

I agree with Abdallah's suggestion of moving your WHERE logic into separate IF statement also and then, IMHO, you can use your statement or just compare D.Value with I.value to check if the value has been changed or not (in the later case insert NULL for this field)

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 3

Microsoft has introduced the OUTPUT clause with SQL Server 2005 to be used for audit trail creation such is the case above.  The inserted and deleted (trigger) tables also available in OUTPUT.
 
Triggers should only be used as last resort.

---

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com

Answer 4

Hi Kalman,

How would you detect only the changes (e.g. if the field didn't change put NULL) using OUTPUT clause ?

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 5

Precisely Naomi,

Using the friendly NULL & NULLIF is one way of doing it. A second way is the CASE function, since the entire inserted and deleted row is available for conditional operations.

-- T-SQL OUTPUT clause for audit log (trail) tracking
USE tempdb; 
GO 

SELECT * 
INTO   dbo.Employee 
FROM   AdventureWorks.HumanResources.Employee 

DECLARE  @AuditTrail  TABLE( 
         ID                INT    IDENTITY ( 1 , 1 )    PRIMARY KEY, 
         EmployeeID        INT    NOT NULL, 
         OldVacationHours  INT, 
         NewVacationHours  INT, 
         OldSickLeaveHours INT, 
         NewSickLeaveHours INT, 
         ModifiedDate      DATETIME 
                            ); 

UPDATE TOP ( 10 ) Employee 
SET    VacationHours = VacationHours * 1.05 
OUTPUT	INSERTED.EmployeeID, 
		NULLIF(DELETED.VacationHours,INSERTED.VacationHours), 
		NULLIF(INSERTED.VacationHours,DELETED.VacationHours), 
		NULLIF(DELETED.SickLeaveHours,INSERTED.SickLeaveHours), 
		NULLIF(INSERTED.SickLeaveHours,DELETED.SickLeaveHours), 
		INSERTED.ModifiedDate INTO @AuditTrail; 

SELECT * 
FROM   @AuditTrail; 

/* 
ID  EmployeeID  OldVacationHours  NewVacationHours  OldSickLeaveHours  NewSickLeaveHours  ModifiedDate 
1  1  21  22  NULL  NULL  2004-07-31 00:00:00.000 
2  2  42  44  NULL  NULL  2004-07-31 00:00:00.000 
3  3  NULL  NULL  NULL  NULL  2004-07-31 00:00:00.000 
4  4  48  50  NULL  NULL  2004-07-31 00:00:00.000 
5  5  NULL  NULL  NULL  NULL  2004-07-31 00:00:00.000 
6  6  40  42  NULL  NULL  2004-07-31 00:00:00.000 
7  7  82  86  NULL  NULL  2004-07-31 00:00:00.000 
8  8  83  87  NULL  NULL  2004-07-31 00:00:00.000 
9  9  NULL  NULL  NULL  NULL  2004-07-31 00:00:00.000 
10  10  88  92  NULL  NULL  2004-07-31 00:00:00.000 
*/ 
GO 

DROP TABLE Employee 

---

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com

Answer 6

Wow, very interesting and clever way of using NULLIF function - I used to have a constant as a second parameter for it.

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 7

You can do the following for the trigger. However, there are few issues with the tables themselves. Are these just examples or that's how you will create them?

CREATE TRIGGER trig_UpdateAddress



ON People_tbl



AFTER UPDATE

AS

IF UPDATE(StudentAddress) OR UPDATE(StudentCity) OR UPDATE(StudentState) OR UPDATE (StudentZip) Or UPDATE (Phone) OR UPDATE (Message)

BEGIN

	INSERT INTO PeopleUpdate_tbl

	SELECT d.UpdateID,d.StudentAddress, d.StudentCity ,d.StudentState ,i.StudentZip ,i.Phone ,i.Message 



	FROM inserted i

	INNER JOIN deleted d

		ON i.EmpId = d.EmpId

END



GO

---

Abdallah, PMP, ITIL, MCTS

what would be the issues with the tables themselves???

Answer 8

Don't put the datatypes inside brackets. Also, I wouldn't put spaces in the column names, it's not best practice to do that. So save the columns as this ParentID instead of [Parent ID].

---

Abdallah, PMP, ITIL, MCTS

Answer 9

I see your point your absolutelty right thank you