Show an alert without rollback 1

[garwain]

I have 2 tables that are related,
table1
id :int
status:string

table2
id:int
table1_id:int
...

my boss has asked me to find a way do display an alert when data is inserted in table2 if table1.status has a certain value.

I've set up a trigger that detects the insert just fine, but I can't seem to figure out how to show an alert.
print only seems to display if I'm working in the query analyser, and raiserror('msg', 11,1) will cause a rollback of the transaction.

If this was just for our software it would be simple to program in a check, but my boss wants the notification to work when he inserts data manually in the enterprise manager as well.

Any ideas on how I could go about this would be greatly appreciated.

 

[memcom]

One would be to use master.dbo.xp_cmdshell. This extended stored procedure will allow you to use any command that you could normally use at a command prompt. From there you can use "net send" to display a message on a particular computer. Be very carefull with xp_cmdshell because you don't want too many people to have permissions to use it. By default the permissions are set so that it is not executable by most people.

 

[garwain]

Thanks. That procedure gave me enough to work with to get a solution set up. It's a little slower than I like, but it works.