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;

• mustaccio: What do you expect :oldRow to be in an AFTER INSERT trigger?
• Vinay Hegde: right now every time :oldRow I am getting null. Somewhere i read after inserting in trigger old values will be null.

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;