Make your own free website on Tripod.com
 
 

Transaction processing, Triggers and DDL statements

In this section we present the DDL statements and the transaction processing concepts that were applied in our project.  DDL statements were deliberately not given earlier so that we can more clearly explain the need for triggers etc that we have created on the tables.  This is more closely tied up with transaction processing.  This keeps all our ideas at one place rather than all over.

1.  DDL statements

2.  DDL statements for creating Triggers and Indexes

3.  Transaction processing


1. DDL
 

The DDL statements written by us do the following
 

  Enforce primary key constraints and define them

 Check is the right data have been entered
 
        Checking for the right e-mail ID’s (see the create statements)
 
Enforcing NOT NULL constraints on some attributes like the company’s name, customers name etc.

Enter the default value of the current system date if the user left it out while entering the data in the RFP table.

 Create triggers, which are used later (…. example)
 
 
 
 

CREATE TABLE COMPANY
(
 --This is the unique ID value
 ID NUMBER CONSTRAINT pk_emp PRIMARY KEY,

 --The address of the customer
  ADDRESS VARCHAR2 (15)
  CHECK (ADDRESS = UPPER (ADDRESS)),

 --The mail ID, check for it
  EMAIL VARCHAR2 (35)
  CHECK (EMAIL LIKE ‘%@%’),

  --The name of the customer
       NAME VARCHAR2 (30)
  CONSTRAINT C_NAME NOT NULL,
 
       --The Telephone number
   TEL NUMBER
  )
 
 

The customer table is also of the same format so is not presented here.

The RFP table is the most important one.  It involves the references for all other tables and hence its DDL is presented here.  There are a lot of foreign keys involved and hence it is a bit complicated.  The DDL for it is.  This DDL also checks for the date and inserts the system date if the data is not specified.
 
 

CREATE TABLE RFP
(
  -- The date of transaction
 
     Date_tc DATE DEFAULT SYSDATE

 --This is the customer’s ID value

 CID NUMBER CONSTRAINT fk_cid

  REFERENCES customer (CID),
 
 
 
 

 --This is the company’s ID

 ID NUMBER CONSTRAINT fk_id
  REFERENCES company (ID),

 -- This is the parttype ID

 TID NUMBER CONSTRAINT fk_tid
  REFERENCES parttype (TID),

 -- This is the part ID

 PPID NUMBER CONSTRAINT fk_ppid
  REFERENCES part (PPID),

      --The number of parts purchased
 
  QTY NUMBER,

  )
 
 

2.  Triggers

Triggers are a fragment of code that is run before or after the table is modified.  In our example we have the company table and the RFP table.  When a tuple from the company table is deleted the effect should propagate all the way to the RFP table.

 The RFP tuple corresponding to the company table is to be deleted.  That is the tuple having the company ID in the RFP is to be deleted.

Also all the tuples in the parttype table that correspond to the parttypes that the company sells are to be deleted.

Hence we create a trigger that runs does all of the above when such an event occurs.
 

The trigger that starts after a tuple from company table is deleted.  All transaction in RFP is void if such a case occurs.
 

CREATE TRIGGER after_delete_company
 
AFTER DELETE ON COMPANY

FOR EACH ROW

BEGIN

 DELETE FROM RFP
 WHERE (: old. ID = RFP.  ID);

 DELETE FROM PARTTYPE
 WHERE (: old.  ID = PARTTYPE.  ID);

END after_delete_company
 

The trigger for deleting the RFP entry after customer is deleted.  All transactions stored in the RFP are void.

CREATE TRIGGER after_delete_customer

AFTER DELETE ON CUSTOMER

FOR EACH ROW

BEGIN

 DELETE FROM RFP
 WHERE (: old.  CID = RFP.  CID);

END after_delete_company
 

The trigger that updates the parttype information after the company updates its information like the e-mail ID, address.

CREATE TRIGGER after_customer_update

AFTER UPDATE OF ADDRESS, EMAIL ON COMPANY

FOR EACH ROW

DECLARE

 Initial_string VARCHAR2 (50): = ‘last updated on’;

 Todays_date DATE DEFAULT SYSDATE;

BEGIN

 UPDATE PARTTYPE
SET PARTTYPE.  TINF = Initial_string || Todays_date || ‘\’
WHERE COMPANY.  CID = PARTTYPE.  CID;

END after_delete_company
 

This trigger updates the PARTTYPE table with the date when the update is made.  This leaves the parttype information up to date.
 

3. Transaction processing

If the administrator or an employee of the Internet firm that we are running chooses to insert new records in the database our Perl script comes into play.

The pseudo code that is used when the user chooses to insert data values works as follows. It creates a save point after every insert.  Then if the number of inserts exceed 5 the transaction is committed.  To keep track of all this information we used the perl array variables in our script.

The pseudo code is defined below the idea is as follows.  The variables used are

# Continue is set to 0 if the user does want to insert any more tuples.
# No_of_inserts records the number of inserts that are made.
 
 

No_of_inserts <- 1

Continue = 1

While (Continue! = 0)

Begin
 The user inserts information into the buffer

 If  (information is correct)
  Create a save point
 Else
  Rollback to the earlier saves point
 
 No_of_inserts <- No_of_inserts + 1

 If (No_of_inserts > 5)
  Commit the transaction

 If the user wants to continue
  Continue = 1;
 Else
  Continue = 0;
End.
 

The Actual part of the perl code fragment, which does this is given below
 


# Insert values into the table
$Sth = $dbh1->do (‘insert into company values \
($tempID, $tempaddress, $tempmail, $tempname, $temptel)”);

# Check if the tempID is the correct value It is the primary key and hence should not
# Exist in the table
If (NotExists ($tempID, $tempaddress))
{
# Create a save point
$Sth = $dbh1->do (“SAVEPOINT @list_save”);
$No_inserts += 1;
Continue;
}
Else
{
$Sth = $dbh1->do (“ROLLBACK TO @(list_save-1)”);
}

#If the number of inserts is greater than 5
# Commit the transaction
If ($No_of_inserts > 5)
$Sth = $dbh1->do (“COMMIT ”);


Explanation

We allow the user to insert up to 5 values before committing the transaction.   Hence we count the number of inserts and when it exceeds 5 we use COMMIT.  Also we create a SAVEPOINT after every insert.  Thus if the user mistakenly enters the wrong value the we can ROLLBACK to the earlier point.

Consider the following sequence
 

INSERT INTO company VALUES
(98122, ‘Worcester', 'lappens@hotmail.com’,’lappens’, 7679220);

SAVEPOINT a;

INSERT INTO company VALUES
(98123, ‘Winchester', 'lappens@hotmail.com’,’lappens’, 7679220);