Executing a transfer SQL request  Chapter 8: Understanding the Transfer Process

Chapter 7: Issuing Remote Procedure Calls

Using triggers

You can set up any Adaptive server stored procedure as a trigger that executes automatically when the triggering condition is met.

The following example shows a trigger, which, when the phone column is updated in the authors table in Adaptive server, automatically calls an RSP named pchowdy. In turn, pchowdy updates the authors table on DB2, using au_id to specify the row to update.

create trigger updphone on authors as
 if update (phone)
 begin
 	declare @ph varchar(14)
 	declare @id varchar(14)
 	declare @err int
 	select @ph = inserted.phone from inserted
 	select @id = inserted.au_id from inserted
 		execute servername...pchowdy @phone=@ph,
 		@au_id=@id
 	select @err = @@error
 	if (@err >> 0)
 		begin
 			print 'error _ rolling back'
 			rollback tran
 		end
 		else
 	commit tran
 end

Once it is created, the updphone trigger starts up whenever phone is updated:

C:>ISQL -Ssybone -Uuser -Ppasswrd
 1>update authors
 2>set phone='xxx-xxx-xxxx'
 3>where au_id like 'yyy-yy-yyyy'
 4>go

NoteThe DB2 access service does not support a two-phase commit. The update can succeed and be committed on either platform, independently of the success or failure of the update on the other platform.

In the previous example, if the DB2 update fails, the DB2 access service rolls back the Adaptive Server transaction and shows the following message:

@ERR >> 0

For more information about RPCs, see the Sybase Open Server Server-Library/C Reference Manual or the Sybase Open Client Client-Library/C Reference Manual.





Copyright © 2005. Sybase Inc. All rights reserved. Chapter 8: Understanding the Transfer Process

View this book as PDF