RDBMS SOLUTION


ASSIGNMENT 6 

SET A Q. 1 SOLUTION

Write a trigger which will fire before insert or update on Investment having investment amount less than 10000. (Raise user defined exception and give appropriate message)




Source code:

    

    /* assignment 6 set a q 1 solution

    this exercise is important as the trigger is one of the important and frequently asked question

    first we will create tables,insert records and then we will create the trigger*/

Create table employee

(

    eid  number not null primary key,

    ename varchar2(40),

    address varchar2(40)

)

create table investment

(

    ino number not null primary key,

    iname varchar2(40),

    idate date,

    iamt number

)

create table e_i

(

    eid number references employee(eid),

    ino number references investment(ino)

)

/*inserting records*/

insert into employee values(1,'E1','A1');

insert into employee values(2,'E2','A2');

insert into employee values(3,'E3','A3');

insert into employee values(4,'E4','A4');

insert into employee values(5,'E5','A5');

insert into investment values(1,'I1',to_date('01/02/2000','dd/mm/yyyy'),50000)

insert into investment values(2,'I2',to_date('01/04/2000','dd/mm/yyyy'),70000)

insert into investment values(3,'I3',to_date('01/09/2000','dd/mm/yyyy'),40000)

insert into investment values(4,'I4',to_date('01/11/2000','dd/mm/yyyy'),20000)

insert into investment values(5,'I5',to_date('01/12/2000','dd/mm/yyyy'),90000)

insert into e_i values(1,1)

insert into e_i values(1,3)

insert into e_i values(2,1)

insert into e_i values(2,4)

insert into e_i values(3,2)

insert into e_i values(3,4)

insert into e_i values(4,5)

insert into e_i values(4,2)

/*trigger*/

Create or replace trigger t_inv before insert or update on investment for each row

Declare

t1 exception;

Begin

if(:new.iamt<10000) then

raise t1;

end if;

Exception 

when t1 then

dbms_output.put_line('not valid amount');

end;

insert into investment values(9,'I7',to_date('01/02/2002','dd/mm/yyyy'),20000)

/* Explaination:

- "Create or replace trigger t_inv" - this line indicates that a new trigger named "t_inv" should be created or replaced if it already exists.

- "before insert or update on investment for each row" - this line specifies that the trigger should execute before any row is inserted or updated in the "investment" table.

- ":new" - refers to the values being inserted or updated in the current row.

- "if(:new.iamt<10000) then" - this line checks if the value of "iamt" in the current row is less than 10000. If it is, then the "t1" exception is raised.

- "raise t1;" - this line raises the "t1" exception if the "if" condition is true.

- "Exception when t1 then" - this line specifies that if the "t1" exception is raised, the following code block should be executed.

- "dbms_output.put_line('not valid amount');" - this line prints a message to the console indicating that the value of "iamt" is not valid.

 */
 Download code         next