Call a DLL or EXE file from SQL Trigger

I need some help calling a DLL or EXE from a SQL Trigger. I have the trigger set up, except I have no clue how to call a DLL or EXE or if it is even possible. Here is what I have for the Trigger so far:

---

-- ================================================

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

CREATE TRIGGER CallProgIfParentID

ON Toc

FOR INSERT

AS

IF ((select ins.[ParentID] FROM inserted ins) = '57660')

-- I want to be able to programmatically change '57660' from an ASPX page

EXEC

-- This is where the call function goes

GO

---

Another question I have is can a trigger be created programmatically using ASPX pages written in VB.NET in VS2003?

Thank you in advance

I have to warn you first: although calling external exes is possible, it is not recommended as there are at least a couple obvious drawbacks:

1. You would possibly lose data integrity because those external processes are NOT bound to the SQL transaction that triggers run in. For example, if the INSERT statement that fires the insert trigger is rolled back, regular trigger actions would be rolled back too, but those external processes would not. Similarly, triggers might not be able to detect errors that happen to those external processes and it ends up with the external exe failed but the trigger (as well as the firing statement) succeeded.
2. These processes will run outside of SQL Server, so you would lose total control of them. E.g. they might come back and compete with SQL Server for resources like CPU and memory.

I am wondering what kind of scenarios you have, but there got to be a better way to do it :)

Anyway, if you still decide to go with this route, you could call external exes from a trigger by:

- xp_cmdshell '<some exe>'
- For a *unsafe* CLR trigger, you could practically do anything, including calling external processes (e.g. Process class)

For you other question, yes, you can create triggers programmatically from wherever you can connect to the server.

|||

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER SendMailIfParentID

ON Toc

AFTER INSERT

AS

IF ((select ins.[ParentID] FROM inserted ins) = '57660')

EXEC master ..xp_cmdshell 'C:\Documents and Settings\michael\My Documents\Visual Studio Projects\Test\bin\Test.exe 57660'

---

Thats what I have for my trigger now (The 57660 is a test ID number and isn't of much relavence), either the trigger isnt being called or the EXE isn't being called. I can't figure out why it's not working.

|||

I figured out what wasn't working and fixed it. Now I can't get a variable to append to the end of the command line. It keeps telling me the + isn't valid. Does anyone have any ideas?

---

ALTER TRIGGER SendMailIfParentID

ON Toc

AFTER INSERT

AS

IF ((select ins.[ParentID] FROM inserted ins) = '57750')

DECLARE @.MyTocId varchar(12)

SELECT @.MyTocId = (SELECT TocId FROM inserted)

EXEC master ..xp_cmdshell '"C:\Documents and Settings\michael\My Documents\Visual Studio Projects\Test\bin\Test.exe" ' + @.MyTocId

GO

|||Hi,

for debugging and better handling purposes, I would suggest first putting evverything in a varaible and executing this afterwards:

IF ((select ins.[ParentID] FROM inserted ins) = '57750')

DECLARE @.MyTocId varchar(12)

SELECT @.MyTocId = '"C:\Documents and Settings\michael\My Documents\Visual Studio Projects\Test\bin\Test.exe" ' + TocId FROM inserted

EXEC master ..xp_cmdshell @.command = @.MyTocId

Warning: Triggers are fired per statement not per row, you will in addition make sure that your trigger is able to handle multiple rows affected in a trigger. In further addition you will have to make sure that the command is not executed as no row is affected as the trigger is fired (as already said) on a statement basis (even if the affected rowcount is 0).

Jens K. Suessmeyer.

http://www.sqlserver2005.de