DBMS-TRIGGERS

                                    TRIGGERS

 AIM:

To perform High-level language extension with triggers (Student mark list).

SYNTAX:

CREATE[OR REPLACE]TRIGGER[schema.]trigger

{BEFORE|AFTER|INSTEAD OF}

{DELETE

|INSERT

|UPDATE[OF column[,column].....]}

|OR {DELETE

|INSERT

|UPDATE[OF column,[,column]....]}]...

ON[schema.]{table|view}

REFERENCING(OLD[AS]old

|NEW[AS]new...]

FOR EACH{ROW|STATEMENT}{WHEN(condition)}]

DESCRIPTION:

            The details about the students mark are being in the stu table with attributes.

            Name

            Rollno

            Mark1

            Mark2

            Mark3

The stu1 table consists of attributes,

            Rollno

            Total

            Average

            Result

The details of the first table are being given in the prompt by the user.  The total, average are processed for currently processed row in the stu table using after insert on trigger the values are placed in the table stu1.

PROGRAM:

create or replace trigger st_trig

after insert on stu

for each row

 declare

 total number;

 average number;

 result varchar2(10);

 begin

 total:=(:new.m1+:new.m2+:new.m3);

 average:=total/3;

 if(:new.m1>50 and :new.m2>50 and :new.m3>50) then

 result:='pass';

 else

result:='fail';

 end if;

 insert into stu1 values(:new.rollno,total,average,result);

 end;

SQL> /

Trigger created.

SQL> desc stu;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 NAME                                                 VARCHAR2(20)

 ROLLNO                                             NUMBER

 M1                                                        NUMBER

 M2                                                        NUMBER

 M3                                                        NUMBER

SQL> desc stu1;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 ROLLNO                                              NUMBER

 TOTAL                                                 NUMBER

 AVERAGE                                           NUMBER

 RESULT                                               VARCHAR2(10)

SAMPLE OUTPUT:

BEFORE TRIGGER CREATION:

SQL> select * from stu;

no rows selected

SQL> select * from stu1;

no rows selected

AFTER TRIGGER CREATION:

SQL> insert into stu values('&name',&rollno,&m1,&m2,&m3);

Enter value for name: GOWTHAM

Enter value for rollno: 1212

Enter value for m1: 100

Enter value for m2: 89

Enter value for m3: 90

old   1: insert into stu values('&name',&rollno,&m1,&m2,&m3)

new   1: insert into stu values('GOWTHAM',1212,100,89,90)

1 row created.

SQL> select * from stu;

NAME                     ROLLNO         M1         M2         M3

-------------------- ---------- ---------- ----------------- ----------

GOWTHAM               1212             100         89         90

GOKILA                     1213               89         67         55

RAMYA                     1214               45         67         78

SQL> select * from stu1;

    ROLLNO      TOTAL    AVERAGE RESULT

     ----------      ---------- ------------------- ----------

      1212             279             139.5           pass

      1213             211             105.5           pass

      1214             190               95               fail

RESULT:

Thus the high-level language extension with triggers has been performed for generating the students mark list.