UPDATE trigger with specific column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

UPDATE trigger with specific column

Author

Topic

burzum
Yak Posting Veteran

55 Posts

Posted - 2012-06-15 : 14:54:55

Hi.

I have the following trigger:

USE [TRI_Portal]
GO
/****** Object:  Trigger [dbo].[supervisorUpdate]    Script Date: 06/15/2012 11:39:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE TRIGGER [dbo].[remoteAgentUpdate]

on [dbo].[Remote_Staff_Loggin]

after  update

as 
declare @personedit varchar (50);
declare @AgentID varchar (50);
declare @Time datetime; 
--declare @Action varchar (50);

select @personedit=i.UpdateBy from inserted i;
select @AgentID=i.ReMoteAgentID from inserted i;
select @Time=i.LastUdate from inserted i;
--select @Action=i.



begin

insert into [remoteAgent_Logs] (updatedBy, AgentID, Date) values (@personedit, @AgentID, @Time);

END

It saves the information in the logs table every time somebody edits something.

The ONLY problem I'm facing is I want to store in the logs table in the column Action the column that was edited.

How I can get that column and store it in the Action column in the logs table as well?

Any help please?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-06-15 : 15:29:02

Two issues:

1. Your trigger is written to only capture 1 row. While it won't fail, it won't capture all the rows if more than one row is updated. It also does 3 separate SELECTs. The following version corrects this:

CREATE TRIGGER [dbo].[remoteAgentUpdate] ON [dbo].[Remote_Staff_Loggin]
AFTER UPDATE
AS 
INSERT INTO [remoteAgent_Logs] (updatedBy, AgentID, DATE) 
SELECT UpdateBy, ReMoteAgentID, LastUdate FROM inserted

2. What if more than one column is updated? What would you store in the Action column?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-15 : 15:47:32

if sql 2008 you can use change data capture

http://www.simple-talk.com/sql/learn-sql-server/introduction-to-change-data-capture-%28cdc%29-in-sql-server-2008/

also for getting columns which got updated, you can use COLUMNS_UPDATED function

see

http://www.techrepublic.com/article/audit-data-using-sql-server-2005s-columnsupdated-function/6175865

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

burzum
Yak Posting Veteran

55 Posts

Posted - 2012-06-15 : 16:00:26

quote:
Originally posted by robvolk

Two issues:

1. Your trigger is written to only capture 1 row. While it won't fail, it won't capture all the rows if more than one row is updated. It also does 3 separate SELECTs. The following version corrects this:
CREATE TRIGGER [dbo].[remoteAgentUpdate] ON [dbo].[Remote_Staff_Loggin]
AFTER UPDATE
AS
INSERT INTO [remoteAgent_Logs] (updatedBy, AgentID, DATE)
SELECT UpdateBy, ReMoteAgentID, LastUdate FROM inserted
2. What if more than one column is updated? What would you store in the Action column?

How about this trigger?

USE [TRI_Portal]
GO
/****** Object:  Trigger [dbo].[supervisorUpdate]    Script Date: 06/15/2012 11:39:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


create TRIGGER [dbo].[remoteAgentUpdateTEST]

on [dbo].[Remote_Staff_Loggin]

after update

as
begin


if UPDATE (FirstName) BEGIN

insert into remoteAgent_Logs (UpdatedBy, LastName)

select d.FirstName, i.FirstName

from inserted i

inner join deleted d on i.ReMoteAgentID=d.ReMoteAgentID
AND i.FirstName <> d.FirstName

end

end

It seems that it does what I want, however I need to be able to add date, person it was edit by, their ID. Any ideas on that?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-15 : 16:04:50

use GETDATE() to capture date of modification. ID i think you should get from your table field ReMoteAgentID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

burzum
Yak Posting Veteran

55 Posts

Posted - 2012-06-15 : 16:30:35

quote:
Originally posted by visakh16

use GETDATE() to capture date of modification. ID i think you should get from your table field ReMoteAgentID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

When I add more stuff to it, it compiles, but it doesn't save ANYTHING!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-15 : 16:37:28

what do you mean it doesnt save anything? can you show code with new details added?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

burzum
Yak Posting Veteran

55 Posts

Posted - 2012-06-15 : 16:51:56

quote:
Originally posted by visakh16

what do you mean it doesnt save anything? can you show code with new details added?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

So this is my new log table.

[FirstName]
      ,[LastName]
      ,[AgentID]
      ,[Date]
      ,[PhoneNumber]
      ,[AltPhoneNumber]
      ,[AltPhoneNumber2]
      ,[AltPhoneNumber3]
      ,[UpdatedBy]

Those are the information I'm trying to collect.

I want the date to be current. And the rest of the stuff is coming from Remote_Staff_Loggin.

 [FirstName]
      ,[LastName]
      ,[PhoneNumber]
      ,[AltPhoneNumber]
      ,[AltPhone2]
      ,[AltPhone3]
      ,[ReMoteAgentID]
      ,[userUpdate]

And this is the new trigger. I only tried with first and last name. It didn't work. So I haven't added the other fields I needed.

USE [TRI_Portal]
GO
/****** Object:  Trigger [dbo].[supervisorUpdate]    Script Date: 06/15/2012 11:39:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

alter TRIGGER [dbo].[updateRemoteAgent]

on [dbo].[Remote_Staff_Loggin]

after update

as
begin


if UPDATE (FirstName) BEGIN

insert into remoteAgent_update_logs (FirstName, LastName)

select d.FirstName, i.FirstName

from inserted i

inner join deleted d on i.FirstName=d.FirstName
AND i.FirstName <> d.FirstName

end

end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-16 : 08:59:38

the code looks fine. why do you think it didnt work? Are you aware that UPDATE() function just checks whether column was part of update and doesnt really check if value has changed (in your case it should be fine as you're having check i.FirstName <> d.FirstName). But one thing to note is if your original update was setting value of FirstName from NULL then it wont cause insertion of details to log table as <> ignores NULL. in that case make insert statement as


...

insert into remoteAgent_update_logs (FirstName, LastName)

select d.FirstName, i.FirstName

from inserted i

inner join deleted d on i.FirstName=d.FirstName
AND COALESCE(i.FirstName,'') <> COALESCE(d.FirstName,'')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

burzum
Yak Posting Veteran

55 Posts

Posted - 2012-06-18 : 11:51:36

quote:

AND COALESCE(i.FirstName,'') <> COALESCE(d.FirstName,'')

Thank you. So having that in insert statement fixes the NULL part.

However, let say in column PhoneNumber I don't have anything. And I go and update it. In my table it will show that it was updated from NULL to that phone number, but it won't show that phone number it PhoneNumber field, UNTILL I go back and do it second time. It does that for every column.

Here's my final code.

USE [TRI_Portal]
GO
/****** Object:  Trigger [dbo].[supervisorUpdate]    Script Date: 06/15/2012 11:39:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


alter TRIGGER [dbo].[updateRemoteAgent]

on [dbo].[Remote_Staff_Loggin]

after update

as
begin


if UPDATE (FirstName) BEGIN

insert into remoteAgent_update_logs (originalName, OriginalNameUpdatedTo, AgentID, Date, UpdatedBy, PhoneNumber, AltPhoneNumber, AltPhoneNumber2, AltPhoneNumber3)

select d.FirstName, i.FirstName, d.ReMoteAgentID, d.LastUdate, d.userUpdated, d.PhoneNumber, d.AltPhoneNumber, d.AltPhone2, d.AltPhone3

from inserted i

inner join deleted d on i.ReMoteAgentID=d.ReMoteAgentID
AND COALESCE(i.FirstName,'') <> COALESCE(d.FirstName,'')

end


if UPDATE (LastName) BEGIN

insert into remoteAgent_update_logs (originalName, OriginalNameUpdatedTo, AgentID, Date, UpdatedBy, PhoneNumber, AltPhoneNumber, AltPhoneNumber2, AltPhoneNumber3)

select d.LastName, i.LastName, d.ReMoteAgentID, d.LastUdate, d.userUpdated, d.PhoneNumber, d.AltPhoneNumber, d.AltPhone2, d.AltPhone3

from inserted i

inner join deleted d on i.ReMoteAgentID=d.ReMoteAgentID
AND COALESCE(i.LastName,'') <> COALESCE(d.LastName,'')

if UPDATE (PhoneNumber) BEGIN

insert into remoteAgent_update_logs (originalName, OriginalNameUpdatedTo, AgentID, Date, UpdatedBy, PhoneNumber, AltPhoneNumber, AltPhoneNumber2, AltPhoneNumber3)

select d.PhoneNumber, i.PhoneNumber, d.ReMoteAgentID, d.LastUdate, d.userUpdated, d.PhoneNumber, d.AltPhoneNumber, d.AltPhone2, d.AltPhone3

from inserted i

inner join deleted d on i.ReMoteAgentID=d.ReMoteAgentID
AND COALESCE(i.PhoneNumber,'') <> COALESCE(d.PhoneNumber,'')

if UPDATE (AltPhoneNumber) BEGIN

insert into remoteAgent_update_logs (originalName, OriginalNameUpdatedTo, AgentID, Date, UpdatedBy, PhoneNumber, AltPhoneNumber, AltPhoneNumber2, AltPhoneNumber3)

select d.AltPhoneNumber, i.AltPhoneNumber, d.ReMoteAgentID, d.LastUdate, d.userUpdated, d.PhoneNumber, d.AltPhoneNumber, d.AltPhone2, d.AltPhone3

from inserted i

inner join deleted d on i.ReMoteAgentID=d.ReMoteAgentID
AND COALESCE(i.AltPhoneNumber,'') <> COALESCE(d.AltPhoneNumber,'')

end

if UPDATE (AltPhone2) BEGIN

insert into remoteAgent_update_logs (originalName, OriginalNameUpdatedTo, AgentID, Date, UpdatedBy, PhoneNumber, AltPhoneNumber, AltPhoneNumber2, AltPhoneNumber3)

select d.AltPhone2, i.AltPhone2, d.ReMoteAgentID, d.LastUdate, d.userUpdated, d.PhoneNumber, d.AltPhoneNumber, d.AltPhone2, d.AltPhone3

from inserted i

inner join deleted d on i.ReMoteAgentID=d.ReMoteAgentID
AND COALESCE(i.AltPhone2,'') <> COALESCE (d.AltPhone2,'')

if UPDATE (AltPhone3) BEGIN

insert into remoteAgent_update_logs (originalName, OriginalNameUpdatedTo, AgentID, Date, UpdatedBy, PhoneNumber, AltPhoneNumber, AltPhoneNumber2, AltPhoneNumber3)

select d.AltPhone3, i.AltPhone3, d.ReMoteAgentID, d.LastUdate, d.userUpdated, d.PhoneNumber, d.AltPhoneNumber, d.AltPhone2, d.AltPhone3

from inserted i

inner join deleted d on i.ReMoteAgentID=d.ReMoteAgentID
AND COALESCE(i.AltPhone3,'') <> COALESCE (d.AltPhone3,'')

end

end


end


end
end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-18 : 11:54:21

sorry didnt get that. do you mean to say originalphonenumber field wont show the NULL value? can you explain with sample data your issue for us to understand better

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

burzum
Yak Posting Veteran

55 Posts

Posted - 2012-06-18 : 12:02:21

Thanks for quick reply.

In my log table I have two fields OriginalName and originalNameUpdateTo. The original name is what was there before and the update to is to what I will update to.

So let's say in PhoneNumber Column the value is NULL. I go and edit that. I enter number 3. So when I go back to my logs table it shows that in the field originalName is NULL and in the field OriginalNameUpdateTO 3. HOWEVER, the actual PhoneNumber column will stay NULL until I go back and change it the second time. What I want is if I changed the first time, it should show it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-18 : 12:06:22

thats because you're using d.PhoneNumber,d.AltPhoneNumber, d.AltPhone2 etc

if you want values in them firsttime itself change them to i.PhoneNumber,i.AltPhoneNumber, i.AltPhone2 etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

burzum
Yak Posting Veteran

55 Posts

Posted - 2012-06-18 : 12:18:02

I tried this just for AltPhone number to test, but still no luck.

if UPDATE (AltPhoneNumber) BEGIN

insert into remoteAgent_update_logs (originalName, OriginalNameUpdatedTo, AgentID, Date, UpdatedBy, PhoneNumber, AltPhoneNumber, AltPhoneNumber2, AltPhoneNumber3)

select i.AltPhoneNumber, i.AltPhoneNumber, d.ReMoteAgentID, d.LastUdate, d.userUpdated, d.PhoneNumber, d.AltPhoneNumber, d.AltPhone2, d.AltPhone3

from inserted i

inner join deleted d on i.ReMoteAgentID=d.ReMoteAgentID
AND COALESCE(i.AltPhoneNumber,'') <> COALESCE(i.AltPhoneNumber,'')

end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-18 : 12:20:58

you have done it at wrong end. it should be


if UPDATE (AltPhoneNumber) BEGIN

insert into remoteAgent_update_logs (originalName, OriginalNameUpdatedTo, AgentID, Date, UpdatedBy, PhoneNumber, AltPhoneNumber, AltPhoneNumber2, AltPhoneNumber3)

select d.AltPhoneNumber, i.AltPhoneNumber, d.ReMoteAgentID, d.LastUdate, d.userUpdated, i.PhoneNumber, i.AltPhoneNumber, i.AltPhone2, i.AltPhone3

from inserted i

inner join deleted d on i.ReMoteAgentID=d.ReMoteAgentID
AND COALESCE(i.AltPhoneNumber,'') <> COALESCE(i.AltPhoneNumber,'')

end

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

burzum
Yak Posting Veteran

55 Posts

Posted - 2012-06-18 : 12:21:54

I changed this as well to test.

insert into remoteAgent_update_logs (originalName, OriginalNameUpdatedTo, AgentID, Date, UpdatedBy, PhoneNumber, AltPhoneNumber, AltPhoneNumber2, AltPhoneNumber3)

insert into remoteAgent_update_logs (originalName, OriginalNameUpdatedTo, AgentID, Date, UpdatedBy, PhoneNumber, i.AltPhoneNumber, AltPhoneNumber2, AltPhoneNumber3)

didn't work.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-18 : 12:24:59

please try last suggestion given and then report back

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

burzum
Yak Posting Veteran

55 Posts

Posted - 2012-06-18 : 12:27:42

So I've tried your suggestion just for the AltPhoneNumber.

What happens now, is it doesn't even store anything in the logs table IF I change it in AltPhoneNumber column. IT will be just blank. However if I add something in the next field and update it, it does what I want.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-18 : 12:32:46

your where condition was wrong as well


if UPDATE (AltPhoneNumber) BEGIN

insert into remoteAgent_update_logs (originalName, OriginalNameUpdatedTo, AgentID, Date, UpdatedBy, PhoneNumber, AltPhoneNumber, AltPhoneNumber2, AltPhoneNumber3)

select d.AltPhoneNumber, i.AltPhoneNumber, d.ReMoteAgentID, d.LastUdate, d.userUpdated, i.PhoneNumber, i.AltPhoneNumber, i.AltPhone2, i.AltPhone3

from inserted i

inner join deleted d on i.ReMoteAgentID=d.ReMoteAgentID
AND COALESCE(id.AltPhoneNumber,'') <> COALESCE(i.AltPhoneNumber,'')

end

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

burzum
Yak Posting Veteran

55 Posts

Posted - 2012-06-18 : 12:38:56

Awesome, works!

Thank you my friend for your time and willingness to help. I appreciate it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-18 : 14:54:21

welcome
please give sample data and required output in future when you post questions. that would avoid some confusion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources