在PostSresSql的MySql中更改触发器？ [英] Change a trigger in MySql for PostgresSql?

问题描述

我试图将此触发器代码从MySql更改为Postgres语法

分隔符$$ 
 创建 触发器 trggBeforeInsertTUser  insert   on  TUsuario  FOR 每行
 开始 
  set  @LastCod =（ select  max（codUsr）来自 TUsuario） ; 
 如果  @ LastCod     null  然后 
  set  @ LastCod =   USRX0000000; 
  end   if ; 
  set  @ partText = mid（ @ LastCod ， 1 ， 8 ）; 
  set  @ partNum = mid（ @ LastCod ， 9 ， 7 ）+ 1; 
  set  @longNumber =（ select  length（ @partNum ））; 
  set  @ codNum = concat（repeat（'  0'，7- @ longNumber）， @ partNum ）; 
  set  @ codNum = concat（ @ partText ， @codNum ）; 
  set  NEW.codUsr =（ select   @ codNum ）; 
  end  

to postgres syntax

  CREATE   TRIGGER  trggBeforeInsertTUser之前 INSERT    TUser 
  EXECUTE  程序 sp_incremental（）; 
 
  CREATE  或 REPLACE  FUNCTION  sp_incremental（） RETURNS   TRIGGER   AS  trggBeforeInsertTUser 
  BEGIN  
  set  LastCod =（选择 max（codUser）来自 TUser）; 
 如果 LastCod    null  然后 
  set  LastCod =   USRX0000000; 
  end   if ; 
  set  partText = mid（LastCod， 1 ， 8 ）; 
  set  partNum = mid（LastCod， 9 ， 7 ）+ 1; 
  set  longNumber =（ select  length（partNum））; 
  set  codNum = concat（repeat（'  0'，7-longNumber），partNum）; 
  set  cod = concat（partText，codNum）; 
  set  NEW.codUsr =（ select  cod）; 
  END  

没有成功......有什么帮助吗？ ？

解决方案

创建 触发器 trggBeforeInsertTUser之前插入 TUsuario FOR EACH ROW
开始
set @LastCod =（从 TUsuario中选择 max（codUsr）;
如果 @ LastCod null 然后
set @ LastCod = USRX0000000;
end if ;
set @ partText = mid（ @ LastCod ， 1 ， 8 ）;
set @ partNum = mid（ @ LastCod ， 9 ， 7 ）+ 1;
set @longNumber =（ select length（ @partNum ））;
set @ codNum = concat（repeat（' 0'，7- @ longNumber）， @ partNum ）;
set @ codNum = concat（ @ partText ， @codNum ）;
set NEW.codUsr =（ select @ codNum ）;
end

to postgres syntax

  CREATE   TRIGGER  trggBeforeInsertTUser之前 INSERT    TUser 
  EXECUTE  程序 sp_incremental（）; 
 
  CREATE  或 REPLACE  FUNCTION  sp_incremental（） RETURNS   TRIGGER   AS  trggBeforeInsertTUser 
  BEGIN  
  set  LastCod =（选择 max（codUser）来自 TUser）; 
 如果 LastCod    null  然后 
  set  LastCod =   USRX0000000; 
  end   if ; 
  set  partText = mid（LastCod， 1 ， 8 ）; 
  set  partNum = mid（LastCod， 9 ， 7 ）+ 1; 
  set  longNumber =（ select  length（partNum））; 
  set  codNum = concat（repeat（'  0'，7-longNumber），partNum）; 
  set  cod = concat（partText，codNum）; 
  set  NEW.codUsr =（ select  cod）; 
  END  

没有成功......有什么帮助吗？ ？

hi, i´ve trying to change this trigger code from MySql to Postgres syntax

delimiter $$
create trigger trggBeforeInsertTUser before insert on TUsuario FOR EACH ROW
begin
set @LastCod=(select max(codUsr) from TUsuario);
if @LastCod is null then
	set @LastCod="USRX0000000";
end if;
set @partText=mid(@LastCod, 1, 8);
set @partNum=mid(@LastCod, 9, 7)+1;
set @longNumber=(select length(@partNum));
set @codNum=concat(repeat('0', 7-@longNumber), @partNum);
set @codNum=concat(@partText, @codNum);
set NEW.codUsr=(select @codNum);
end			

to postgres syntax

CREATE TRIGGER trggBeforeInsertTUser BEFORE INSERT on TUser 
  EXECUTE PROCEDURE sp_incremental();

CREATE OR REPLACE FUNCTION sp_incremental () RETURNS TRIGGER AS trggBeforeInsertTUser
BEGIN
set LastCod=(select max(codUser) from TUser);
if LastCod is null then
	set LastCod="USRX0000000";
end if;
set partText=mid(LastCod, 1, 8);
set partNum=mid(LastCod, 9, 7)+1;
set longNumber=(select length(partNum));
set codNum=concat(repeat('0', 7-longNumber), partNum);
set cod=concat(partText, codNum);
set NEW.codUsr=(select cod);
END

without success... any help??

解决方案

create trigger trggBeforeInsertTUser before insert on TUsuario FOR EACH ROW begin set @LastCod=(select max(codUsr) from TUsuario); if @LastCod is null then set @LastCod="USRX0000000"; end if; set @partText=mid(@LastCod, 1, 8); set @partNum=mid(@LastCod, 9, 7)+1; set @longNumber=(select length(@partNum)); set @codNum=concat(repeat('0', 7-@longNumber), @partNum); set @codNum=concat(@partText, @codNum); set NEW.codUsr=(select @codNum); end

to postgres syntax

CREATE TRIGGER trggBeforeInsertTUser BEFORE INSERT on TUser 
  EXECUTE PROCEDURE sp_incremental();

CREATE OR REPLACE FUNCTION sp_incremental () RETURNS TRIGGER AS trggBeforeInsertTUser
BEGIN
set LastCod=(select max(codUser) from TUser);
if LastCod is null then
	set LastCod="USRX0000000";
end if;
set partText=mid(LastCod, 1, 8);
set partNum=mid(LastCod, 9, 7)+1;
set longNumber=(select length(partNum));
set codNum=concat(repeat('0', 7-longNumber), partNum);
set cod=concat(partText, codNum);
set NEW.codUsr=(select cod);
END

without success... any help??

这篇关于在PostSresSql的MySql中更改触发器？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！