Tech Support Guy banner
  • Please post in our Community Feedback thread for help with the new forum software! If you are having trouble logging in, please Contact Us for assistance.
Status
Not open for further replies.
1 - 2 of 2 Posts

·
Registered
Joined
·
48 Posts
Discussion Starter · #1 ·
I have an MS SQL database with a web front end. I'm using PHP to write values to and retrieve values from the database via an ODBC connection.

After I insert a row into one table, I need to retrieve the row's primary key (PreOpKey) so that I can use it to insert other data into other tables. Since it's an ODBC connection I can't have an ident_current statement tag along with my INSERT, so I want to create a trigger that will return the PreOpKey to my PHP.

Here's what I have so far:

CREATE TRIGGER NewPatientID ON [dbo].[PreOpVisit]
FOR INSERT
AS
DECLARE @PreOpKey INTEGER;
SELECT @PreOpKey = (SELECT IDENT_CURRENT('PreOpVisit'));

And here's the chunk of PHP code:

$query1 = "INSERT INTO PreOpVisit
(PatientKey, VisitDate...)
VALUES
('$PatientKey', '$VisitDate'...)";

$result1 = odbc_exec($_SERVER['connection'], $query1) or die ("Error in Insert PreOp query: ".odbc_errormsg());

$row1 = odbc_fetch_array($result1);

print "New PreOpKey: ".$row1['$PreOpKey'];

The INSERT works fine but I can't get the $PreOpKey from the trigger. Any thoughts?
Thanks,
Lisa
 

·
Registered
Joined
·
6,649 Posts
Hi Pea****,

I'm a bit confused here because a trigger is used by the database management system for 'triggered' updates, insertions or deletions of tables. Also, it is not the role of a trigger to return values. You should instead use SQL functions.
 
1 - 2 of 2 Posts
Status
Not open for further replies.
Top