Ms sql trigger to fire a php script

Dev Shed Forums Sponsor:

January 4th, 2013, 10:34 AM

jreis

Contributing User

Join Date: Dec 2005

Posts: 341

Time spent in forums: 3 Days 4 h 46 m 42 sec
Reputation Power: 8

Ms sql trigger to fire a php script

I have an issue I am hoping someone might be able to assist with

I have a ms sql database with a trigger that runs when their is an insert on a specific table. What I am trying to do is when their is a new row inserted to run a php script on the server which is a windows server by the way. the php file writes some data to an external Filemaker database

My issue is I am not to experience with triggers and do not think that the trigger is running the php script. When I execute the trigger manually within microsoft sql server management studio express I get a success message but I don't the the php file is actually being run. I can run the php file manually from the command line and have it successfully insert data into Filemaker

Wondering if I post below my trigger if someone might spot an issue that maybe I am missing? Again this is a windows server but php works fine because I can run the php file from the command line and it inserts the data into Filemaker however if I manually execute the trigger in the ms sql studio express program I get a success but no data makes it to my Filemaker?

Here is my trigger

Code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER TRIGGER [dbo].[cardholders]
   ON [dbo].[ALARM]
   AFTER INSERT
AS 
BEGIN
DECLARE @CARDPIN VARCHAR(255)
DECLARE @CARDNUMBER VARCHAR(255)
DECLARE @FIRSTNAME VARCHAR(255)
DECLARE @LASTNAME VARCHAR(255)
DECLARE @EVENT_TIMESTAMP VARCHAR(255)
DECLARE @CMD VARCHAR(255)
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
SELECT   @CARDNUMBER=  ALARM.ALARM_CARDNUMBER, 
                    @FIRSTNAME=  ALARM.ALARM_CARDFIRSTNAME, @LASTNAME=ALARM.ALARM_CARDLASTNAME,@EVENT_TIMESTAMP=CONVERT(VARCHAR(23),ALARM.ALARM_DATETIME, 121),@CARDPIN= CARD.CARD_PIN
FROM         ALARM LEFT OUTER JOIN
                      CARD ON CARD.CARD_NUMBER = ALARM.ALARM_CARDNUMBER AND CARD.CARD_DELETED = 'false' where  alarm.alarm_id =(select max(alarm_id) from alarm)

    -- Insert statements for trigger here 
SET @CMD = 'C:\phpfiles>php c:\php\filemakerexport.php "firstname='+@FIRSTNAME+'" "lastname='+@LASTNAME+'" "EventTimestamp='+@EVENT_TIMESTAMP+'" "cardnumber='+@CARDNUMBER+'" "cardpin='+@CARDPIN+'"'

EXEC xp_cmdshell @CMD

END

January 11th, 2013, 08:21 AM

Hoser01

Registered User

Join Date: Jan 2013

Posts: 1

Time spent in forums: 14 m 57 sec
Reputation Power: 0

I believe your problem is with the CMD executing the trigger.

Code:

SET @CMD = 'C:\phpfiles>php c:\php\filemakerexport.php "firstname='+@FIRSTNAME+'" "lastname='+@LASTNAME+'" "EventTimestamp='+@EVENT_TIMESTAMP+'" "cardnumber='+@CARDNUMBER+'" "cardpin='+@CARDPIN+'"'

Try something along the lines of:

Code:

set @cmd = 'cmd.exe /C "c:\php\filemakerexport.php... ...

You may have to change your working directory first.

Cheers,

Chris

Ms sql trigger to fire a php script

Developer Shed Advertisers and Affiliates