0

Hi everyone hope you're doing well. AS you might know by now I'm new to PL/SQL and I'm trying to write a trigger as you can see below :

CREATE OR REPLACE TRIGGER T_Insert
  AFTER INSERT ON tag

    for each row
      BEGIN


       Select case
          WHEN EXISTS (Select tag , brand_id
                       from my_tags
                       where tag      = :new.tag AND
                       brand_id = :new.brand_id) THEN


                      UPDATE my_tags set count := count +1                     
         ELSE
            insert into my_tags (sr_no,tag,count,brand_id)
            valuse (:new.sr_no , :new.tag ,1 , :new.brand_id)
            end case AS SS
     from dual;

 END;

When I want to run the code and create the trigger I receive these errors:

ORA_00936 : missing expression. (Line 15) update statement
SQL statement ignored

am I using the Select Case and When Exists The wrong way?

2

try this one

CREATE OR REPLACE TRIGGER T_Insert
AFTER INSERT ON tag

for each row
declare
  v_cnt number := 0;
BEGIN
  -- selection number of rows  
  -- where tag = :new.tag and AND brand_id = :new.brand_id
  -- the result will be stored in a variable v_cnt
  -- if there no rows matched the Count(1) will return 0 
  Select count(1)
    into v_cnt
    from my_tags
   where tag = :new.tag
     AND brand_id = :new.brand_id;

  -- check if some rows Exits
  -- than do an update
  -- else do an insert
  if v_cnt > 0
  then
    UPDATE my_tags set count = count + 1
    -- if you want to update only one row
    WHERE tag=:new.tag AND brand_id=:new.brand_id 
    ;

  ELSE
    insert into my_tags
      (sr_no, tag, count, brand_id) values
      (:new.sr_no, :new.tag, 1, :new.brand_id);
  end if;

END;

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.