1

My problem is as follows.

I an creating a MySQL database for a company that whenever a row is updated on the DB i want to have a trigger that calls a cURL request to transfer the updated information to our clients.

Is doing this the best way to do this? or is it better to do this in the php script that transfers the data to us?

Thank you for your time and experience.

  • This sounds like some kind of auditing, the best solution is likely dependent of the DBMS you use. – Lennart Jul 28 '16 at 20:22
  • Im running a SQL database, just updated the description – Krunkmaster Jul 28 '16 at 20:51
  • SQL is a language that many DBMS understands. The language itself is fairly standard even though it differs slightly between different vendors. However, things like auditing is not described in the SQL standard and is implemented very different between vendors, so you need to tag your question with the vendor that you are using. With SQL database, do you mean Microsoft SQL Server? – Lennart Jul 28 '16 at 20:55
  • yes it is a microsoft SQL server, its hosted on 1and1.com. i really dont know the differences between just a regular sql server and a microsoft one.. i thought all SQL servers were microsoft – Krunkmaster Jul 28 '16 at 21:17
1

first create one temp table , and create records whenever your updating row. for example if you are updating one row with id=24, save this id as refference id into temp table.then using Php if temp table contains any records call curl request and delete temp records once curl request is completed. by using cron jobs run this php scripts repeatedly

  • Thank you for your answer Naisa. I'm unfamiliar with database design, my experience is all front end development – Krunkmaster Jul 28 '16 at 19:02
  • it is not a big thing,how you are updating rows through code or through api's directly you are updating to db? if it code after update query use Insert query to insert temp table with timestamp. if it is through api, ask them to insert record in temp table – Naisa purushotham Jul 29 '16 at 2:52
0

If your database is Oracle, the best way to do this is to create a packaged procedure encapsulating everything you want to happen. This would include both inserting the row and doing the http request. The Oracle user used by the php script can then be given execute privileges on this package allowing the script to call the procedure. Not only is this more clear, flexible, and secure, it also takes full advantage of the database software and avoids triggers.

  • Thank you for the advice, i'm on a SQL server. Is a procedure like that possible on that kind of server? – Krunkmaster Jul 28 '16 at 20:42
  • @Krunkmaster I believe so, but have never worked on SQL server. – Leigh Riffel Jul 29 '16 at 13:03
  • Thank you for your time Leigh, i appreciate the response – Krunkmaster Jul 29 '16 at 16:12
0

I agree with the basic idea that Naisa had, but I would use a 'real' table instead of a 'temp' table in case the server goes down before you get a chance to process the rows. In addition, you might want to add a column with the sysdatetime() so you can process the rows in the 'order' they were presented to the system.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.