Drop

Drop:

Dropping the table means deleting the data of the table along with its definition(structure of the table).

The difference between the truncate and drop:

 Truncate : only deleting the content or  entire records  of the data.

  Drop : deleting the entire table along with its content or records.

 lets create a table:

 create table student(sid number primary key,firstname varchar2(50) not null,lastname varchar2(50) not null,email varchar2(50) not null unique);

now lets drop the table:

 drop table student;

the log will be:

drop table student succeeded.

now just see the description of the table by following query:

desc student;

the log will be:

ERROR: object STUDENT does not exist

now if we want to get the table back. fire the following query:

flashback table student to before drop;

the log will be:

flashback table succeeded.

now again see the description of the log:

Name                           Null     Type                                                                                                                                                                                        

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

SID                            NOT NULL NUMBER                                                                                                                                                                                      

FIRSTNAME                      NOT NULL VARCHAR2(50)                                                                                                                                                                                

LASTNAME                       NOT NULL VARCHAR2(50)                                                                                                                                                                                

EMAIL                          NOT NULL VARCHAR2(50)

Flashback:

when the table is dropped along with its records , if we use flashback then the table along with the data is restored. but the triggers which are created on the table does not work. you have to create the trigger again.

we can use rename to with the flashback;

Example: flashback table student to before drop rename to student_new;

Conclusion:

 whenever a table is dropped the associated triggers are also dropped.

Trigger

Trigger : 

Triggers are fired when the following events occur.

    The following events are :

    1. insert,update,delete -- DML 

    2. create,alter,drop -- DDL

    3. Login,Logoff,shutdown of the database.

Why Triggers?

 Suppose when some one is inserting in the table where you are the owner. then you can know that some one is inserting the data in the table that you own. We can say its for handling the events and for Security.

The basic syntax of the trigger is:

CREATE [OR REPLACE] TRIGGER <TRIGGERNAME>

[BEFORE | AFTER ][INSERT|UPDATE|DELETE] ON <TABLENAME>

BEGIN

 <STATEMENTS LIKE DBMS OUTPUT>

END;

/

Note : here triggers are created once and executed when ever a event is fired on the table specified in the creation of the trigger.

we cannot call trigger in the procedure or function , since it is a event handler , it is dynamic, that is it is fired when the specific queries are fired. 

Conclusion :

 Triggers are stored in the database once it is compiled and fired whenever the event occurs.

Below is the example:

 1. Create a table:

    create table student(sid number primary key,firstname varchar2(50) not null,lastname varchar2(50) not null,email varchar2(50) not null unique);

2. set the server output on:

 set serveroutput on;

3. create trigger for insert

  create or replace trigger studentinserttrigger

before insert on student

for each row

begin

 dbms_output.put_line('you are inserting into the student table');

end;

/

when the trigger is compiled you will get the following log:

 TRIGGER STUDENTINSERTTRIGGER compiled.

4. now lets insert into the table:

 insert into student values(1,'raaj','saraff','raajsaraff@self.com')

5. the log on console will be:

   1 rows inserted

    you are inserting into the student table

here in the above example , trigger is fired when the record in inserted.

In the same way, triggers for update,delete are created.

Now lets write a update trigger for the student table.

 create or replace trigger studentupdatetrigger

after update on student

for each row

begin

 dbms_output.put_line('you are updating the student table');

end;

/

the log will be: 

TRIGGER STUDENTUPDATETRIGGER compiled

Now update the record that is inserted.

update student set email = 'raajsaraff@saraff.in' where sid = 1

the console log  will be :

1 rows updated

you are updating the student table .

Now lets create trigger on delete:

create or replace trigger studentdeletetrigger 
before delete on student
begin
 dbms_output.put_line('you are deleting from student');
end;
/

The log will be :
 TRIGGER STUDENTDELETETRIGGER compiled

now just delete the record :
 delete from student;
The log will be :
1 rows deleted
 you are deleting from student.


Dropping a trigger:

drop trigger studentinserttrigger

log will be:
drop trigger studentinserttrigger succeeded.

Session Control Statements

Session Control Statements are not supported by the PL/SQL. They are basic SQL Commands.
ALTER SESSION
SET ROLE

TCS

Transaction Control Statements:

These statements manages the changes or modifications that are made by the DML statements
Following are the Commands used for Transaction Control Statement.
1. COMMIT
2. ROLLBACK
3. SAVEPOINT
4. SET TRANSACTION

DML

Data Manipulation Language:

it means that inserting and modifying the data in the TABLE:
some of the basic commands are listed below:
1. CALL
2. DELETE
3. EXPLAIN PLAN
4. INSERT
5. LOCK TABLE
6. MERGE
7. SELECT
8. UPDATE

NOTE: here SELECT is used for only retrieval of data, it does not manipulate.
             CALL and EXPLAIN PLAN will only used in PL/SQL blocks, rest all are Supported in SQL              and PL/SQL.

DDL

DDL stands for Data Definition Language:

here DDL queries Define the data. the following are the important operations done by DDL:
1. creating the database objects like tables, views, sequences, procedures etc.
2. alter the database objects .
3. dropping the database objects.
4. granting and revoking privileges and roles in the database.
5. Following are the commands used as DDL commands:
    a. CREATE
    b. DROP
    c. ALTER
    d. GRANT
    e. REVOKE
    f. ANALYSE
    g. AUDIT
    h. COMMENT
    i. ASSOCIATE STATISTICS
    j. DISASSOCIATE STATISTICS
    k. NOAUDIT
    l. PURGE
   m. RENAME
   n. TRUNCATE
   o. UNDROP