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

oracleplsqltrigger

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

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

Best Answer

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.

AFTER INSERT:

USE `TEST`;
DELIMITER $$
DROP TRIGGER IF EXISTS MEDICAMENTO.monthUpdateTriggerAI$$
USE `TEST`$$
    CREATE DEFINER = CURRENT_USER TRIGGER `TEST`.`monthUpdateTriggerAI` AFTER INSERT ON `TestTable` FOR EACH ROW
    BEGIN
    SET @COUNT=(SELECT COUNT(*) FROM TestTable2 WHERE (ItemId=NEW.ItemId AND Year=YEAR(NEW.Date) AND Month=MONTH(NEW.Date)));
    IF @COUNT=0 THEN
        INSERT INTO TestTable2 (ItemId,Year,Month,Open,Close,Increase ) VALUES 
        (NEW.ItemId , YEAR(NEW.Date), MONTH(NEW.Date),NEW.Value,NEW.Close,0.0); 
    ELSE
        UPDATE TestTable2 SET TestTable2.Close=NEW.Close AND Increase=(NEW.Close/(SELECT 
        Open FROM TestTable WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date)) LIMIT 1))
        WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date));
    END IF;
    END;
$$
DELIMITER ;

AFTER UPDATE:

USE `TEST`;
DELIMITER $$
DROP TRIGGER IF EXISTS MEDICAMENTO.monthUpdateTriggerAU$$
USE `TEST`$$
    CREATE DEFINER = CURRENT_USER TRIGGER `TEST`.`monthUpdateTriggerAU` AFTER UPDATE ON `TestTable` FOR EACH ROW
BEGIN

    UPDATE TestTable2 SET TestTable2.Close=NEW.Close AND Increase=(NEW.Close/(SELECT 
    Open FROM TestTable WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date)) LIMIT 1))
    WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date));

END;
$$
DELIMITER ;

Note: Be careful about the SELECT Open FROM TestTable WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date)), I've added a LIMIT 1 to avoid duplicate rows.

Change the database TEST.

You can also use ON DUPLICATE KEY on the two triggers execution.

You can get more information in the MySQL's documentation manual.

Best Answer

Related Solutions

Mysql – find and insert row to another table using thesql trigger

Mysql – Create Trigger MySql update or insert in another table

AFTER INSERT:

AFTER UPDATE:

Related Question