It might be possible if you use a CLR trigger and set up the right messaging services between the server and the client. You can also drive a screw by twisting it with the claws on your hammer, but it's a lot easier, more efficient, etc., to just plain use a screwdriver. You can steer a car by sticking metal poles out from the sides and forcing them into the surface of the road, but it's generally better to use the steering wheel. And so on. Just because something is possible, doesn't necessarily make it a good idea.

I'm going to have to agree with GSquared on this one. This definitely does not belong in a trigger and as Gail said the trigger runs on the server so where would the pop-up show up?

I'd be interested to see the resources you found about triggers and how you are using triggers. Typically you try to limit the amount of work done in triggers. You either do the work in a a stored procedure or the business layer of the application.

SQL Server is a server. Your application will submit an insert or update to the server, and any result from that gets sent to your application, not the user on the screen. SQL Server has no interaction here.

What you can do is return a message to the application, but it would need to handle that message and then display something. Alternatively, the way most people handle it and GSquared suggested, is that you use the front end to catch this before you submit it to SQL Server and pop a note to the client.