CODEWITHSUNDEEP
sqlsql-serversql-server-2008triggers
user2913128
user2913128

Reputation: 23

Trigger to run stored procedure SQL Server 2008

I have staging table for data, every month company insert data in staging table. I am using a stored procedure to copy data form staging to multiple tables. I want to create a trigger to run these stored procedures.

For example my staging table blk_data and my stored procedure is sp_payment, I tried to using something like 

IF EXISTS (SELECT name FROM sysobjects
           WHERE name = 'trg_pay' AND type = 'TR')
   DROP TRIGGER blk_data
GO

CREATE TRIGGER trg_pay
ON payroll
FOR INSERT, UPDATE, DELETE
AS
   sp_payment
GO

but it doesn't work. Please correct me.

Thanks!

Upvotes: 0

Views: 2041

Answers (2)

Kritner
Kritner

Reputation: 13765

A few things... is this your intended deployment script for this trigger? I'm having trouble understanding what else this could be.

Your original statement:

IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'trg_pay' AND type = 'TR')
   DROP TRIGGER blk_data
GO
CREATE TRIGGER trg_pay
ON payroll
FOR INSERT,UPDATE,DELETE
AS
   sp_payment

GO

and your original question:

I have staging Table for data, every month company insert data in staging table . I am using Stored Procedure to copy data form staging to multiple tables. I want to create a Trigger to run these Stored procedures. For example my Staging table Name is "blk_data" and my store procedure is "sp_payment" , i tried to using something like

  1. Why are you dropping the trigger blk_data?
  2. Why are you creating the trigger on the payroll table when you state in your question the staging table name is blk_data? (at least that's how i'm interpreting it)
  3. You're missing an exec.

I would think you would actually want something like this:

IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'trg_pay' AND type = 'TR')
   DROP TRIGGER trg_pay
GO
CREATE TRIGGER trg_pay
ON blk_data
FOR INSERT,UPDATE,DELETE
AS
   exec sp_payment

GO

this will mean any changes to data in the blk_data table would cause an execution of the stored procedure sp_payment. I'm assuming this is your intention?

Upvotes: 1

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

You need to exec it:

IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'trg_pay' AND type = 'TR')
   DROP TRIGGER trg_pay
GO
CREATE TRIGGER trg_pay
ON payroll
FOR INSERT,UPDATE,DELETE
AS
   exec sp_payment

GO

You do not have to specify the EXECUTE keyword when you execute stored procedures when the statement is the first one in a batch.

https://msdn.microsoft.com/en-us/library/ms188332.aspx

Other way you have to.

Upvotes: 2

Related Questions