I have a table:
create table test_tbl (id number, text varchar2(50));
with this data in it:
insert into test_tbl values (1,'Text 1');
insert into test_tbl values (2,'Text 2');
Now I want to insert a record, but if the ID is allready in the table, I want an update instead.
So if I do this insert
insert into test_tbl values (2,'Text 2 edited');
I want a trigger to do an UPDATE instead of an INSERT.
A trigger could be something like:
create trigger test_trg
before insert on test_tbl for each row
begin
if exists (select * from test_tbl where id = :new.id) then
-- update record
else
-- insert into table
end if;
end;But I can't figure out how to create this trigger.
Don't use a trigger for that. It's way more trouble than it's worth. Search this site for "read consistency" and "mutating table" for examples how incredibly complex that can get.
Best resolution:
1) Add a constraint,
alter table test_tbl add primary key ( id )
2) Use MERGE to handle both the insert and update
merge into test_tbl t
using ( select [new values] from dual ) n
on ( t.id = n.id)
when matched then
update set t.col = n.col, etc
when not matched than
insert (t.id,t.col, ... ) values (n.id,n.col, ... )