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

  • 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? – Aaron Bertrand Feb 18 '15 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 – constellation-pro Feb 18 '15 at 14:30
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.

|improve this answer|||||
  • 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 – constellation-pro Feb 18 '15 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). – Aaron Bertrand Feb 18 '15 at 14:18
  • I agree, 'OBJECTPROPERTY(OBJECT_ID' wasn't working for me. – constellation-pro Feb 18 '15 at 14:31
  • Updated the answer, thanks for your suggestion. – Michael Granowski Feb 18 '15 at 14:59
  • THROW is another thing that wouldn't work in every version of SQL Server. – Andriy M Feb 20 '15 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?

|improve this answer|||||
  • I was referring to the trigger creation script, apologies for that. – constellation-pro Feb 18 '15 at 13:55
  • I will create the trigger on the table as it will run as a result of the table getting updated. – constellation-pro Feb 18 '15 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

|improve this answer|||||
  • 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. – Aaron Bertrand Feb 18 '15 at 14:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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