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 the primary keys, yes, if you expect large scale data inside your system that would be the initial setup that will fit you
  • Changes also in the foreign key part of you USER_ACCOUNT table, should comply with the related key of the reference table's column, and should also cascade for any changes
  • Changed UserAccountBalence into UserAccountBalance, and also the datatype to accommodate the currency value that will be saved in the said column
  • Regarding the UserEmail column in your USERS table, changed in into 50 from 30, you need to give the application some breathing room incase validation suddenly failed
  • Updated the trigger area to also save the key that you will use in your foreign key
Ad
source: stackoverflow.com
Ad