CODEWITHSUNDEEP
oracle-databaseplsqldatabase-trigger
microfonic
microfonic

Reputation: 3

Oracle trigger create with case statement

I've got a problem with creating trigger in oracle, I have a table "stock". And I have fields "xxx" ,"yyy" and "zzz". I'd like to make a trigger which can fill the another field on this "stock" table just after insert or update. I've tired something like that but I couldn't.

 create or replace 
Trigger Connector_Stock_Trg 
AFTER INSERT OR UPDATE OF xxx,yyy,zzz ON STOCK
For Each Row 
Declare 
L_Temp VARCHAR2(64);
BEGIN
L_Temp := Case  
         When Xxx Is Not Null Then Xxx
         When yyy Is Not Null And zzz Is Not Null Then yyy||'_'||zzz
         When Yyy Is Null And zzz Is Not Null Then zzz
         WHEN zzz IS NULL AND yyy IS NOT NULL THEN yyy
   END;

   Update Stock 
   SET CONNECTOR=L_Temp;
END;

Upvotes: 0

Views: 743

Answers (1)

Popeye
Popeye

Reputation: 35900

You don't have to update the table STOCK but you just need to assign an expression to :NEW.CONNECTOR as following.

CREATE OR REPLACE TRIGGER CONNECTOR_STOCK_TRG BEFORE
    INSERT OR UPDATE OF XXX, YYY, ZZZ ON STOCK
    FOR EACH ROW
--DECLARE
--    L_TEMP   VARCHAR2(64);
BEGIN
    :NEW.CONNECTOR := -- USE :NEW
        CASE
            WHEN :NEW.XXX IS NOT NULL THEN :NEW.XXX
            WHEN :NEW.YYY IS NOT NULL AND :NEW.ZZZ IS NOT NULL THEN :NEW.YYY
                                                          || '_'
                                                          || :NEW.ZZZ
            WHEN :NEW.YYY IS NULL AND :NEW.ZZZ IS NOT NULL THEN :NEW.ZZZ
            WHEN :NEW.ZZZ IS NULL AND :NEW.YYY IS NOT NULL THEN :NEW.YYY
        END;

--    UPDATE STOCK
--    SET
--        CONNECTOR = L_TEMP;

END;
/

Note: You can use the Virtual Columns concept in the Oracle for such requirements. see this

Cheers!!

Upvotes: 2

Related Questions