Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2007
    Posts
    25

    Unanswered: Trigger question

    Write separate triggers for Oracle and Microsoft SQLServer that will set ‘ZIP_YN’ to ‘0’ if ‘ZIP_CODE’ is empty and set ‘ZIP_YN’ to ‘1’ if ‘ZIP_CODE’ has a value when a record is added or modified.

    CREATE OR REPLACE TRIGGER zip_modify
    AFTER INSERT OR UPDATE
    ON Orders
    FOR EACH ROW
    WHEN ZIP_CODE = NULL

    this is as far as i got.will someone explain to me how to word the if in
    Thanks in advance
    Last edited by superdrog; 07-03-07 at 02:47.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >will someone explain to me how to put an if in
    by writing a few lines of PL/SQL
    >ZIP_CODE = NULL
    WRONG! Never, ever use "=" with NULL
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2007
    Posts
    25
    CREATE OR REPLACE TRIGGER zip_modify
    AFTER INSERT OR UPDATE
    ON Orders
    FOR EACH ROW
    BEGIN
    -- Find ZIP_CODE
    IF (SELECT ZIP_CODE FROM Orders) is NULL;
    -- Insert record
    Update Orders SET ZIP_YN = 0;
    ELSE IF (SELECT ZIP_CODE FROM Orders) is NOT NULL;
    -- Insert record
    Update Orders SET ZIP_YN = 1;
    END IF;
    END;

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >SELECT ZIP_CODE FROM Orders
    How many rows in ZIP_CODE table?
    How many rows returned by this SELECT?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    You still have it wrong. Do the following.

    Code:
    CREATE OR REPLACE TRIGGER zip_modify
    AFTER INSERT OR UPDATE
    ON Orders
    FOR EACH ROW
    BEGIN
    -- Find ZIP_CODE
    IF :new.ZIP_CODE is NULL then
       :new.ZIP_YN = 0;
    else
       :new.ZIP_YN = 1;
    END IF;
    END;
    However, my real question is why do you bother to set the zip_YN flag. If you want to return the flag, simply use

    decode(zip_code,null,0,1) zip_YN

    in your select.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Jun 2007
    Posts
    25
    Thats exactly what i wanted....Thanks so much.I know its a bit of a roundabout way of witing a trigger but im following the question.

    What would be the difference in writing that trigger for microsoft sql server???

  7. #7
    Join Date
    Jun 2007
    Posts
    25
    How close is this?

    CREATE TRIGGER zip_modify_mirosoftsqlserver
    ON Orders
    FOR INSERT,UPDATE
    AS
    IF UPDATE (ZIP_CODE) IS NULL
    BEGIN
    UPDATE ZIP_YN = 0
    else
    UPDATE ZIP_YN = 1
    END
    GO

    Dont think im right here....give me time
    Last edited by superdrog; 07-03-07 at 02:49.

Posting Permissions

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