Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2010
    Posts
    4

    Unanswered: How to create triggers..

    Hello,

    I have been working on the DB2 but have come to a problem, and was hoping you would help me:

    Q: I have created two databases;
    1) “NewFarmsRecord “and 2) “OldFarmsRecord”.
    “NewFarmsRecord” contains the Tables:


    Farms Table
    Pk FarmID Char()
    FarmSize Char()
    FarmOnwerName Char()
    FarmOnwerAddress Char()
    LeaseAmount Double()
    LeaseStartDate Date
    LeaseEndDate Date
    FarmType Char()


    Work Table
    Pk WorkID Char()
    Pk, Fk EmployeeID Char()
    WorkTittle Char()


    Employees Table
    Pk EmployeeID Char()
    FirstName Char()
    LastName Char()
    Address Char()
    HireDate Date
    Fk1 WorkID Char()
    Fk2 FarmID Char()


    Categories Table
    Pk CategoryID integer()
    categoryName Char()
    Description Char()
    Picture Blob()



    Shipments Table
    Pk ShipmentID Char()
    CategoryName Char()
    ShipmentDate
    ShipmentVehicle No: Char()
    Fk FarmID Char()


    Shipment_Type Table
    Pk, Fk1 ShipmentID Char()
    Pk ShipmentType Char()
    Quantity Char()


    Commission_Agent Table
    Pk CommissionAgentID Char()
    FirstName Char()
    LastName Char()
    Address Char()
    Phone No:
    Commission Char()


    Sales table
    Pk SalesNo: Char()
    Pk, Fk1 ShipmentID Char()
    Fk2 CommissionAgentID Char()
    SalesDate Date
    SellAmount Double()
    Expenses Double()
    NetAmount Double()
    AmountTaken Double()


    The “OldFarmsRecord” also contains the same tables.

    I have some problems regarding;

    1- when I enter values in a table (since the data-type is character) the wrong values can be entered, Suppose ‘farmID’ is “##CCC###’ (# represent numbers from 0-9 and C is capital alphabetic characters from A-Z) but due to character data-type, any value in string form is entered, so now what to do to ensure that only correct type of value is entered.
    How the wrong values must be traced and stopped before they are inserted into a table. (One way is using trigger but I do not understand how to create that).

    2- The 2nd problem that I am facing is that, when row in parent table is deleted, the row in child table corresponding that value is also deleted. (This is done by using ‘ON DELETE CASCADE’ rule). Here I want to use a before Trigger that must be activated before Delete operation is performed on any table resulting that row to be inserted into corresponding tables of “OldFarmsRecords” Database.

    Thank you for your time.

    Regards,

    Mansoor Nizamani

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What DB2 version and OS are you using?

    For your questions:

    1) This can be accomplished with either a BEFORE trigger or a constraint.

    2) Assuming you can access the "OldFarmsRecord" database from the "NewFarmsRecord" database, then you would use an after delete trigger.

    Andy

  3. #3
    Join Date
    Mar 2010
    Posts
    4
    Sir, i am using db2v9.5 and windows XP

    and sir i have a concept to use 'before trigger' but not able to create that trigger, and sir how to use the constraint???

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Example of before trigger:

    Code:
    create trigger MyBeforeTrigger before insert on MyTable 
    referencing new as NewData 
    for each row 
    BEGIN
       IF (substr(NewData.FarmID,1,1) not between '0' and '9')
         then signal sqlstate '75001' set message_text = 'Invalid Farm ID';
       END IF;
       IF (substr(NewData.FarmID,2,1) not between '0' and '9')
         then signal sqlstate '75001' set message_text = 'Invalid Farm ID';
       END IF;
       IF (substr(NewData.FarmID,3,1) not between 'A' and 'Z')
         then signal sqlstate '75001' set message_text = 'Invalid Farm ID';
       END IF;
       ..
    
    END
    Example of Constraint:

    Code:
    Alter table MyTable add constraint MyConstraint check (
    substr(FarmID,1,1) between '0' and '0' and 
    substr(FarmID,2,1) between '0' and '0' and
    substr(FarmID,3,1) between 'A' and 'Z' and
    ... 
    
    );
    I am sure that there are more elegant solutions. I just wanted to show you an example.

    Andy

  5. #5
    Join Date
    Mar 2010
    Posts
    4
    sir,

    thank you for helping me.

Posting Permissions

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