Create a trigger to call a procedure

40 pts.
Tags:
Oracle
PL/SQL
HI,
I am trying to create a trigger where is checks for a condition every time an insert or delete happens on the table and then calls a procedure. Any help is appreciated.
PROCEDURE:
============
  CREATE OR REPLACE PROCEDURE "GFMPNRT"."PP_CLEANUP_ALERTS_FOR_POC" (v_ACTION_LEVEL_KEY   IN VARCHAR2,
        v_ACTION_LEVEL_ID    IN NUMBER,
        v_STARTTIME         IN TIMESTAMP,
        v_ENDTIME           IN TIMESTAMP)
as
BEGIN
   DELETE ALERTS
   WHERE DD_DATE BETWEEN v_STARTTIME AND v_ENDTIME
   AND ACTION_LEVEL_KEY = v_ACTION_LEVEL_KEY
   AND ACTION_LEVEL_ID = v_ACTION_LEVEL_ID;
 
END;
 
 
TABLE (EMP):
===========
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ACTION_LEVEL_KEY                          NOT NULL VARCHAR2(50)
 ACTION_LEVEL_ID                           NOT NULL NUMBER(10)
 DTACTDATEKEY                              NOT NULL TIMESTAMP(3)
 TDDATEKEY                                          VARCHAR2(21)
 NREVID                                             NUMBER(10)
 LACTIONID                                          NUMBER(10)
 LACTIONTYPE                                        NUMBER(10)
 LSTATE                                             NUMBER(10)
 LSTATUS                                            NUMBER(10)
 SACTACTION                                         NUMBER(10)
 SACTNOTES                                          NVARCHAR2(255)
 SACTNOTES2                                         NVARCHAR2(255)
 LD_QUEID                                           NUMBER(10)
 SREASON_CODE                                       NVARCHAR2(5)


Software/Hardware used:
Oracle Database 11g
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

This is my trigger creation script:

create OR REPLACE TRIGGER GFMPNRT.TEST_RECVID
AFTER INSERT OR DELETE ON EMP FOR EACH ROW
BEGIN
DECLARE
v_ACTION_LEVEL_KEY IN ACTION_LEVEL_KEY; 
v_ACTION_LEVEL_ID IN ACTION_LEVEL_ID;
v_STARTTIME IN DTACTDATEKEY – INTERVAL ‘7’ DAY;
v_ENDTIME IN DTACTDATEKEY;
PP_CLEANUP_ALERTS_FOR_POC(:NEW.ACTION_LEVEL_KEY,:NEW.ACTION_LEVEL_ID,:NEW.STARTTIME,:NEW.ENDTTIME);
END;
/
I get the following error
LINE/COL ERROR
——– —————————————————————–
3/20     PLS-00103: Encountered the symbol “IN” when expecting one of the
         following:
         constant exception <an identifier>
         <a double-quoted delimited-identifier> table long double ref
         char time timestamp interval date binary national character
         nchar

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • carlosdl
    So, what exactly do you need help with?

    Are you getting errors?  If so, please post them.
    85,430 pointsBadges:
    report
  • Nagesh1985
    I need the trigger creation script.  Mine gets created with compilation errors.
    40 pointsBadges:
    report
  • Nagesh1985

    This is my trigger creation script:


    create OR REPLACE TRIGGER GFMPNRT.TEST_RECVID
    AFTER INSERT OR DELETE ON EMP FOR EACH ROW
    BEGIN
    DECLARE
    v_ACTION_LEVEL_KEY IN ACTION_LEVEL_KEY; 
    v_ACTION_LEVEL_ID IN ACTION_LEVEL_ID;
    v_STARTTIME IN DTACTDATEKEY - INTERVAL '7' DAY;
    v_ENDTIME IN DTACTDATEKEY;
    PP_CLEANUP_ALERTS_FOR_POC(:NEW.ACTION_LEVEL_KEY,:NEW.ACTION_LEVEL_ID,:NEW.STARTTIME,:NEW.ENDTTIME);
    END;
    /


    I get the following error

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    3/20     PLS-00103: Encountered the symbol "IN" when expecting one of the
             following:
             constant exception <an identifier>
             <a double-quoted delimited-identifier> table long double ref
             char time timestamp interval date binary national character
             nchar

    40 pointsBadges:
    report
  • carlosdl
    Sorry, I didn't see you responded.

    The IN word is causing the problem in these lines:

    v_ACTION_LEVEL_KEY IN ACTION_LEVEL_KEY; 
    v_ACTION_LEVEL_ID IN ACTION_LEVEL_ID;
    v_STARTTIME IN DTACTDATEKEY - INTERVAL '7' DAY;
    v_ENDTIME IN DTACTDATEKEY;
    Why did you put it there?  What is your goal or what were you trying to achieve?

    There are several errors in your code:

    You are declaring your variables incorrectly.
    You don't have a BEGIN.
    85,430 pointsBadges:
    report
  • carlosdl
    The official documentation has a lot of examples of using triggers:

    Using Triggers
    85,430 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: