-
I have a trigger that does half of what I would like it to do.
For every record that is inserted, it gets the pidm correctly.
Now, if a record has a pidm, I would like to set the tran_number to 1 or increment the tran_number for that pidm. With that added, the trigger won't compile. I'm getting ora-600 internal errors. Here is what I got so far...
create or replace trigger tzgprnt_insert_pidm
before insert on tzgprnt
for each row
declare
pidm number;
tran_number number;
begin
select distinct gwsntid_pidm into pidm
from gwsntid
where gwsntid_netid = :new.tzgprnt_netid;
:new.tzgprnt_pidm := pidm;
exception
when no_data_found then
tzgprnt_pidm := null;
if pidm not null then
select max(tzgprnt_tran_number) +1 into tran_number
from tzgprnt
where tzgprnt_pidm = pidm;
:new.tzgprnt_tran_number := tran_number;
exception
when no_data_found then
:new.tzgprnt_tran_number := 1;
end if;
end;
-
create or replace trigger tzgprnt_insert_pidm
before insert on tzgprnt
for each row
declare
pidm number;
tran_number number;
begin
begin
select distinct gwsntid_pidm into pidm
from gwsntid
where gwsntid_netid = :new.tzgprnt_netid;
:new.tzgprnt_pidm := pidm;
exception
when no_data_found then
tzgprnt_pidm := null;
end; -- u have to close every begin block
if pidm not null then
begin
select max(tzgprnt_tran_number) +1 into tran_number
from tzgprnt
where tzgprnt_pidm = pidm;
:new.tzgprnt_tran_number := tran_number;
exception
when no_data_found then
:new.tzgprnt_tran_number := 1;
end;
end if;
end;
/
-
Shestakov--
thanks... it still didn't work.
i started getting:
Warning: Trigger created with compilation errors.
Errors for TRIGGER BANINST2.TT_TZGTEST_INSERT_PIDM:
LINE/COL ERROR
-------- --------------------------------------------------------------
19/13 PLS-00103: Encountered the symbol "NULL" when expecting one of the following:
in like between
The symbol "like" was substituted for "NULL" tocontinue.
so i changed "if pidm not null then" to "if pidm > 0 then"
and started getting this error:
CREATE OR REPLACE TRIGGER baninst2.tt_tzgtest_insert_pidm
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [16221], [], [], [], [], [], [], []
No errors.
It might be time to go a different route, but surely this can be done.
-
OK!
create or replace trigger tzgprnt_insert_pidm
before insert on tzgprnt
for each row
declare
pidm number;
tran_number number;
begin
begin
select distinct gwsntid_pidm into pidm
from gwsntid
where gwsntid_netid = :new.tzgprnt_netid;
:new.tzgprnt_pidm := pidm;
exception
when no_data_found then
tzgprnt_pidm := null;
end; -- u have to close every begin block
if pidm IS not null then -- add IS --> this is oracle syntax.
begin
select max(tzgprnt_tran_number) +1 into tran_number
from tzgprnt
where tzgprnt_pidm = pidm;
:new.tzgprnt_tran_number := tran_number;
exception
when no_data_found then
:new.tzgprnt_tran_number := 1;
end;
end if;
end;
/
-
still no luck... with that change, i still get the ORA-00600 error.
thanks
-doug-
-
--
-- second version of ur trigger (much more simple)
--
create or replace trigger tzgprnt_insert_pidm
before insert on tzgprnt
for each row
begin
--
-- first select
--
select max(gwsntid_pidm) into :new.tzgprnt_pidm
from gwsntid
where gwsntid_netid = :new.tzgprnt_netid;
--
-- second select
--
select nvl(max(tzgprnt_tran_number),0) + 1
into :new.tzgprnt_tran_number
from tzgprnt
where tzgprnt_pidm = pidm;
:new.tzgprnt_tran_number := tran_number;
end;
/
Why it possible :
Function max always return 1 row (with null or not).
U can use this in ur select.
Example :
create table abc (a number);
select nvl(max(a),0) + 1 from abc;
SQL> create table abc (a number);
Table created.
SQL> select * from abc;
no rows selected
SQL> select max(a) from abc;
MAX(A)
----------
SQL> select nvl(max(a),0) + 1 from abc;
NVL(MAX(A),0)+1
---------------
1
-
thanks... still getting ORA-00600 error after putting
in the nvl function.
-doug-
-
Probably u haven't problem with trigger code.
I have come problems with tables or database instance,
because code like this works perfectly in our applications.
