2

If I have a simple table and I want to do something (say .. execute a stored procedure) when a column (and I know the column I wish to look for) has changed.

Is this possible?

Version: SQL Server 2008 R2.

Example table :-

ID INTEGER PRIMARY KEY
Name NVARCHAR(100) NOT NULL
Age TINYINT NOT NULL
HairColour TINYINT NOT NULL

So .. if the content of the Name field changes, then I will execute stored procedure 'Foo'.

Any ideas?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Pure.Krome
  • 84,693
  • 113
  • 396
  • 647

1 Answers1

4

Use:

CREATE TRIGGER trig_updateName ON YOUR_TABLE

FOR UPDATE AS

  IF NOT UPDATE(Name) 
  BEGIN

     RETURN

  END

  EXEC foo

This trigger would automatically be executed whenever we updated one/more records in YOUR_TABLE. The "UPDATE" function is used to check whether or not the "Name" field has been updated by an "UPDATE" query that executed the "trig_updateAuthor" trigger. If field hasn't, then the trigger returns control to SQL server.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • Poines - sauceofawesome ! Now, the thing that KILLS me with triggers (read: I suck at triggers) is that if *any/one-or-more* of the rows that were effected, in the trigger results, this the stored proc will run. I can never get my head around triggers + multiple results/rows and having to fire off logic per row. i keep thinking it's one trigger per row *blush* .. I'm guessing i'll need an evil cursor to call stored procs per row... :( – Pure.Krome Aug 24 '10 at 03:14
  • @Pure.Krome: Depends on what you want to do in the stored procedure. Maybe that should addressed in a separate question? – OMG Ponies Aug 24 '10 at 03:21
  • Yep - i was only thinking out aloud .. definitely not extending this question. – Pure.Krome Aug 24 '10 at 04:25