CODEWITHSUNDEEP
phpmysqltriggers
hitek
hitek

Reputation: 372

My SQL triggers

Is it possible to set up a mysql trigger that fires back the id number of that record when ever there is an insert into the database AND how do i capture that using php?

Upvotes: 3

Views: 533

Answers (4)

achinda99
achinda99

Reputation: 5078

As explained in the previous answers, you'd don't need to use a trigger to return the identity. You can use the mysql_insert_id() command as described in the [documentation][1].

However if you need to use the new insert id in a trigger, use NEW.[identity_column_name] as follows:

CREATE TABLE temp (
    temp_id int auto_increment,
    value varchar(10),
    PRIMARY_KEY(temp_id)
);

CREATE TRIGGER after_insert_temp AFTER INSERT ON temp
FOR EACH ROW 
BEGIN
    DECLARE @identity;
    SET @identity = NEW.temp_id;
    -- do something with @identity
END

Upvotes: 2

antik
antik

Reputation: 5330

You do not need a trigger to accomplish what you are trying to do.

Simply calling PHP's mysql_insert_id will return the ID generated from the last INSERT query executed.

Described here: https://www.php.net/manual/en/function.mysql-insert-id.php

Upvotes: 0

Peter Bailey
Peter Bailey

Reputation: 105878

Unless I don't fully understand your question, you don't need a trigger for this - just use the "last inserted ID" functionality of your database driver.

Here's an example using the basic mysql driver in PHP.

<?php

$db = mysql_connect( 'localhost', 'user', 'pass' );

$result = mysql_query( "insert into table (col1, col2) values ('foo', 'bar')", $db );

$lastId = mysql_insert_id();

This is a connection-safe way to obtain the ID.

Upvotes: 3

user64718
user64718

Reputation: 827

I'm not really sure what you're asking. Are you wanting to insert a row into the database and get the id it was assigned too? If so do this

printf("Last inserted record has id %d\n", mysql_insert_id());

Upvotes: 1

Related Questions