Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Posts
    12

    Question Unanswered: Trigger problem...

    I need to make a trigger as follows:

    Table Customer:
    Columns: CustomerID (NOT NULL), CustomerNo

    Table Contract:
    Columns: CustomerID (NOT NULL), TransferCode (possible values: I, S, U, D)

    You can find contracts of certain customer by using CustomerID in table Contract. When I create a customer, it needs only customerID, not CustomerNo.

    When I add CustomerNo to customer I have in table Customer, I need to check table Contract if there is contracts for that customer and if there is, I need to update TransferCode to "I" if it is "S" or "U".

    I tried following code but it is not correct. I would appreciate any help

    CREATE OR REPLACE TRIGGER "MASO".CONTRACT_UPDATE_TRG
    AFTER UPDATE ON MASO.CUSTOMER FOR EACH ROW
    WHEN (:NEW.CUSTOMERNO <> :OLD.CUSTOMERNO)
    BEGIN
    IF :OLD.CUSTOMERNO = "" THEN
    UPDATE CONTRACT SET CONTRACT.TRANSFERCODE = "I" WHERE CONTRACT.CUSTOMERID = :NEW.CUSTOMERID;
    END IF;
    END;
    ------------------------------------
    Long days and pleasant nights
    - Roland the gunslinger
    ------------------------------------

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    You need to check up on Oracle's handling of NULL and empty strings. Change your code as follows

    IF :OLD.CUSTOMERNO IS NULL THEN

    This also applies to the line
    WHEN (:NEW.CUSTOMERNO <> :OLD.CUSTOMERNO)

    Should be something like

    WHEN ((:NEW.CUSTOMER_NO IS NOT NULL AND :OLD.CUSTOMER_NO IS NULL) OR ((:NEW.CUSTOMERNO <> :OLD.CUSTOMERNO))

    Hth
    Bill

  3. #3
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    look at the comments from BillM. Also beware of the double quotes. That's wrong that should be single quotes:

    WRONG:
    UPDATE CONTRACT SET CONTRACT.TRANSFERCODE = "I" WHERE CONTRACT.CUSTOMERID = :NEW.CUSTOMERID

    RIGHT:
    UPDATE CONTRACT SET CONTRACT.TRANSFERCODE = 'I' WHERE CONTRACT.CUSTOMERID = :NEW.CUSTOMERID

    Also, is an update always required when the customerno changes ?
    Doesn't it depend on the value of the transfercode ?
    Edwin van Hattem
    OCP DBA / System analyst

  4. #4
    Join Date
    May 2003
    Posts
    12

    Thumbs up

    Thank you for your help! I got it now. Here ist the right code which works perfectly (all quotation marks are single quotes):

    CREATE OR REPLACE TRIGGER "MASO".CONTRACT_UPDATE_TRG
    AFTER UPDATE of CUSTOMERNO ON MASO.CUSTOMER FOR EACH ROW
    WHEN (OLD.CUSTOMERNO IS NULL OR OLD.CUSTOMERNO = '')
    BEGIN
    IF (:OLD.CUSTOMERNO IS NULL OR :OLD.CUSTOMERNO = '') THEN
    UPDATE CONTRACT SET CONTRACT.TRANSFERCODE = 'I'
    WHERE CONTRACT.CUSTOMERid = :OLD.CUSTOMERID
    AND CONTRACT.TRANSFERCODE = 'S';
    END IF;
    END;

    For evanhattem: Customer number does never change. Only time change in that column occurs is when it is given or customer destroyed. This trigger is made for one particular situation, when Contract is transferred before customer had its number. It caused one another application to reject the contract. And because Transfer status was updated to 'S', contract was not transferred again when customer was given a number. From now on, when this situation occurs again, the contract transfer number is changed back to 'I' when customer is given its number and contract is then automatically tranferred again.

    ------------------------------------
    Long days and pleasant nights
    - Roland the gunslinger
    ------------------------------------

Posting Permissions

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