Example 5

This example uses a trigger with a Web service to deliver quotes and other information about a stock in the form of an XML document.

To use this Web service, you must create a table to hold stock information:

1> create table stocktab(symbol varchar(25), value numeric(10,2) null, xmldoc text null)
2> go

Use the add option of sp_webservices to map Web methods to proxy tables:

1>sp_webservices "add",
"http://www.xignite.com/xquotes.asmx?WSDL", ws
2>go

Fifteen Web methods are mapped to proxy tables. One of these Web methods is named GetQuotes.

Now create a trigger that invokes the GetQuotes Web method. The trigger places the output from GetQuotes, a stock quote for the specified symbol and a summary of other data encapsulated in an XML document, into the value and xmldoc columns of the stocktab table:

1> CREATE trigger stocktrig ON stocktab FOR insert,update
AS
UPDATE stocktab 
SET    stocktab.value =
       xmlextract('//Quote/Last/text()',outxml returns        numeric(10,2)), xmldoc = outxml
FROM   stocktab, inserted , GetQuotes
WHERE  _inxml  ='<GetQuotes 
       xmlns="http://www.xignite.com/services/">
       <Symbol>'+stocktab.symbol+'</Symbol>
       </GetQuotes>'
AND    stocktab.symbol = inserted.symbol
2> go

This trigger executes when data is inserted into the stocktab table:

1> insert stocktab values('SY',null,null)
2> go
1> insert stocktab values('PSFT',null,null)
2> go
1> insert stocktab values('MSFT',null,null)
2> go

View the updated data with a select on the stocktab table:

1> select value, xmldoc from stocktab
2> go

value             xmldoc                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           ------------- ------------------------ ...
22.53          <?xml version="1.0" encoding="UTF-8" ?>
<GetQuotesResponse ...> ...</GetQuotesResponse>
21.04          <?xml version="1.0" encoding="UTF-8" ?>
<GetQuotesResponse ...> ...</GetQuotesResponse>
27.07          <?xml version="1.0" encoding="UTF-8" ?>
<GetQuotesResponse ...> ...</GetQuotesResponse>
(3 rows affected)

Copyright © 2004. Sybase Inc. All rights reserved.

View this book as PDF