Originally posted by nigelrivett
You can start an exe via
exec master..xp_cmdshell 'xxxx.exe'
Do I just add this at the end of the trigger as per below ? Pretty new to this stuff !
CREATE TRIGGER tr_owner_coord_email ON dbo.ownership
after insert as
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET coordemail = (select con.emailaddress
from contact as con with (nolock)
where coordinator = (con.forename + ' ' + con.surname))
There is not going to be heavy demand with the trigger. It will probably only be average of 60-80 rows inserted per day.
The other way I was handling this was with a timer in Vb but max interval on that is 60 seconds so even more overhead.
I would prefer to do it as Nigel suggested but need more detailed info being a newbie. I just wanted to know if there is a way the exe can be launched after the trigger had been executed. I am open to all suggestions and as was put in the original post someone thought a DTS package could launch the exe after the trigger. That way it would only be run 60-80 times per day rather than over 500 times pd using vb timer.
While the trigger runs, the transaction is not yet commited. The database keeps exclusive locks on the row or page you are about to insert into. If you have a multi-statement transaction, then there are even more locks, preventing everyone else to access your records.
Now imagine you put some long-running command in the trigger. AFAIK, xp_cmdshell runs synchronously, i.e. it waits untill your EXE completes. All the while your records are locked. You may not worry about concurrency, if this is a single-user application or some background drip-feed.
But if you intend to connect from the EXE back into Sql Server, then that is a separate connection. The EXE won't see the record just being inserted by the trigger. If the EXE attempts to Select one of the locked records, you may end up waiting forever ( Exe waits for Trigger to release the locks, while Trigger waits for Exe to complete ). Sounds like fun.
As about integrity: it is nice to know you can roll back a multi-statement transaction, including the efefcts of triggers. But xp_cmdshell is outside the Rbdms, so it won't roll back at all.
I realise triggers are an attractive thought, to streamline the chain of command, and to reduce the number of moving parts. In practice, they don't perform this role too well.