1

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;
  • What do you expect :oldRow to be in an AFTER INSERT trigger? – mustaccio Jun 10 at 12:35
  • right now every time :oldRow I am getting null. Somewhere i read after inserting in trigger old values will be null. – Vinay Hegde Jun 10 at 12:37
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.

0

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;

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.