Page 1 of 1
SQL triggers and php
Posted: Wed Feb 22, 2012 5:00 am
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.
Re: SQL triggers and php
Posted: Wed Feb 22, 2012 10:20 am
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