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 that calls an RSP named pcrsp when the phone column is updated in the Adaptive Server authors table. In turn, pcrsp updates the authors table on Informix, using au_id to specify the row to update.

create trigger updatephone 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. . .pcrsp @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, updatephone starts up whenever phone is updated, as shown in the following example:

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

If the Informix update fails, the DirectConnect access service rolls back the Adaptive Server transaction and shows the following message:

@ERR >> 0

For more information about RPCs, see the following sources:

• Sybase Open Server Server-Library/C Reference Manual

• Sybase Open Client Client-Library/C Reference Manual

Copyright © 2005. Sybase Inc. All rights reserved.

View this book as PDF