SQL triggers and php

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
markyboy17
Forum Newbie
Posts: 7
Joined: Mon Jan 16, 2012 3:38 am

SQL triggers and php

Post by markyboy17 »

Code: Select all

CREATE TRIGGER [dbo].[CreditDetailUpdateTrg]
ON [dbo].[CreditDetail] AFTER UPDATE
AS 
   INSERT INTO dbo.CreditDetailLog(DetailID, Old_Status, New_Status,Old_InvoiceNumber,New_InvoiceNumber)
      SELECT i.DetailID, d.[Status], i.[Status],d.InvoiceNumber,i.InvoiceNumber
      FROM Inserted i
      LEFT JOIN Deleted d ON i.DetailID = d.DetailID
This is a trigger i have in sql which basically logs any updates to a table the problem is i need to know how i can log the user who did that update.
In the php i have a username in a session so this is available at every point
my question is how do i pass the username into the log table when they do an update to that table.
I should probably mention that the update is done through the php. so this might make it easier.
Could i maybe pass it into the trigger as a parameter.
PeterScott
Forum Newbie
Posts: 1
Joined: Wed Feb 22, 2012 10:07 am

Re: SQL triggers and php

Post by PeterScott »

AS far as I'm aware, SQL and php servers run seperately (even if they are hosted on the same physical server). As such, you can't call up the php Session variable from the SQL trigger.

One thing you could do is add an extra column into your CreditDetail table and title it something like 'last_user'.
That way you can submit the username in the initial SQL statement and save it to that column in the table - this would allow you to call it from the SQL trigger.

However, I don't think there is any direct method of getting the user details without them being part of the initial SQL Statement.

Peter
Post Reply