Oracle & Iron Man – Great Combination

http://www.oracle.com/us/ironman2/index.html

How to use trigger to check database constant

We can use trigger to check database constant and customize error message. For example we have two tables name Department and Employee. I have written two trigger on Update and Delete on Department table, which will responsible to check referential integrity constraint and customize the error message.

Creating Tables Department and Employee

CREATE TABLE DEPARTMENT

  (

    Department_Id          Number(*,0) Not Null Enable,

    Department_Name        Varchar2(45 Byte) Not Null Enable,

    Department_Description Varchar2(500 Byte),

    Primary Key (Department_Id) 

  );

CREATE TABLE EMPLOYEE

  (

    Employee_Name        Varchar2(45 Byte) Not Null Enable,

    Employee_Ssn         Varchar2(45 Byte) Not Null Enable,

    Employee_Phone       Varchar2(45 Byte) Not Null Enable,

    Employee_Cellular    Varchar2(45 Byte) Not Null Enable,

    Employee_Description Varchar2(500 Byte),

    Department_Id        Number(*,0) Not Null Enable,

    Primary Key (Employee_Ssn) ,

    Foreign Key (Department_Id) References Department (Department_Id) Enable

  );

Insert some data on Department and Employee Table

Insert Into Department (Department_Id, Department_Name, Department_Description) 

Values ('1001', 'Tecnical', 'Tecnical Department');

Insert Into Department (Department_Id, Department_Name, Department_Description) 

Values ('1002', 'Merketing', 'Merketing Department');

Insert Into EMPLOYEE (Employee_Name, Employee_Ssn, Employee_Phone, Employee_Cellular, Department_Id) 

Values ('Tamim', '100001', '880175307713', '880175307713', '1001');

Trigger on Delete of Department

Create Or Replace Trigger Td_Department 

  After Delete on Department 

  FOR EACH row

    DECLARE numrows INTEGER;

  BEGIN

    SELECT COUNT(*) INTO numrows

    FROM Employee

    WHERE  Employee.Department_ID = :old.Department_ID;

    IF (numrows> 0) THEN

      raise_application_error( -20001, 'Cannot DELETE Department because Employee exists.' );

    END IF;

  END;

  /

Test a Delete SQL Statement on Department Table

  Delete 

  From Department 

  Where Department_Id = 1001 

  /*

  SQL Error: ORA-20001: Cannot DELETE Department because Employee exists.

  */

Trigger on Update of Department

Create Or Replace Trigger Tu_Department 

  After Update On Department 

  For Each Row

    Declare Numrows Integer;

  Begin

    IF (:old.Department_ID <> :new.Department_ID) THEN

      Select Count(*) Into Numrows    

      FROM Employee

      Where Employee.Department_Id = :Old.Department_Id;

      IF (numrows > 0) THEN

        raise_application_error( -20005, 'Cannot UPDATE Department because Employee exists.' );

      END IF;

    END IF;

  END;

  /

Test a Update SQL Statement on Department Table

  Update Department 

  Set Department_Id = 1003

  Where Department_Id = 1001 

  /*

  SQL Error: ORA-20005: Cannot UPDATE Department because Employee exists.

  */

How To Block Websites Without Using Any Software.

Sometimes we want to restrict access to some particular

websitefrom our PC but we don’t know how to do it without using some software
for it.

Here I m sharing with you a method to do it without using any software......

1. Run -> drivers

2. Open The file ..\drivers\etc\ hosts using note pad

3. Under "127.0.0.1 localhost" Add 127.0.0.2 www.yahoo.com

Code:

127.0.0.1                 localhost

127.0.0.2                 www.yahoo.com

127.0.0.2                 yahoo.com

That site will no longer be accessible.

Even if you ping www.yahoo.com from command prompt it will not response from original source.

N.B: Please restart your browser before you perform the test.