Trigger in mysql copying data in the same table

Asked

Viewed 147 times

-2

Good Afternoon,

I have a table that contains the id_document (int) AI fields, username and nosso_numero(int). I would like to use a Trigger if it is possible that after I insert a new document the field our number would receive the field id_formatted document type LPAD(id_document, 8, '0'). I would like a help to develop this visa Trigger q are inserted several documents at a time in a single action. From now on I appreciate any help.

I’ve tried to

If nosso_numero is null then 
UPDATE tb_documentos_emitidos SET nosso_numero = LPAD(id_documento, 8, '0') 
WHERE tb_documentos.nosso_numero = null ; 
end if

Now I’ve tried

CREATE
DEFINER=`root`@`localhost`
TRIGGER `amoruna`.`tgr_nossonumero`
BEFORE INSERT ON `amoruna`.`tb_documentos_emitidos`
FOR EACH ROW
If (NEW.nosso_numero IS NULL) THEN
        SET NEW.nosso_numero = (LPAD(NEW.id_documentos, 8, '0'));
end if

Works if I enter the value manually from the id_documents field.

But the field is auto-incremented and if it is set the regisro automatically does not work.

mysql sql
  • 1

    puts what you’ve tried

  • 1

    puts in old question. any information that can help answer your question has that is there

1 answer

0

SOLVED AND SHARING

JUST REPLACE THE FIELD ID DOCUMENTO FOR 

select auto_increment from information_schema.tables 
where table_name = 'tb_documentos_emitidos' and table_schema = 'amoruna'

AI AI THE INSTRUCTION IS THUS

If (NEW.nosso_numero IS NULL) THEN
        SET NEW.nosso_numero = (LPAD((select auto_increment from information_schema.tables where table_name = 'tb_documento_emitidos' and table_schema = 'amoruna'), 8, '0'));

this statement always returns the next auto increment number. I hope I helped.

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.