CREATING TRIGGER

Question

CREATING TRIGGER

hemanth T

SSC Eights!

Points: 807
More actions
May 6, 2011 at 8:21 am

#237102

Hi All,
I met a scenario "When a coloumn of a table A is updated trigger should be performed like"AFTER UPDATE" and i need to insert values into table B".
The requirement is only when coloumn X is updated my trigger should be performed but my trigger is executing when any of the coloumns are updated.
Can anyone tell me how to write trigger for this scenario.
Thanks in Advance....

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

hemanth T (5/6/2011)
Hi All,
I met a scenario "When a coloumn of a table A is updated trigger should be performed like"AFTER UPDATE" and i need to insert values into table B".
The requirement is only when coloumn X is updated my trigger should be performed but my trigger is executing when any of the coloumns are updated.
Can anyone tell me how to write trigger for this scenario.
Thanks in Advance....

INSERT INTO TABLE B( [Column List])

SELECT [Column List]

FROM INSERTED

INNER JOIN DELETED ON INSERTED.[PrimaryKey] = DELETED.[PrimaryKey]

WHERE INSERTED.[X] <> DELETED.[X]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

You can also use the UPDATE function in the trigger. Just be aware that if you include the column in an update statement this function will consider that column updated even if the value does not change so you'd still want to use Lowell's code if you only want changes to the column.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

hemanth T SSC Eights! Points: 807 More actions · Answer 3

Hi Lowell,

I tried your code for my trigger.It is working fine.

Thank you for your valuable suggestion.

Hemanth

....................................................................................................................................................

hemanth T SSC Eights! Points: 807 More actions · Answer 4

Hi Jack,

I followed Lowell's code and its working fine.Just as you said UPDATE function may result in unnecessary INSERT's.

Thank you for extending my area of solution.

Hemanth

..........................................................................................................................................................

Lowell SSC Guru Points: 323518 More actions · Answer 5

hemanth T (5/7/2011)
Hi Lowell,
I tried your code for my trigger.It is working fine.
Thank you for your valuable suggestion.
Hemanth
....................................................................................................................................................

Glad it helped you build your trigger!

Understanding the relationship between the INSERTED and DELETED tables is all it takes to make a really good trigger.

way to go!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!