Ad
Totally A MySQL Trigger Happiness Issue
So I create my lil DB and then use the thing. I add two tables and now all I wanna do is this: when a user is inserted into the USERS table, also insert some default info into USER_ACCOUNT table that corresponds to the newly inserted user. Clearly I'm doing something wrong but I don't know what... Thanks in advance. =)
CREATE DATABASE REST_PROJECT;
USE REST_PROJECT;
CREATE TABLE USERS (
UserID INT Primary Key AUTO_INCREMENT NOT NULL,
UserEmail VARCHAR(30),
UserPassword VARCHAR(30)
);
CREATE TABLE USER_ACCOUNT (
UserAccountID INT Primary Key AUTO_INCREMENT NOT NULL,
OwnerUserID INT,
UserAccountName VARCHAR(30),
UserAccountType VARCHAR(10) NOT NULL,
UserAccountBalence INT,
FOREIGN KEY (OwnerUserID) REFERENCES USERS(UserID)
);
DELIMITER //
CREATE TRIGGER makeDefaultUserAccount
AFTER INSERT ON USERS
For each row
BEGIN
INSERT INTO USER_ACCOUNT
(UserAccountName, UserAccountType, UserAccountBalence)
values ('Default Account','default', 100);
END; //
Ad
Answer
CREATE DATABASE REST_PROJECT;
USE REST_PROJECT;
CREATE TABLE `USERS` (
UserID BIGINT(20) UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
UserEmail VARCHAR(50),
UserPassword VARCHAR(30)
);
CREATE TABLE `USER_ACCOUNT` (
UserAccountID BIGINT(20) UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
UserID BIGINT(20) UNSIGNED NOT NULL,
UserAccountName VARCHAR(30),
UserAccountType VARCHAR(10) NOT NULL,
UserAccountBalance DECIMAL(19,6),
CONSTRAINT `fk_USER_ACCOUNT_UserID` FOREIGN KEY (`UserID`)
REFERENCES `USERS`.`UserID` (`UserID`)
ON DELETE CASCADE
ON UPDATE CASCADE
);
DELIMITER //
CREATE TRIGGER `makeDefaultUserAccount`
AFTER INSERT ON `USERS`
FOR EACH ROW BEGIN
INSERT INTO USER_ACCOUNT (OwnerUserID, UserAccountName, UserAccountType, UserAccountBalance)
VALUES (NEW.UserID, 'Default Account','default', 100);
END//
DELIMITER ;
Notes
- Notice the changes in the
datatype
of theprimary keys
, yes, if you expectlarge
scale data inside your system that would be theinitial setup
that will fit you - Changes also in the
foreign key
part of youUSER_ACCOUNT
table, should comply with the related key of thereference table's column
, and should alsocascade
for any changes - Changed
UserAccountBalence
intoUserAccountBalance
, and also thedatatype
to accommodate thecurrency value
that will be saved in the said column - Regarding the
UserEmail
column in yourUSERS
table, changed in into50
from30
, you need to give the application some breathing room incasevalidation
suddenly failed - Updated the trigger area to also save the
key
that you will use in yourforeign key
Ad
source: stackoverflow.com
Related Questions
- → I can't do a foreign key, constraint error
- → How to implement DbDongle::convertTimestamps as workaround of invalid timestamps with MySql strict
- → MySQL error "Foreign key constraint is incorrectly formed"
- → Eloquent Multitable query
- → "Laravel 5.1" add user and project with userId
- → Database backup with custom code in laravel 5 and get the data upto 10 rows from per table in database
- → Laravel 5.1 QueryException when trying to delete a project
- → Using Array in '->where()' for Laravel Query Building
- → Chaining "Count of Columns" of a Method to Single Query Builder
- → Laravel Eloquent Joining Strange query
- → convert time using mysql laravel 5
- → How to update a column after an expiration date in MySQL?
- → Foreign key constraint fails on existing key
Ad