SQLTeam.com | Weblogs | Forums

Automate a script when table is altered on the front end?


#1

I am fairly new to T-SQL and know just basics. So bare with me :wink: I work at a hospital and one of the directors is needing something that will notify them when a patient is readmitted with 30 days of previous admit. I have written a script that will do that, but it has to be manually run and they are wanting it to happen basically real time.

My question is there a way when a patient is admitted that it will run this script then populate a file that is then sent in an email to that individual? I don't have the rights to create or edit tables on the prod server, but was able to get a database on another server that I can do with what I want....if that helps ?


#2

short answer: yes, you can do it

longer: What happens on the database server when a patient is admitted? Is there some table you can query to find them? If so, you can set up a SQL Server Agent job to run a script every few minutes to query that table and send the email.


#3

If you could then you could add a TRIGGER to the Admissions table which would make the check you need whenever a record was modified.

As you aren't allowed to do that your only option is to periodically run a query to look for any appropriate records, as gbritton described. But that is a brute-force approach so make sure your query is efficient - if it runs frequently it will be important not to put strain on the system - particularly once you have hundreds of such things running every minute or two!