After Inserting , Update / Insert into another table in pl/sql trigger

Question

I am working on PL/SQL trigger i have two table

 TAG Table

SR.NO    ID    TAG    BRAND_ID  
------  ----  -----  ----------
  1      10    ABC       99


MY_TAGS Table

 SR_NO    TAG    COUNT   BRAND_ID  
--------   ---   ------   -------
   1      ABC       1      99

I need a trigger in such a way that

1. If tag ABC is inserted with BRAND_ID=99 one more times in TAG table then i need to increase the count of ABC to 2 in MY_TAGS table
2. If new values inserted other than ABC for 99 then i need to insert new row in MY_TAG table

How can I handle this situation using trigger. I am new to PL /SQL any help would be great.

I tried like this , but it will always execute Insert statement (i.e else part )

 CREATE or replace TRIGGER trig1
    AFTER INSERT ON TAG
    REFERENCING NEW AS newRow OLD AS oldRow
    FOR EACH ROW
    WHEN (newRow.ID >=0)
    BEGIN
    IF(:newRow.TAG=:oldRow.TAG AND :newRow.BRAND_ID=:oldRow.BRAND_ID) THEN
       UPDATE MY_TAGS SET COUNT=(select max(counter) from MY_TAG)+1) where brand_id=newRow.BRAND_ID and tag=:newRow.TAG;
    ELSE
       INSERT INTO MY_TAGS VALUES((select max(SR_NO) from MY_TAGS)+1,:newRow.TAG,1,:newRow.BRAND_ID);
    END IF;
    END trig1;

Answer 1

Instead of materializing the count, which bears the risk of inconsistencies, I recommend you drop the my_tags table and create a view which uses aggregation to get the count.

DROP TABLE my_tags;

CREATE VIEW my_tags
AS
SELECT sr_no,
       tag,
       brand_id,
       count(*)
       FROM tag
       GROUP BY sr_no,
                tag,
                brand_id;

Like that you don't need any trigger, the count is always accurately calculated.

Answer 2

I did like this , Its working now :

CREATE or replace TRIGGER trig1
AFTER INSERT ON TAG
REFERENCING NEW AS newRow OLD AS oldRow
FOR EACH ROW
DECLARE 
rowcnt number;
BEGIN
SELECT COUNT(*) INTO rowcnt FROM MY)TAGS where brand_id=:newRow.COBRAND_ID and 
tag_name=:newRow.TAG_NAME;
    IF(rowcnt=0) THEN 
        INSERT INTO MY_TAGS VALUES((select max(SR_NO) from 
MY_TAGS)+1,:newRow.TAG,1,:newRow.BRAND_ID);
ELSE
     UPDATE MY_TAGS SET COUNT=(select max(count) from MY_TAG)+1) where 
brand_id=newRow.BRAND_ID and tag=:newRow.TAG;
END IF;
END trig1;