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

• Aaron Bertrand: By updated or altered, do you mean this stored procedure needs to run any time a user runs UPDATE and also any time someone runs ALTER TABLE? Can you give some details about what the stored procedure does? Does it need to have some understanding of what row(s) were affected, or does it just blindly process every single row in the table even if only one row was touched?
• constellation-pro: The whole idea was to run the stored procedure whenever a single of mutiple runs are touched. The table is made of just the five days and working hours so the data will allows get altered than updated quarterly but the stored procedure is locked, so i don't know what it does specifically but am guessing, it updates a bunch of tables

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.

• constellation-pro: Thanks Michael, this is what i was looking for. i wasn't aware that it was a bad idea running stored procedures from triggers but i will take that under advisement though it would be helpful to know why. thank you again for your quick response
• Aaron Bertrand: IF EXISTS (SELECT 1 FROM sys.triggers WHERE name = N'name') is a much more intuitive approach, and is no less portable than OBJECTPROPERTY(OBJECT_ID which, AFAIK, will only ever work in SQL Server (and maybe Sybase).
• constellation-pro: I agree, 'OBJECTPROPERTY(OBJECT_ID' wasn't working for me.
• Andriy M: THROW is another thing that wouldn't work in every version of SQL Server.

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?

• constellation-pro: I was referring to the trigger creation script, apologies for that.
• constellation-pro: I will create the trigger on the table as it will run as a result of the table getting updated.

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

• Aaron Bertrand: Not sure why this is an answer - it's essentially the same as Michael's, except that it doesn't satisfy one of your requirements: i want the script to check if the trigger exist and if it doesn't then create it.
• jason: Personally, I think this helps being an answer as it makes the exact resolution chosen quite prominent. If the author accepted this as the answer, I'd take issue as you are right that it copies Michael's answer nearly verbatim. I think a better approach here is for @constelllation-pro to update his question with this resolution, delete this answer, and accept Michael's answer as the solution.