Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

trigger to start a stored procedure

Status
Not open for further replies.

michelleqw

Programmer
Joined
Jan 4, 2004
Messages
120
Location
DE
Dear sqlserver users,

We are using sqlserver 2000. Is it possible to trigger on a field in a table to start a stored procedure?

For example all records have a field X with the value "0". We want to start a SP if a record is inserted with field X = 1 or an updating of field X to 1

If yes how do we do that and if no are there any posibillities to effect wath we described above?

Nice regards,

Michelle.
 
You do it like this:

Code:
CREATE TRIGGER tr_mytable_i_u
  ON mytable
  FOR INSERT, UPDATE
AS

IF EXISTS (
    SELECT *
    FROM inserted i LEFT JOIN deleted d ON i.id = d.id
    WHERE i.x = 1
      AND (d.x IS NULL OR d.x <> 1)
  )
  EXEC my_sp
GO

The only thing to bear in mind is that the trigger will have to wait until the SP finishes processing before it can continue and the insert/update can complete.

--James
 
BOL shows an example of executing xp_sendmail from within a trigger, so I think you should be able to EXEC any procedure. Good luck!

Code:
CREATE TRIGGER trgTrigger
ON SomeTable
FOR INSERT, UPDATE, DELETE 
AS
   EXEC uspStoredProcedure

--John [rainbow]
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)
 
Where do I have to place a trigger??

Michelle.
 
??

If you mean where do you run the code, run it from Query Analyzer.

If you mean where is the trigger "created" - it is stored as an object in the database and is linked to the specific table (note this is specified in the CREATE TRIGGER syntax).

--James
 
ok but for stored procedures you do have a special folder, is there a folder for triggers. I do think so but I can't find such a folder.

Michelle.
 
In EM, right-click a table, All tasks, Manage triggers. Then select the trigger from the dropdown box.

--James
 
Thanks again, I found it!

Michelle.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top