-
hi,
I've 2 columns SITE_ID and COD_CONV (default '____-_-________' varchar2(15)).
When I update SITE_ID I must insert automatically into col COD_CONV this value progressive:
_000
_001
_002
....
_A00
_A01
....
_ZZZ
after I must fill up a table with max value update.
I tried with this two trigger:
create or replace TRIGGER ON_SITE_ID
BEFORE INSERT OR UPDATE OF
SITE_ID
ON BL
REFERENCING
NEW AS NEW
OLD AS OLD
FOR EACH ROW
DECLARE COD_SITO VARCHAR2(4);
COD1 CHAR(1);
COD2 CHAR(1);
COD3 CHAR(1);
Begin
SELECT COD_CONV INTO COD_SITO FROM MAX_VAL;
COD1 := SUBSTR(COD_SITO,4,1);
COD2 := SUBSTR(COD_SITO,3,1);
COD3 := SUBSTR(COD_SITO,2,1);
IF COD3='_' THEN COD3:= '0';
END IF;
IF COD2='_' THEN COD2:= '0';
END IF;
IF COD1='_' THEN COD1:= '0';
END IF;
IF COD1='Z' THEN
COD1 := '0';
COD2 := SUBSTR('0123456789ABCDEFGHIJKLMNOPQRSTUVWYXZ0',INSTR('0123456789ABCDEFGHIJKLMNO PQRSTUVWYXZ0',SUBSTR(COD_SITO,2,1)) 1,1);
IF COD2 = 'Z' THEN
COD2 := '0';
COD3 := SUBSTR('0123456789ABCDEFGHIJKLMNOPQRSTUVWYXZ0',INSTR('0123456789ABCDEFGHIJKLMNO PQRSTUVWYXZ0',SUBSTR(COD_SITO,3,1)) 1,1);
ELSE
COD2 := SUBSTR('0123456789ABCDEFGHIJKLMNOPQRSTUVWYXZ0',INSTR('0123456789ABCDEFGHIJKLMNO PQRSTUVWYXZ0',SUBSTR(COD_SITO,2,1)) 1,1);
END IF;
ELSE
COD1 := SUBSTR('0123456789ABCDEFGHIJKLMNOPQRSTUVWYXZ0',INSTR('0123456789ABCDEFGHIJKLMNO PQRSTUVWYXZ0',SUBSTR(COD_SITO,1,1)) 1,1);
END IF;
SELECT '_'||COD3||COD2||COD1||'-' INTO :NEW.COD_CONV FROM DUAL;
End;
create or replace TRIGGER POST_COD_CONV
AFTER INSERT OR UPDATE OF
COD_CONV
ON BL
FOR EACH ROW
Begin
UPDATE MAX_VAL
SET COD_CONV = :NEW.COD_CONV;
End;
but when I update SITE_ID, I see only _000 in COD_CONV
What I wrong???
Thanks
Raf
-
Better try the following:
1) Create a sequence:
Create sequence my_seq
minvalue 0 start with 0;
2) Create the following function:
create or replace
function alpha_seq(sq number)
return varchar2 is
a Varchar2(40):='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
l pls_integer;
s1 pls_integer;
d1 pls_integer:=0;
d2 pls_integer:=0;
d3 pls_integer:=0;
i1 pls_integer:=0;
i2 pls_integer:=0;
i3 pls_integer:=0;
Begin
l:=length(a);
s1:=sq;
i1:=trunc(s1/l);
d1:=mod(s1,l);
if i1 > 0 then
s1:=s1-i1*l;
i2:=trunc(s1/l);
d2:=mod(s1,l);
if i2 > 0 then
s1:=s1-i2*l;
i1:=trunc(s1/l);
d3:=mod(s1,l);
end if;
end if;
return '_'||substr(a,d3+1,1)
||substr(a,d2+1,1)
||substr(a,d1+1,1);
end;
/
3) Create the trigger:
Create Or Replace Trigger On_Site_Id
Before Insert Or Update Of Site_Id
On Bl For Each Row
Declare
Sq Number;
Begin
Select My_Seq.Nextval Into Sq From Dual;
:New.Cod_Conv := Alpha_Seq(Sq);
End;
-
ok,
but the result is
SITE_ID COD_CONV
-------------------------------- ---------------
03022038 _000
05035238 _002
02014837 _007
12034041 ____-_-________
02607812 _009
07601955 ____-_-________
02050658 _00D
02034645 ____-_-________
02607796 ____-_-________
not
_000
_001
_002
...
...
and after how can I insert the max value into another table MAX_VAL???
Thanks
Raf
-
Don't know what you did but it works for me:
SQL>Create sequence my_seq
2 minvalue 0 start with 0;
Sequence created.
SQL>create or replace
2 function alpha_seq(sq number)
3 return varchar2 is
4 a Varchar2(40):='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
5 l pls_integer;
6 s1 pls_integer;
7 d1 pls_integer:=0;
8 d2 pls_integer:=0;
9 d3 pls_integer:=0;
10 i1 pls_integer:=0;
11 i2 pls_integer:=0;
12 i3 pls_integer:=0;
13 Begin
14 l:=length(a);
15 s1:=sq;
16 i1:=trunc(s1/l);
17 d1:=mod(s1,l);
18 if i1 > 0 then
19 s1:=s1-i1*l;
20 i2:=trunc(s1/l);
21 d2:=mod(s1,l);
22 if i2 > 0 then
23 s1:=s1-i2*l;
24 i1:=trunc(s1/l);
25 d3:=mod(s1,l);
26 end if;
27 end if;
28 return '_'||substr(a,d3+1,1)
29 ||substr(a,d2+1,1)
30 ||substr(a,d1+1,1);
31 end;
32 /
Function created.
SQL>create table bl
2 (SITE_ID varchar2(10), COD_CONV varchar2(10))
3 /
Table created.
SQL>Create Or Replace Trigger On_Site_Id
2 Before Insert Or Update Of Site_Id
3 On Bl For Each Row
4 Declare
5 Sq Number;
6 Begin
7 Select My_Seq.Nextval Into Sq From Dual;
8 :New.Cod_Conv := Alpha_Seq(Sq);
9 End;
10 /
Trigger created.
SQL>select * from bl;
no rows selected
SQL>insert into bl values('03022038','___-_-___');
1 row created.
SQL>insert into bl values('05035238','___-_-___');
1 row created.
SQL>insert into bl values('02014837','___-_-___');
1 row created.
SQL>insert into bl values('12034041','___-_-___');
1 row created.
SQL>insert into bl values('02607812','___-_-___');
1 row created.
SQL>insert into bl values('07601955','___-_-___');
1 row created.
SQL>insert into bl values('02050658','___-_-___');
1 row created.
SQL>insert into bl values('02034645','___-_-___');
1 row created.
SQL>insert into bl values('02607796','___-_-___');
1 row created.
SQL>select * from bl;
SITE_ID COD_CONV
---------- ----------
03022038 _000
05035238 _001
02014837 _002
12034041 _003
02607812 _004
07601955 _005
02050658 _006
02034645 _007
02607796 _008
9 rows selected.
To update the max_value just add the following to the trigger:
UPDATE MAX_VAL SET COD_CONV = :NEW.COD_CONV;
Like:
Create Or Replace Trigger On_Site_Id
Before Insert Or Update Of Site_Id
On Bl For Each Row
Declare
Sq Number;
Begin
Select My_Seq.Nextval Into Sq From Dual;
:New.Cod_Conv := Alpha_Seq(Sq);
UPDATE MAX_VAL SET COD_CONV = :NEW.COD_CONV;
End;
PS: NO Need to create an 'AFTER' trigger!.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|