0 votes
20 views
There are time when we would like to trigger an exe rather a sql query in sql triggers.

We can trigger sql query or even a exe file , it would be helpful for users who are in need of the operation as said.

Please find the code in the answer

Thanks
asked Jun 4, 2016 in Computer Science - IT by slow_learner (1,492 points) | 20 views

1 Answer

0 votes
Best answer

Below we have an trigger command which executes the file in location given ,

Find the schema and more about trigger in :

Write a Simple Trigger

Now we are adding trigger to table events , 

 

USE [one]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

create TRIGGER [dbo].[trgAfterInsert1]
on [dbo].[events]
FOR INSERT
AS declare @id int,@tag int,@audio int;

BEGIN

EXEC master ..xp_cmdshell '\\filelocation\a.mp3'

END

 

Note :

  • Make sure the folder is shared where the file to be executed is available
  • also allow the sql user to access the file and have privilege , you can set from server -> user -> securables
  • Also set user as sysadmin to perform actions like executing exe files or playing mp3 files

 

answered Jun 4, 2016 by slow_learner (1,492 points)
edited Jun 7, 2016 by slow_learner