My SQL triggers

Question

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?

Answer 1

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());

Answer 2

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.

Answer 3

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: http://us2.php.net/manual/en/function.mysql-insert-id.php

Answer 4

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