Facing Error ‘Missing expression’ while trying to create a After insert trigger in PL/SQL

oracleoracle-11gplsql

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?

Best Answer

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;
Related Question