#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2005
    Posts
    341
    Rep Power
    9

    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
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    1
    Rep 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

IMN logo majestic logo threadwatch logo seochat tools logo