Skip to main content

Return to Question

added 4 characters in body
Source Link
dee-see
dee-see
  • 24.1k
  • 6
  • 63
  • 92

I have a MySQL database which contains a table of users. The primary key of the table is 'userid', which is set to be an auto increment field.

What I'd like to do is when I insert a new user into the table is to use the same value that the auto increment is creating in the 'userid' field in a different field, 'default_assignment'.

e.g.

I'd like a statement like this:

INSERT INTO users ('username','default_assignment') VALUES ('barry', value_of_auto_increment_field())

INSERT INTO users ('username','default_assignment') VALUES ('barry', value_of_auto_increment_field())

so I create user 'Barry', the 'userid' is generated as being 16 (for example), but I also want the 'default_assignment' to have the same value of 16.

Is there any way to achieve this please?

Thanks!

Update:

Thanks for the replies. The default_assignment field isn't redundant. The default_assigment can reference any user within the users table. When creating a user I already have a form that allows a selection of another user as the default_assignment, however there are cases where it needs to be set to the same user, hence my question.

Update:

Ok, I've tried out the update triggers suggestion but still can't get this to work. Here's the trigger I've created:

CREATE TRIGGER default_assignment_self BEFORE INSERT ON `users` FOR EACH ROW BEGIN SET NEW.default_assignment = NEW.userid; END;

When inserting a new user however the default_assignment is always set to 0.

If I manually set the userid then the default_assignment does get set to the userid.

Therefore the auto assignment generation process clearly happens after the trigger takes effect.

I have a MySQL database which contains a table of users. The primary key of the table is 'userid', which is set to be an auto increment field.

What I'd like to do is when I insert a new user into the table is to use the same value that the auto increment is creating in the 'userid' field in a different field, 'default_assignment'.

e.g.

I'd like a statement like this:

INSERT INTO users ('username','default_assignment') VALUES ('barry', value_of_auto_increment_field())

so I create user 'Barry', the 'userid' is generated as being 16 (for example), but I also want the 'default_assignment' to have the same value of 16.

Is there any way to achieve this please?

Thanks!

Update:

Thanks for the replies. The default_assignment field isn't redundant. The default_assigment can reference any user within the users table. When creating a user I already have a form that allows a selection of another user as the default_assignment, however there are cases where it needs to be set to the same user, hence my question.

Update:

Ok, I've tried out the update triggers suggestion but still can't get this to work. Here's the trigger I've created:

CREATE TRIGGER default_assignment_self BEFORE INSERT ON `users` FOR EACH ROW BEGIN SET NEW.default_assignment = NEW.userid; END;

When inserting a new user however the default_assignment is always set to 0.

If I manually set the userid then the default_assignment does get set to the userid.

Therefore the auto assignment generation process clearly happens after the trigger takes effect.

I have a MySQL database which contains a table of users. The primary key of the table is 'userid', which is set to be an auto increment field.

What I'd like to do is when I insert a new user into the table is to use the same value that the auto increment is creating in the 'userid' field in a different field, 'default_assignment'.

e.g.

I'd like a statement like this:

INSERT INTO users ('username','default_assignment') VALUES ('barry', value_of_auto_increment_field())

so I create user 'Barry', the 'userid' is generated as being 16 (for example), but I also want the 'default_assignment' to have the same value of 16.

Is there any way to achieve this please?

Thanks!

Update:

Thanks for the replies. The default_assignment field isn't redundant. The default_assigment can reference any user within the users table. When creating a user I already have a form that allows a selection of another user as the default_assignment, however there are cases where it needs to be set to the same user, hence my question.

Update:

Ok, I've tried out the update triggers suggestion but still can't get this to work. Here's the trigger I've created:

CREATE TRIGGER default_assignment_self BEFORE INSERT ON `users` FOR EACH ROW BEGIN SET NEW.default_assignment = NEW.userid; END;

When inserting a new user however the default_assignment is always set to 0.

If I manually set the userid then the default_assignment does get set to the userid.

Therefore the auto assignment generation process clearly happens after the trigger takes effect.

Update after unsucessfully trying out an insert trigger
Source Link
TheKeys
TheKeys
  • 685
  • 2
  • 7
  • 14

I have a MySQL database which contains a table of users. The primary key of the table is 'userid', which is set to be an auto increment field.

What I'd like to do is when I insert a new user into the table is to use the same value that the auto increment is creating in the 'userid' field in a different field, 'default_assignment'.

e.g.

I'd like a statement like this:

INSERT INTO users ('username','default_assignment') VALUES ('barry', value_of_auto_increment_field())

so I create user 'Barry', the 'userid' is generated as being 16 (for example), but I also want the 'default_assignment' to have the same value of 16.

Is there any way to achieve this please?

Thanks!

Update:Update:

Thanks for the replies. The default_assignment field isn't redundant. The default_assigment can reference any user within the users table. When creating a user I already have a form that allows a selection of another user as the default_assignment, however there are cases where it needs to be set to the same user, hence my question.

Update:

Ok, I've tried out the update triggers suggestion but still can't get this to work. Here's the trigger I've created:

CREATE TRIGGER default_assignment_self BEFORE INSERT ON `users` FOR EACH ROW BEGIN SET NEW.default_assignment = NEW.userid; END;

When inserting a new user however the default_assignment is always set to 0.

If I manually set the userid then the default_assignment does get set to the userid.

Therefore the auto assignment generation process clearly happens after the trigger takes effect.

I have a MySQL database which contains a table of users. The primary key of the table is 'userid', which is set to be an auto increment field.

What I'd like to do is when I insert a new user into the table is to use the same value that the auto increment is creating in the 'userid' field in a different field, 'default_assignment'.

e.g.

I'd like a statement like this:

INSERT INTO users ('username','default_assignment') VALUES ('barry', value_of_auto_increment_field())

so I create user 'Barry', the 'userid' is generated as being 16 (for example), but I also want the 'default_assignment' to have the same value of 16.

Is there any way to achieve this please?

Thanks!

Update:

Thanks for the replies. The default_assignment field isn't redundant. The default_assigment can reference any user within the users table. When creating a user I already have a form that allows a selection of another user as the default_assignment, however there are cases where it needs to be set to the same user, hence my question.

I have a MySQL database which contains a table of users. The primary key of the table is 'userid', which is set to be an auto increment field.

What I'd like to do is when I insert a new user into the table is to use the same value that the auto increment is creating in the 'userid' field in a different field, 'default_assignment'.

e.g.

I'd like a statement like this:

INSERT INTO users ('username','default_assignment') VALUES ('barry', value_of_auto_increment_field())

so I create user 'Barry', the 'userid' is generated as being 16 (for example), but I also want the 'default_assignment' to have the same value of 16.

Is there any way to achieve this please?

Thanks!

Update:

Thanks for the replies. The default_assignment field isn't redundant. The default_assigment can reference any user within the users table. When creating a user I already have a form that allows a selection of another user as the default_assignment, however there are cases where it needs to be set to the same user, hence my question.

Update:

Ok, I've tried out the update triggers suggestion but still can't get this to work. Here's the trigger I've created:

CREATE TRIGGER default_assignment_self BEFORE INSERT ON `users` FOR EACH ROW BEGIN SET NEW.default_assignment = NEW.userid; END;

When inserting a new user however the default_assignment is always set to 0.

If I manually set the userid then the default_assignment does get set to the userid.

Therefore the auto assignment generation process clearly happens after the trigger takes effect.

updated reasons for having a default_assignment field
Source Link
TheKeys
TheKeys
  • 685
  • 2
  • 7
  • 14

I have a MySQL database which contains a table of users. The primary key of the table is 'userid', which is set to be an auto increment field.

What I'd like to do is when I insert a new user into the table is to use the same value that the auto increment is creating in the 'userid' field in a different field, 'default_assignment'.

e.g.

I'd like a statement like this:

INSERT INTO users ('username','default_assignment') VALUES ('barry', value_of_auto_increment fieldvalue_of_auto_increment_field())

so I create user 'Barry', the 'userid' is generated as being 16 (for example), but I also want the 'default_assignment' to have the same value of 16.

Is there any way to achieve this please?

Thanks!

Update:

Thanks for the replies. The default_assignment field isn't redundant. The default_assigment can reference any user within the users table. When creating a user I already have a form that allows a selection of another user as the default_assignment, however there are cases where it needs to be set to the same user, hence my question.

I have a MySQL database which contains a table of users. The primary key of the table is 'userid', which is set to be an auto increment field.

What I'd like to do is when I insert a new user into the table is to use the same value that the auto increment is creating in the 'userid' field in a different field, 'default_assignment'.

e.g.

I'd like a statement like this:

INSERT INTO users ('username','default_assignment') VALUES ('barry', value_of_auto_increment field())

so I create user 'Barry', the 'userid' is generated as being 16 (for example), but I also want the 'default_assignment' to have the same value of 16.

Is there any way to achieve this please?

Thanks!

I have a MySQL database which contains a table of users. The primary key of the table is 'userid', which is set to be an auto increment field.

What I'd like to do is when I insert a new user into the table is to use the same value that the auto increment is creating in the 'userid' field in a different field, 'default_assignment'.

e.g.

I'd like a statement like this:

INSERT INTO users ('username','default_assignment') VALUES ('barry', value_of_auto_increment_field())

so I create user 'Barry', the 'userid' is generated as being 16 (for example), but I also want the 'default_assignment' to have the same value of 16.

Is there any way to achieve this please?

Thanks!

Update:

Thanks for the replies. The default_assignment field isn't redundant. The default_assigment can reference any user within the users table. When creating a user I already have a form that allows a selection of another user as the default_assignment, however there are cases where it needs to be set to the same user, hence my question.

Source Link
TheKeys
TheKeys
  • 685
  • 2
  • 7
  • 14