I'm sort of new to Pl/SQL and I'm trying to create a trigger that on every insert to the table inserts some more new values into the table. For eg if I insert 12.01 into the table, the trigger should insert 12.01A, 12.01B, 12.01C into it.
It seems simple and I've created the following trigger but im getting quite a few errors.
create or replace trigger "BUILDRELEASEVERSIONS_T1"
AFTER
insert or update on "BUILDRELEASEVERSIONS"
for each row
begin
DECLARE
i number(1);
v_version BuildReleaseVersions.version%TYPE;
BEGIN
v_version := :NEW.version;
FOR i IN 1..5
LOOP
Insert into BuildReleaseVersions values
(case
when i=1 then concat(v_version,'B')
when i=2 then concat(v_version,'C')
end);
end LOOP;
end;
end;
the errors im getting are:
Errors: TRIGGER BUILDRELEASEVERSIONS_T9
Line/Col: 8/1 PL/SQL: SQL Statement ignored
Line/Col: 12/7 PL/SQL: ORA-00933: SQL command not properly ended
Line/Col: 14/3 PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
;
I'm unable to figure out where im going wrong. Please help. Thank you!
CodePudding user response：
Your trigger would cause an infinite loop, as each insert would cause the trigger to fire, which would perform more inserts, which would cause the trigger to fire, which would perform more inserts, etc. If that happened Oracle would notice and terminate the loop with an error, though you might exceed the length of the version field first. But as you're trying to insert into the same table you'll get the mutating table error before it reaches that point anyway.
is it possible to do this same thing with a simple Select statement?
You could do all of the inserts with a single insert statement, which uses a hierarchical query:
insert into buildreleaseversions (version)
select '12.01' || case level when 1 then null else chr(63 level) end
from dual
connect by level <= 4
The query generates 4 rows, from the level limit; for each row it appends something to the base '12.01' value based on the level - for level it appends nothing, for the rest it generates a letter based on the offset into the ASCII range. 'A' is character 65, B is 66, C is 67; so those can be obtained by adding the level (2, 3, 4) to fixed value 63.
Your question refers to suffixes A to C, but your trigger only has B and C; if you don't want A then just adjust slightly:
insert into buildreleaseversions (version)
select '12.01' || case level when 1 then null else chr(64 level) end
from dual
connect by level <= 3