Lab 11 : Triggers, Prodedure and Cursor



Design any sample table(s) with necessary field and values. After considering the below questions and demonstrate all the issues.
a.       Triggers(Insert, Update, Delete)
b.      Procedure
c.       Cursor

3 comments:

  1. SQL*Plus: Release 10.2.0.3.0 - Production on Sat Nov 29 04:36:01 AM 2014

    Copyright (c) 1982, 2006, Oracle. All Rights Reserved.


    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
    With the Partitioning, OLAP and Data Mining options

    SQL> set serveroutput on
    SQL> create table sample
    2 (id number(5),
    3 name varchar(15));

    Table created.

    SQL> create table another
    2 as
    3 select * from sample;

    Table created.

    SQL> desc sample

    SQL> desc another

    SQL> alter table another
    2 add username varchar(10);

    Table altered.

    SQL> alter table another
    2 add changedate date;

    Table altered.

    SQL> desc another

    SQL> select sysdate from dual;

    SQL> select user from dual;

    SQL> create or replace trigger info
    2 before insert on sample
    3 for each row
    4 declare
    5 v_user varchar(10);
    6 v_date date;
    7 begin
    8 select user into v_user from dual;
    9 select sysdate into v_date from dual;
    10 insert into another
    11 values(:new.id,:new.name,v_user,v_date);
    12 end;
    13 /

    Trigger created.

    SQL> desc sample

    SQL> insert into sample
    2 values(000,'tamim');

    1 row created.

    SQL> select * from sample;

    SQL> select * from another;

    SQL> desc sample;

    SQL> desc another;

    SQL> insert into another
    2 (id,name)
    3 values
    4 (111,'shah');

    1 row created.

    SQL> select * from another;

    SQL> update another
    2 set username='mahmud';

    2 rows updated.

    SQL> select * from another;

    SQL> create or replace trigger info
    2 before insert on another
    3 for each row
    4 declare
    5 v_user varchar(10);
    6 v_date date;
    7 begin
    8 select user into v_user from dual;
    9 select sysdate into v_date from dual;
    10 :new.changedate:=v_date;
    11 :new.username:=v_user;
    12 end;
    13 /

    Trigger created.

    SQL> desc another

    SQL> insert into another
    2 (id,name)
    3 values
    4 (222,'firstSQL');

    1 row created.

    SQL> select * from another;

    SQL> exit

    ReplyDelete
  2. Because of the limitation of words, i was unable to publish the program with results. Here i published the program without created diagram....

    ReplyDelete
  3. sir , trigger to create hossena, maybe i have some mistake

    ReplyDelete

Popular Posts