Click here to Skip to main content
15,867,834 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hi, i´ve trying to change this trigger code from MySql to Postgres syntax
SQL
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
SQL
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??
Posted

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900