How to create a SQL trigger to execute a stored procedure when a table is updated

Question

i need some help with creating a trigger to run a qf.stored_procedure when a qf.table is created. AT the moment, i have a job t run the stored procedure every 15 minutes but changing that is not an option.

The idea is for the trigger to run the stored procedure whenever the table is updated regardless of whether its a single row or all. As well am not sure whether to create it in triggers under the table or Database triggers?. i want the script to check if the trigger exist and if it doesn't then create it.

table name: qf.customer_working_hours columns: WHours_id,DayOFW

Stored Procedure: qf.ServiveRefreshCustomer_WH

Am new to SQL scripting and SQL triggers, any help will be appreciated

Answer 1

I am assuming you are trying to implement some basic replication using a trigger. As long as your process remains fairly simple, this should work OK.

However, I would advise you to read up on (i.e. google) why executing stored procedures from triggers is not a good idea, and also about SQL's built in transactional replication feature. Here is a practical guide to the 2nd topic: http://www.sql-server-performance.com/2010/transactional-replication-2008-r2/

Having said that, here is what I think should work:

USE [database_name]
IF EXISTS (SELECT 1 FROM sys.triggers WHERE name = N'trigger_name')
BEGIN
  THROW 51000, 'The trigger [trigger_name] already exists.', 1;
END
ELSE
  CREATE TRIGGER [trigger_name] ON qf.customer_working_hours
  AFTER INSERT, UPDATE, DELETE AS
  BEGIN
    SET NOCOUNT ON
    EXEC [procedure_database].qf.ServiveRefreshCustomer_WH
  END
BEGIN
END

EDIT: Updated trigger if exists check from Aaron's suggestion below.

Answer 2

Here's a link that's very similar:

https://stackoverflow.com/questions/3768278/call-stored-procedure-within-create-trigger-in-sql-server

Since the scope is an action resulting from a data change within a table, you'll want to write the trigger on the table, not the database.

When you say "i want the script to check if the trigger exist and if it doesn't then create it", do you mean the trigger creation script or the procedure it's supposed to execute?

Answer 3

Thanks Dog OnAPorch, your link was veryhelpful. i for-gone checking if the trigger exists on this one off rare occasion but i managed to get a working version from the link you provided.

SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO

CREATE TRIGGER qf.Update_ServiceWorkingHours_Cache     
ON  qf.customer_working_Hours    
AFTER INSERT,UPDATE AS  BEGIN
    SET NOCOUNT ON;
    -- EXEC do something here   
EXEC [qf].[ServiceRefreshCustomer_WH] 
END 
GO

It all works like a charm, thanks you all for the assistance