Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Posts
    42

    Unanswered: what is the wrong with this trigger

    Hi,

    iam trying to execute the trigger, but it wont work.
    Actually iam trying to fire this trigger after inserting a row in table, then iam checking a column of that particular row, if it is then i am inserting the values of the row in a new table. here is my code pls help

    CREATE OR REPLACE TRIGGER TRG_INS_IN_FILE
    AFTER INSERT ON IN_FILE

    DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;

    BEGIN

    IF (INFILE.STATUSCODE='RECEIVED') THEN
    INSERT INTO QUEUE(QUEUEID, QUEUETYPE, INFILENAME, PROCESSINGFLAG, MANUALFLAG,CREATEDDATE) VALUES
    (QUEUEID.NEXTVAL,'FILEVALIDATOR',INFILE.INFILENAME ,'Y','N',SYSDATE);
    COMMIT;
    ELSE IF(INFILE.STATUSCODE='ACCEPTED') THEN
    INSERT INTO QUEUE(QUEUEID, QUEUETYPE, INFILENAME, PROCESSINGFLAG, MANUALFLAG,CREATEDDATE) VALUES
    (QUEUEID.NEXTVAL,'RECORDVALIDATOR',INFILE.INFILENA ME,'Y','N',SYSDATE);
    COMMIT;
    ELSE IF (INFILE.STATUSCODE='PROCESSED') THEN
    INSERT INTO QUEUE(QUEUEID, QUEUETYPE, INFILENAME, PROCESSINGFLAG, MANUALFLAG,CREATEDDATE) VALUES
    (QUEUEID.NEXTVAL,'FORMATTER',INFILE.INFILENAME,'Y' ,'N',SYSDATE);
    COMMIT;
    ELSE IF(INFILE.STATUSCODE='READY') THEN
    INSERT INTO QUEUE(QUEUEID, QUEUETYPE, INFILENAME, PROCESSINGFLAG, MANUALFLAG,CREATEDDATE) VALUES
    (QUEUEID.NEXTVAL,'ROUTER',INFILE.INFILENAME,'Y','N ',SYSDATE);
    COMMIT;
    END IF;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
    END;

  2. #2
    Join Date
    Nov 2003
    Posts
    87
    itz
    ELSIF not ELSEIF

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: what is the wrong with this trigger

    As well as the ELSIF syntax error mentioned by Raseena, you are also refering to values in the inserted row wrongly - you should use :NEW, and the trigger must be a FOR EACH ROW trigger.

    Also, I would be surprised if you really wanted those QUEUE records to be created even if the insert into IN_FILE rolls back? Assuming you didn't want that, I have removed the autonomous transaction code and commits.

    I removed the EXCEPTION block because (a) your trigger will never raise NO_DATA_FOUND, and (b) if it could you probably shouldn't be ignoring it like you do (DBMS_OUTPUT is for debugging, not error handling).

    Then looking at the code I see that the 4 insert statements differ only in the value of QUEUETYPE used, so factored that out into a variable. In case there are other STATUSCODE values that are not handled, I checked the variable was not null before inserting.

    Code:
    CREATE OR REPLACE TRIGGER TRG_INS_IN_FILE 
    AFTER INSERT  ON IN_FILE 
    FOR EACH ROW  
    DECLARE
       l_queue_type QUEUE.QUEUETYPE%TYPE;
    BEGIN
     
       IF (:NEW.STATUSCODE='RECEIVED') THEN 
          l_queue_type := 'FILEVALIDATOR';
       ELSIF(:NEW.STATUSCODE='ACCEPTED') THEN 
          l_queue_type := 'RECORDVALIDATOR';
       ELSIF (:NEW.STATUSCODE='PROCESSED') THEN 
          l_queue_type := 'FORMATTER';
       ELSIF(:NEW.STATUSCODE='READY') THEN 
          l_queue_type := 'ROUTER';
       END IF; 
    
       IF l_queue_type IS NOT NULL THEN
          INSERT INTO QUEUE(QUEUEID, QUEUETYPE, INFILENAME, PROCESSINGFLAG, MANUALFLAG,CREATEDDATE) VALUES 
             (QUEUEID.NEXTVAL,l_queue_type,:NEW.INFILENAME,'Y','N',SYSDATE); 
       END IF;
    END;
    If those 4 statuscodes are ALL the possible status codes then it could be simplified even further:

    Code:
    CREATE OR REPLACE TRIGGER TRG_INS_IN_FILE 
    AFTER INSERT  ON IN_FILE 
    FOR EACH ROW  
    BEGIN
       INSERT INTO QUEUE(QUEUEID, QUEUETYPE, INFILENAME, PROCESSINGFLAG, MANUALFLAG,CREATEDDATE) VALUES 
          (QUEUEID.NEXTVAL
          ,DECODE(:NEW.STATUSCODE
                 ,'RECEIVED','FILEVALIDATOR'
                 ,'ACCEPTED','RECORDVALIDATOR'
                 ,'PROCESSED','FORMATTER'
                 ,'READY','ROUTER'
                 )
          ,:NEW.INFILENAME,'Y','N',SYSDATE); 
    END;
    (I don't normally put so much effort into rewriting other people's code! Don't know what's come over me today.)

  4. #4
    Join Date
    Dec 2003
    Posts
    42
    Thank you very much, right now it is working

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •