8 Replies Latest reply: May 18, 2014 4:08 PM by Frank Kulash RSS

    Conditional Update in After Insert Trigger

    Maestro_Vineet

      My requirement is to come up with a Trigger which will update a Column of a table once the event takes place.

      Structure of the table is given below. Table name Broadcast

      Column Name

      Data Type

      BRDCST_MSGID

      Number(10) (PK)

      BRDCST_MSG_SBJ_TXT

      Varchar2 (25)

      BRDCST_MSG_TXT

      Varchar2 (2000)

      BRDCST_MSG_EFCTV_DT

      Date

      BRDCST_MSG_TERMNT_DT

      Date

      LAST_UPDT_TIMSTM

      Date

      STATUS

      Varchar2(10)

       

      Broadcast

      BrdCast_MsgID

      BRDCST_MSG_SBJ_TXT

      BRDCST_MSG

      BRDCST_MSG_EFCTV_DT

      BRDCST_MSG_TERMNT_DT

      LAST_UPDT_TIMSTM

      STATUS

      1

      Unavailability of the application

      Application will not be available from 19-May,2014 till 22-May-2014,due to maintenance activity.

      5/19/2014

      5/22/2014

      5/19/2014

      Active

       

      If the Sysdate is equal to BRDCST_MSG_EFCTV_DT the value of the STATUS column will get updated to Active , Last_updt_timstm=Sysdate ,Again STATUS column become INACTIVE when the Sysdate is equal to BRDCST_MSG_TERMNT_DT and Last_updt_timstm=Sysdate.

       

      Create or Replace Trigger Update_Broadcast

      After Insert on Broadcast for each row

      Begin

      If  BRDCST_MSG_EFCTV_DT=Sysdate;

      then

      Update Broadcast

      Set Last_updt_timstm=Sysdate,STATUS=’Active’

      Where BRDCST_MSG_EFCTV_DT =Sysdate;

      Elsif

      BRDCST_MSG_TERMN_DT=Sysdate

      Then

      Update Broadcast

      Set Last_updt_timstm=Sysdate, STATUS=INACTIVE

      Where BRDCST_MSG_TERMNT_DT =Sysdate;

      Endif;

      End B Update_Broadcast;


      I am getting an error while executing the above mentioned trigger.Need your help to meet this business requirement.


      Thanks in advance.

        • 1. Re: Conditional Update in After Insert Trigger
          Zlatko Sirotic

          Do you want something like this?

           

          CREATE OR REPLACE TRIGGER update_broadcast

            BEFORE INSERT ON broadcast

            FOR EACH ROW

          BEGIN

            IF    :NEW.brdcst_msg_efctv_dt = SYSDATE THEN

              :NEW.last_updt_timstm := SYSDATE;

              :NEW.status := 'Active';

            ELSIF :NEW.brdcst_msg_termn_dt = SYSDATE THEN

              :NEW.last_updt_timstm := SYSDATE;

              :NEW.status := 'Inactive';

            END IF;

          END;

          /

           

          Regards

          • 2. Re: Conditional Update in After Insert Trigger
            Frank Kulash

            Hi,

             

            A FOR EACH ROW trigger on broadcast can't update, or even query, the broadcast table itself.

            This will be much simpler if you do the changes in a BEFORE INSERT OR UPDATE trigger, to set the columns on the row actually being modified.

             

            It looks like, whenever any row is being inserted, you want to update all rows where certain DATE columns have today's date.  That's not very efficient.  You'd be better off using DBMS_SCHEDULER to run a job once a day to change all the rows that have DATEs that need to be changed.

             

            Remember, SYSDATE changes values every second, so

            If  BRDCST_MSG_EFCTV_DT=Sysdate

            is extrememly unlikely to be TRUE.  For example, if brdcst_msg_efctv_dt is 17:00:00 on May 17, 2024, the condition above wIll be FALSE if SYSDATE is 17:00:01 on May 17, just 1 second later.  You probably want something like

            IF  TRUNC (brdcst_msg_efctv_dt) = TRUNC (SYSDATE)

            which will be TRUE all day long, no matter what the hours, minutes and seconds of the column (or of SYSDATE are).

            You don't want a semicolon at the end of an IF statement.

             

            Whenever you'd like help, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the results you want from that data.

            In the case of a DML operation (such as INSERT) the sample data should show what the tables are like before the DML, and the results will be the contents of the changed table after the DML.  If the results depend on SYSDATE, then say exactly what SYSDATE is at the time of each statement.  Remember, all DATEs in Oracle include hours, minutes and seconds.

            Explain, using specific examples, how you get those results from that data.

            Always say what version of Oracle you're using (e.g. 11.2.0.2.0).

            • 3. Re: Conditional Update in After Insert Trigger
              Maestro_Vineet

              @Frank

              I am using  Oracle version 11g Rel-2 11.2.0.2. Please find the Create Table and Insert statement below.

              CREATE TABLE BROADCAST

                2    (

                3    BRDCST_MSGID                    Number(10),

                4    BRDCST_MSG_SBJ_TXT        varchar2(40),

                5    BRDCST_MSG_TXT                varchar2(2000),

                6    BRDCST_MSG_EFCTV_DT     Date(10),

                7    BRDCST_MSG_TERMNT_DT  Date(10),

                8    Last_updt_timstm                   Date(10),

                9    Status                                   Varchar2(10),

                10    CONSTRAINT BROADCAST_pk PRIMARY KEY (BRDCST_MSGID),

                16    );

              INSERT into  Broadcast (BrdCast_MsgID,BRDCST_MSG_SBJ_TXT,BRDCST_MSG_txt,BRDCST_MSG_EFCTV_DT,BRDCST_MSG_TERMNT_DT,LAST_UPDT_TIMSTM,STATUS)

              Values(1,'Unavailability of the application','Application will not be available from 19-May,2014 till 22-May-2014,due to maintenance activity.',Trunc(Sysdate),Trunc(Sysdate + 3),Sysdate,'Active');

               

              Whenever the value of BRDCST_MSG_EFCTV_DT = Trunc(Sysdate) then update the Column Last_updt_timstm =Sysdate and Status=ACTIVE and again when the value of BRDCST_MSG_TERMNT_DT = Trunc(Sysdate+3)

              update the value of Last_updt_timstm=Sysdate and STATUS=INACTIVE.

               

              Do let me know if you need some more information.

               

              Thanks

              • 4. Re: Conditional Update in After Insert Trigger
                Frank Kulash

                Hi,

                Maestro_Vineet wrote:

                 

                @Frank

                I am using  Oracle version 11g Rel-2 11.2.0.2. Please find the Create Table and Insert statement below.

                CREATE TABLE BROADCAST

                  2    (

                  3    BRDCST_MSGID                    Number(10),

                  4    BRDCST_MSG_SBJ_TXT        varchar2(40),

                  5    BRDCST_MSG_TXT                varchar2(2000),

                  6    BRDCST_MSG_EFCTV_DT     Date(10),

                  7    BRDCST_MSG_TERMNT_DT  Date(10),

                  8    Last_updt_timstm                   Date(10),

                  9    Status                                   Varchar2(10),

                  10    CONSTRAINT BROADCAST_pk PRIMARY KEY (BRDCST_MSGID),

                  16    );

                 

                 

                ...

                Are you sure you're using Oracle?  In Oracle, you can't specify a size for DATE columns, such as   DATE (10)  ; all DATEs in Oracle have the same size.

                • 5. Re: Conditional Update in After Insert Trigger
                  Maestro_Vineet

                  Hi Frank

                  That is something which i added before pasting it over here,so don't bother about the lenght of the Date data type.We all know that we don't need to define the length fo the Date data type.

                   

                  I would appreciate if you can help me to come up with the After Insert Trigger,to meet my requirement.

                   

                  Thanks

                  • 6. Re: Conditional Update in After Insert Trigger
                    rp0428
                    That is something which i added before pasting it over here,so don't bother about the lenght of the Date data type.We all know that we don't need to define the length fo the Date data type.

                     

                    Your explanation makes NO sense. YOU are the one that told us to 'find the Create Table and Insert statement below' and what you POSTED is NOT valid Oracle syntax.

                     

                    And why would you 'added before pasting' if 'We all know that we don't need to define the length'?

                     

                    People can't use what you posted to reproduce the problem because what you posted is NOT Oracle syntax.

                    I would appreciate if you can help me to come up with the After Insert Trigger,to meet my requirement.

                     

                    There IS NO SUCH trigger. That is NOT done with a trigger.

                    My requirement is to come up with a Trigger which will update a Column of a table once the event takes place.

                    I've never heard of someone 'requiring' that a developer use a trigger as a solution; especially when a trigger is NOT the proper solution.

                     

                    When you need to perform table-level operations you need to use a SQL query and/or PL/SQL code. As Frank already told you a trigger should not query or perform DML on the same table the trigger fires on.

                     

                    Also you should NOT perform non-transactional statements within a transactional trigger. A trigger can NOT see changes being made by other sessions.

                     

                    You need to abandon you desire to use a trigger for this problem.

                    • 7. Re: Conditional Update in After Insert Trigger
                      Frank Kulash

                      Hi,

                       

                      If you can explain what you need to do, then someone can help you find a good way to do it, or at least try.

                       

                      So far, you have not explained what you need to do; you have explained how you are planning to do something, but not what that is.  What is the business requirement here?  What is the purpose of the status column?  If status is a function of brdcst_msg_efctv and SYSDATE, why can't you use  brdcst_msg_efctv and SYSDATE?  There are good reasons why you might want to set a status column; if you happen to have one, it's very unclear whether a trigger is a good what to do whatever you need, and an AFTER INSERT trigger is almost certainly not a good way.

                      • 8. Re: Conditional Update in After Insert Trigger
                        Zlatko Sirotic

                        If you want to work with the Oracle DBMS, you need to read the documentation.

                        E.g. see about mutating-table restriction:

                         

                        Oracle Database PL/SQL Language Reference 11g Release 2 (11.2)

                        http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/triggers.htm#LNPLS759

                         

                        Mutating-Table Restriction

                         

                        This topic applies only to row-level simple DML triggers.

                        A mutating table is a table that is being modified by a DML statement (possibly by the effects of a DELETE CASCADE constraint).

                        (A view being modified by an INSTEAD OF trigger is not considered to be mutating.)

                         

                        The mutating-table restriction prevents the trigger from querying or modifying the table that the triggering statement is modifying.

                        When a row-level trigger encounters a mutating table, ORA-04091 occurs, the effects of the trigger and triggering statement are rolled back,

                        and control returns to the user or application that issued the triggering statement ...

                         

                         

                        Regards