Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2009
    Posts
    12

    Unanswered: Error in trigger

    Hi All,

    I have a problem in this trigger,pls help me solving it.

    It says compiled sucessfulyy but not updating. There is an procedure "eDocsNetNHRStatusChange" used in this trigger, whic works fine when executed directly....

    Its also updates but with trigger doesnt work.....
    -------------------------------------Trigger-------------------------------

    create or replace TRIGGER "TrgUpdateeDocsNetRequests" AFTER UPDATE ON eDocsNetRequests
    FOR EACH ROW
    DECLARE
    DivisionID VARCHAR2(12);
    eDocsFormID VARCHAR2(12);
    RequestID NUMERIC(18,0);
    RequestStatus VARCHAR2(1);
    CreatedBy VARCHAR2(50);

    CURSOR CurseDocsNetRequests
    IS
    SELECT
    DivisionID,
    eDocsFormID,
    RequestID,
    RequestStatus,
    TO_CHAR(CreatedBy)
    FROM DUAL a
    WHERE (select count(*) from dual b where
    (:new.RequestID = :OLD.RequestID)
    AND (:new.RequestStatus :OLD.RequestStatus)) > 0
    AND :NEW.RequestStatus in ( 'A' , 'C' );

    BEGIN
    OPEN CurseDocsNetRequests;
    << LABEL8 >>

    WHILE 1 = 1
    LOOP
    BEGIN
    FETCH CurseDocsNetRequests INTO DivisionID, eDocsFormID, RequestID, RequestStatus, CreatedBy;
    IF CurseDocsNetRequests%NOTFOUND THEN
    EXIT;
    END IF;


    IF eDocsFormID = 'NHR' and( RequestStatus = 'A' or RequestStatus = 'C') THEN
    BEGIN
    eDocsNetNHRStatusChange( RequestID, CreatedBy, RequestStatus);
    END;
    END IF;
    END;

    END LOOP;
    CLOSE CurseDocsNetRequests;
    END;





    -------------------------------------------------------------------------

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Do you have a little idea what this piece of code does? It took me a while to decipher it (and a lot of amazed staring, how complicated - and wrongly - may be simple things implemented).

    To the cursor - it selects local variables from DUAL based on some conditions. As they are initially NULL, cursor will return zero rows or one row full of NULLs. The comparison inside the LOOP will be NULL too, so the procedure will never be called.

    Just curious, why not simply (without cursor and local variables)
    Code:
    IF :NEW.<column supposed to hold eDocsFormID value> = 'NHR'
      and( :NEW.RequestStatus = 'A' or :NEW.RequestStatus = 'C') 
      <and maybe some conditions checking equality of some :NEW and :OLD values> THEN
      -- suppose you named these local variables by column names
      eDocsNetNHRStatusChange( :NEW.RequestID, :NEW.CreatedBy, :NEW.RequestStatus);
    END IF;

  3. #3
    Join Date
    Aug 2009
    Posts
    12
    Hi,

    Thanks for the reply...

    This below query is in Sql, the same we migrated to oracle.

    Can you pls help me to properly migrate it to oracle... It wud be a great help...

    ------------------------------sql query-----------------------------------

    ALTER trigger [TrgUpdateeDocsNetRequests] on [dbo].[eDocsNetRequests] for update as
    Begin
    declare @DivisionID varchar (12) ,
    @eDocsFormID varchar (12) ,
    @RequestID numeric(18, 0) ,
    @RequestStatus varchar (1) ,
    @CreatedBy varchar (50)
    declare CurseDocsNetRequests cursor for
    SELECT DivisionID , eDocsFormID ,
    RequestID , RequestStatus , convert(varchar(8),CreatedBy)
    FROM inserted a where (select count(*) from deleted b where a.RequestID = b.RequestID
    and a.RequestStatus<>b.RequestStatus) > 0
    and a.RequestStatus in ( 'A', 'C')
    for read only
    open CurseDocsNetRequests
    while 1=1 begin fetch CurseDocsNetRequests into @DivisionID , @eDocsFormID , @RequestID , @RequestStatus , @CreatedBy if @@fetch_status <> 0
    break

    if @eDocsFormID = 'NHR' and ( @RequestStatus = 'A' or @RequestStatus = 'C' )
    begin
    exec eDocsNetNHRStatusChange @RequestID , @CreatedBy, @RequestStatus
    end
    end
    close CurseDocsNetRequests
    deallocate CurseDocsNetRequests end



    --------------------------------------------------------------------------

    It just have to trigger while updating ....

    Thanks...

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by ChinnuBlr
    It just have to trigger while updating ....
    It just triggers when updating. If you want to call the procedure, just call it.

    Instead of blind code rewriting, you should rather determine, what this trigger does, and implemented it in Oracle way.
    As I have no idea what that SQL Server code does, I am unable to migrate it.
    I am afraid that many people here will have the same problem.

  5. #5
    Join Date
    Aug 2009
    Posts
    12
    Hi,

    This trigger is written for a particular table...

    When that table is updated this trigger is fired and updates in other tables ....

    But the table related with the trigger is not updating.....

    Thanks

  6. #6
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by ChinnuBlr
    This trigger is written for a particular table...
    When that table is updated this trigger is fired and updates in other tables ....
    But the table related with the trigger is not updating.....
    It seems you have a big problem at least in formulating the trigger behaviour.
    Have a look at what you posted. Would you be able to write code based on this specification?
    Quote Originally Posted by flyboy
    It just triggers when updating. If you want to call the procedure, just call it.
    Good luck.

  7. #7
    Join Date
    Aug 2009
    Posts
    12
    Hi,

    This Trigger calls a procedure "eDocsNetNHRStatusChange" inside an if condition which updates in a specific table....

    But when this trigger is fired, that update is not happening....

    Pls help me.....
    -------------------------------------Trigger-------------------------------

    create or replace TRIGGER "TrgUpdateeDocsNetRequests" AFTER UPDATE ON eDocsNetRequests
    FOR EACH ROW
    DECLARE
    DivisionID VARCHAR2(12);
    eDocsFormID VARCHAR2(12);
    RequestID NUMERIC(18,0);
    RequestStatus VARCHAR2(1);
    CreatedBy VARCHAR2(50);

    CURSOR CurseDocsNetRequests
    IS
    SELECT
    DivisionID,
    eDocsFormID,
    RequestID,
    RequestStatus,
    TO_CHAR(CreatedBy)
    FROM DUAL a
    WHERE (select count(*) from dual b where
    (:new.RequestID = :OLD.RequestID)
    AND (:new.RequestStatus :OLD.RequestStatus)) > 0
    AND :NEW.RequestStatus in ( 'A' , 'C' );

    BEGIN
    OPEN CurseDocsNetRequests;
    << LABEL8 >>

    WHILE 1 = 1
    LOOP
    BEGIN
    FETCH CurseDocsNetRequests INTO DivisionID, eDocsFormID, RequestID, RequestStatus, CreatedBy;
    IF CurseDocsNetRequests%NOTFOUND THEN
    EXIT;
    END IF;


    IF eDocsFormID = 'NHR' and( RequestStatus = 'A' or RequestStatus = 'C') THEN
    BEGIN
    eDocsNetNHRStatusChange( RequestID, CreatedBy, RequestStatus);
    END;
    END IF;
    END;

    END LOOP;
    CLOSE CurseDocsNetRequests;
    END;





    -------------------------------------------------------------------------

Posting Permissions

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