trigger to start a stored procedure

[michelleqw]

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.

 

[JamesLean]

You do it like this:

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

 

[JohnDTampaBay]

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!

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

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[michelleqw]

Thanks a lot.

Michelle.

 

[michelleqw]

Where do I have to place a trigger??

Michelle.

 

[JamesLean]

??

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

 

[michelleqw]

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.

 

[JamesLean]

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

--James

 

[michelleqw]

Thanks again, I found it!

Michelle.