0

i'm trying to create a TRIGGER for my Oracle database. I Have 2 tables in it(table1 , table2) Table 1 has 3 columns (id, name, value). id can be non unique here Table 2 has 2 columns ( id, concat) id is unique here

What i want to do, is, every time a new row is inserted in Table 1., the trigger must Update the concat value of Table 2 which is a concate of name and values of all row where table1.id = table2.id

At the moment i think about this trigger, but i dont want to break anything in my db, so any advice would be great before testing :)


CREATE TRIGGER creation
AFTER INSERT  ON table1 ac
FOR EACH ROW
BEGIN 
UPDATE table2
SET concat= CONCAT(' ',CONCAT(ac.NAME, CONCAT('=',ac.VALUE)))
WHERE 
table1.id = table2.id 
END

Thx a lot

1
  • As always make a backup, of the data, insert a row and watch for4 change in tabke2 Besides ac.NAME should be NEW.NAME and so on and calling a column concat is not a good idea – nbk Jul 2 '20 at 9:36
1

Try something like this. It also accounts for situations in which "id" didn't previously exist. Note: I used "concat_col" as the "concat" column name; @nbk is right - you shouldn't have column names that match pre-defined function names.

CREATE OR REPLACE TRIGGER TRIGGER1 
AFTER INSERT ON TABLE1 
REFERENCING OLD AS OLD NEW AS NEW 
FOR EACH ROW
BEGIN
   merge into table2
   using (select :new.id l_id from dual) d on (table2.id = d.l_id)
   when matched then   
      update set concat_col = concat_col || ' ' || :new.name || '=' || :new.value
   when not matched then
      insert (id, concat_col) values (:new.id, :new.name || '=' || :new.value);
END;
/
1
  • thx a lot, i will change my column name – Dallincha Jul 2 '20 at 12:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.