4

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

2
  • 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? Feb 18, 2015 at 14:15
  • 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 Feb 18, 2015 at 14:30

3 Answers 3

6

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.

5
  • 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 Feb 18, 2015 at 13:38
  • 1
    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). Feb 18, 2015 at 14:18
  • I agree, 'OBJECTPROPERTY(OBJECT_ID' wasn't working for me. Feb 18, 2015 at 14:31
  • Updated the answer, thanks for your suggestion. Feb 18, 2015 at 14:59
  • THROW is another thing that wouldn't work in every version of SQL Server.
    – Andriy M
    Feb 20, 2015 at 7:44
0

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?

2
  • I was referring to the trigger creation script, apologies for that. Feb 18, 2015 at 13:55
  • I will create the trigger on the table as it will run as a result of the table getting updated. Feb 18, 2015 at 13:56
0

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

2
  • 1
    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. Feb 18, 2015 at 14:35
  • 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.
    – jason
    Apr 24, 2020 at 16:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.