Update Trigger - SQL Server Forums

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

SQL Server 2012 Forums

Transact-SQL (2012)

Update Trigger

Author

Topic

Gerald30
Yak Posting Veteran

62 Posts

Posted - 2014-12-03 : 20:42:54

Hello,

Good Morning,

I have a question, on how to identify which column is updated.

I have 2 different Database in which both of them have the Personal Information Table. Both of them have the same structure.

I have created a Trigger that when a new record is inserted to Database1..Personal it will also send a copy to Database2..personal.

What I want is that when I update the Marital Status Column in Database1..Personal the trigger should only update the Marital Status Column in Database2..Personal.

Kindly suggest on how can I do this trigger.

Thank you very much

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-03 : 21:55:49

You can use the UPDATE function in a trigger to determine if it was updated or not. Please check Books Online for details, plus examples.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Gerald30
Yak Posting Veteran

62 Posts

Posted - 2014-12-03 : 22:53:19

Hi tkizer,

Thank you for your help.

Actually what I want is this.

For example both tables have 10 columns, out of the 10 columns in table 1 only column 1 and 3 are updated.

I want the trigger to also update only the 1 and 3 columns in table 2.

As much a possible I do not want to update all the 10 columns because it may also affect the performance.

Kindly correct me if I am wrong with this idea.

Thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-04 : 08:54:54

so in your trigger, use the UPDATE() function to see if the columns you are interested in have changed or not. Then update your table 2 accordingly.

http://msdn.microsoft.com/en-us/library/ms187326.aspx

Gerald30
Yak Posting Veteran

62 Posts

Posted - 2014-12-04 : 19:50:01

Hi.

Thanks for the Tip.

With this Is still need to declare the Columns 1 by 1?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-04 : 19:53:22

quote:
Originally posted by Gerald30

Hi.

Thanks for the Tip.

With this Is still need to declare the Columns 1 by 1?

Use the COLUMNS_UPDATED function to determine what got updated: http://msdn.microsoft.com/en-us/library/ms186329.aspx

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources