CODEWITHSUNDEEP
mysql
user3743374
user3743374

Reputation: 11

MySQL, trigger that generates primary key before insert a row

I'm trying to generate a primary key based on current date and time,

Here's my trigger:

DELIMITER $$
CREATE TRIGGER cuenta
BEFORE UPDATE ON cuenta
FOR EACH ROW
BEGIN
  INSERT INTO cuenta VALUES (NULL);
  SET NEW.NoCuenta = DATE_FORMAT(NOW(), '%000%d%m%y%h%i%s');
END$$
DELIMITER ;

My table cuenta:

NoCuenta, varchar(15), NOT NULL.
TipoCuenta, varchar(7),
saldo, float
IDCliente, int(8).

Then I try to insert:

insert into banco.cuenta(TipoCuenta, saldo, IDCliente)
    VALUES('Debito','12500.5','1');

The result: 1 row(s) affected, 1 warning(s): 1364 Field 'NoCuenta' doesn't have a default value But when I type select*from cuenta... NoCuenta field is empty.

And then when I try to insert a new row, it shows this: Error Code: 1062. Duplicate entry '' for key 'PRIMARY'

Please HELP!!

Upvotes: 1

Views: 672

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

Why are you inserting into the table in the trigger? Just try this:

DELIMITER $$
CREATE TRIGGER cuenta
BEFORE UPDATE ON cuenta
FOR EACH ROW
BEGIN
  SET NEW.NoCuenta = DATE_FORMAT(NOW(), '%000%d%m%y%h%i%s');
END$$
DELIMITER ;

Upvotes: 1

Related Questions