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

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

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???

Example of before trigger:

Example of Constraint:

I am sure that there are more elegant solutions. I just wanted to show you an example.

Andy

sir,

thank you for helping me.