Req:After insert & update trigger on a StoredProc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Req:After insert & update trigger on a StoredProc

Author

Topic

vijay1234
Starting Member

48 Posts

Posted - 2014-10-01 : 04:06:31

Hi all,

I have a requirement to call web services from a stored procedure.

That was achieved from the following stored procedure
________________________________________________________
USE [AdventureWorks2008R2]
GO
/****** Object: StoredProcedure [dbo].[usp_callwebservice] Script Date: 10/01/2014 12:58:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[usp_callwebservice]

@location nvarchar(200) = NULL

as begin

declare @obj int
declare @url nvarchar(200)
declare @response varchar(8000)
declare @xml XML

set @url = 'http://maps.googleapis.com/maps/api/geocode/xml?address='+@location+'&sensor=false';

exec sp_oacreate 'MSXML2.ServerXMLHTTP', @obj OUT
exec sp_OAMethod @obj,'open',NULL,'GET',@url,false
exec sp_oamethod @obj,'send'
exec sp_OAGetproperty @obj, 'responsetext', @response OUT

select @xml= CAST(@response as XML)

select @xml

exec sp_OADestroy @obj
return
end
_______________________________________________________________

I would like to create an after insert and after update triggers on this stored procedure in one single code.

Can some one help me out with the requirement please.

Here the location is the input parameter with XML as the output.

Thanks,
Vijay

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2014-10-01 : 04:35:31

Triggers can be created on Tables/Views, not on stored procedures.
As an alternative, you can insert an entry in some table after web service call. You can then create trigger on that table after each insert.

Harsh Athalye
http://in.linkedin.com/in/harshathalye/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources