views:

36

answers:

1

Has anyone used an Notification Trigger successfully?

Does anyone know where I can get a sample of the string returned?

Thanks,

Howard

+1  A: 

I'm not completely sure what you are looking for, but you can create a trigger that will signal an event like this:

CREATE TRIGGER MyNotifier ON EventTest
   AFTER UPDATE 
BEGIN 
   execute procedure sp_SignalEvent( 'UpdateOccurred', 
                   false, 0, 'some data' );
END; 

Then the following two statements will create the event and then wait for it (30 seconds in this example). The data that would be returned is the last parameter in the sp_SignalEvent procedure ('some data' in the above example). More realistic would be to use data that was updated in the table.

execute procedure sp_CreateEvent( 'UpdateOccurred', 2 );
execute procedure sp_WaitForEvent( 'UpdateOccurred', 30000, 0, 0 );

The documentation for sp_CreateEvent also provides an example.

Mark Wilkins
I created a .Net Trigger based upon the documentation. It's been deployed to the server and I added it to a table. Do these system event procs replace the .Net one?
hsedidin
@hsedidin, The sp_* calls in the above examples are just system procedures. They don't replace triggers. You can call sp_SignalEvent in your own trigger. The create and waitfor event calls would be in whatever bit of code wants to be notified.
Mark Wilkins
According to the example for the .NET Notification Tigger, the client does not need to be connected. Since we will be developing WCF Services this is required. The event procedures appear to require a connection. This won't work in a stateless service. What would be helpful to us, is an example of the "shape" of the data returned from the .NET proc.
hsedidin
What we mean by shape is an example showing the following parameters returned from Encoding.UTF8.GetString(bytes) String strTriggerName, String strTableName, Int32 ulEventType,Int32 ulTriggerType, Int32 ulRecNo
hsedidin
@hsedidin, In order to receive a notification, the client must have a connection. It is not possible to communicate with the database server without a connection. So maybe rather than using notifications, you should be storing data in a table for later lookup ... but I am not really sure what you are looking for. I also do not know what you mean by "an example showing the following parameters..."
Mark Wilkins
According to the code in the Trigger Notification Demo, the client is using a socket to listen for notifications on a particular port.
hsedidin
The UdpClient class provides simple methods for sending and receiving connectionless UDP datagrams in blocking synchronous mode. Because UDP is a connectionless transport protocol, you do not need to establish a remote host connection prior to sending and receiving data. You do, however, have the option of establishing a default remote host in one of the following two ways: •Create an instance of the UdpClient class using the remote host name and port number as parameters.•Create an instance of the UdpClient class and then call the Connect method.
hsedidin
@hsedidin: Ah - now I understand. I thought you were talking about the notification capability that is built into Advantage. Yes, the example/demo you are referring to does use UDP and communicates to the listener without an Advantage connection. I am, however, not familiar with its usage.
Mark Wilkins