#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    3
    Rep Power
    0

    Unhappy Firebird Trigger: Modify Value & Insert Record Problem


    Hi all experts...

    I am currently new in Firebird especially in trigger. Usually I done this in script manually, but I am really fascinating to create it with trigger.

    Please let me explain my tables first.

    Code:
    • STOCK CODE NAME TOTAL GOOD BROKEN SERVICE
    • DETAIL ID STOCK_CODE SERIAL
    • BROKEN DETAIL_ID MARK
    • SERVICE DETAIL_ID START_DATE END_DATE COST
    • LOGS DETAIL_ID MARK START_DATE END_DATE COST
    And now my problems:
    1. How to modify STOCK.GOOD and STOCK.BROKEN value before insert a new record into BROKEN? That will be: STOCK.GOOD-1, STOCK.BROKEN+1.
    2. How to insert all record from BROKEN and SERVICE into LOGS before current record in SERVICE is deleted?

    I hope my questions could be accepted.

    Thank you very much in advance.

    Regards,
    Abdi
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,055
    Rep Power
    383
    I have some serious reservations about your schema.
    Assuming that will not be changed, it looks as if you just need an after insert trigger on
    the BROKEN table that updates the STOCK table as required.

    Somewhere, of course, you will have to take care of all the possible logic error situations
    e.g. GOOD turning negative.
    However, the integrity of the math inside the trigger will be guaranteed by the transaction within which it runs.

    Clive
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    3
    Rep Power
    0
    Yes, I mean after insert. My mistake I'm sorry

    I've try to consider all logic error situations as possible I can.
    I mean like this, the user cannot mark a stock detail as broken, before she/he insert a record detail from table stock. So, if there's no record in DETAIL, access to insert record in BROKEN (in app) is disabled, and GOOD will not turning negative.

    Regards,
    Abdi
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    3
    Rep Power
    0
    Never mind, I figured it out.

    Thanks.

IMN logo majestic logo threadwatch logo seochat tools logo