DML COMMANDS in Sql

1.Group By Clause

1.Group By Clause
2. Months_between 
3.Select Clause
4. Create User In Oracle
5. View In Oracle
6. Conversion Function In Oracle
7. In Clause 
8. Using Clause
9.Truncate ,Drop,Delete Commands
10. Convert-number-into-words

Read More

Implicit Cursor Rowcount

Example :-
write  PL/SQL Block, the salaries of all the employees in the ‘employees’ from

department_no 50  are updated. If none of the employee’s salary are updated we get a

message 'None of the salaries where updated'.
Else we get a message like for example, 'Salaries  are updated'
Implicit cursors are automatically created when you issue
select statement it used by oracle.
it is created by default when you issue dml statements like insert

,delete,update,select.

 declare num_of_emp number;
 BEGIN
   UPDATE employees
   SET salary = salary + 2000 where department_id=&dno;
   IF SQL%NOTFOUND THEN
     dbms_output.put_line('None of the salaries where updated');
   ELSIF SQL%FOUND THEN
     num_of_emp := SQL%ROWCOUNT;
     dbms_output.put_line('Salaries for ' || num_of_emp || 'employees are up
d');
   END IF;
 END;
/

Read More

PLSQL Interview Questions

HOME

1.How Many triggers can be applied to a table?

There are maximum 12 triggers can be applied to a table.

2. Difference between Syntax error & runtime error in PLSQL?

A Syntax error is easily identified by a PLSQL compiler.For example Incorrect spelling.

but runtime error can be handled with the help of Exception Section block in PLSQL.

3.How to disabled multiple triggers  of a table at a time ?

Alter table Table_name disable   all triggers.

4. Where is the pre defined procedures stored?

In oracle standard package,procedures,functions.

5.What is a cursor for loop?
Cursor for loop implicitly declares %ROWTYPE as loop index,opens a cursor,fetches rows of a table values from active set into record variable
& it closes when all the records have been carried out process.

6.What is the difference between Procedure and function?

Function must return value and procedure does not.

Function can be used in sql with some restrictions. where as procedure can not be called directly from sql.

7.Explain Boolean Datatype with Example In Oracle

DECLARE
  V_SAL NUMBER;
  V_FLAG BOOLEAN;
BEGIN
  SELECT SALARY INTO V_SAL FROM EMPLOYEES WHERE EMPLOYEE_ID=&EMPNO;

  V_FLAG := (V_SAL>=10000);

  IF V_FLAG=TRUE THEN
     DBMS_OUTPUT.PUT_LINE('HIGH GRADE');
  ELSE
     DBMS_OUTPUT.PUT_LINE('LOWER GRADE');
  END IF;
END;


8 WRITE A PROGRAM TO PRINT EVEN NUMBERS FROM 1 TO 100 DECLARE


--WRITE A PROGRAM TO PRINT EVEN NUMBERS FROM 1 TO 100
DECLARE
N NUMBER(3) :=0;
BEGIN
WHILE N<=100
LOOP
N :=N+2;
DBMS_OUTPUT.PUT_LINE(N);
END LOOP;
END;


9.Which privilege can be granted only on a DIRECTORY?

 READ,Write,Execute

10. Viewing Constraints

 --view all constraint definitions and names

- SELECT constraint_name, constraint_type, search condition

FROM user_constraints

WHERE table_name = 'EMP'

Read More

Example Of Constraints in Sql

Primary Key:- YOU CAN NOW CREATE A PRIMARY KEY IN oracle with the CREATE TABLE STATEMENT.
SYNTAX
THE SYNTAX TO CREATE A PRIAMARY KEY USING THE CREATE TABLE STATEMENT IN ORACLE/PLSQL IS:

 create table empl(employee_id number primary key,
 last_name varchar2(50),
 city varchar2(50))
 /

Syntax

Alter table table_name
Add column column_defination
Add constraint clause
drop[column] column-name [cascade|Restrict]
drop{primary key}foreign key constraint_name | unique
constraint_name|check constraint-name|constraint constraint-name}
alter [column] column_alteration|locksize {Row |Table}

alter table emp1 add phone number;


In oracle Sql-PLSQL a foreign key is column that appears in one table and must appear in another table.

Create table table_name
(Column_name1 datatype Null/Not null,
Column_name2 datatype Null/Not null,
Column_name3 datatype Null/Not null,
constraint constraint_name
foreign key(column_name1,column_name2....column_namen)
On delete cascade;

Syntax to create a foreign key with on Delete Cascade in alter table statement is:

Alter Table Table_name
Add Constraint constraint_name
Foreign key (Column_name1,Column_name2,....Column_nameN)
On Delete Cascade;

Now we will take one example:-

create  table dept(department_id number,
department_name varchar2(50),
location_id number,
city varchar2(50));

alter table dept add constraint pk10 primary key(department_id);

create table emp(employee_id number,last_name varchar2(50),
salary number(10,2),
department_id number )
/

 alter table emp add constraint fk14 foreign key(department_id)
 references dept(department_id) on delete cascade;

/

How to check   user constraints from employees table

select constraint_name,constraint_type,index_name
from user_constraints
where table_name='EMPLOYEES'
/

Read More

Oracle-Sql-Basic_Queries

1.Get the first_day of the Month

SQL> SELECT TRUNC (SYSDATE, 'MONTH') "First day of current month"

  2      FROM DUAL;

First day

---------

01-AUG-14

2.Get the Last_day of  the Current Month

SELECT TRUNC (LAST_DAY (SYSDATE)) "Last day of current month"

    FROM DUAL;

3.Write a query that check if a table exists in the current database schema.

 SELECT table_name

   FROM user_tables

  WHERE table_name = 'EMPLOYEES'

4. Write a query that shows a Table structure.

Desc Employees;

5.Write a query to display Current User.

Show User;

6.Write a query to find last_record from a table.

 SELECT employee_id,last_name,job_id

   FROM employees

  WHERE ROWID IN (SELECT MAX (ROWID) FROM employees)

 /

7. To Check table has any data

 select count(*) from employees

Or select 1 from employees

where Rownum=1


8. How to find out table name for which this constraint  applied

sql> select owner_table-name from user_constraints
where constraints_name =<<your constraint_name>>

if you have access to the dba_constraints
sql> select owner_table-name from dba_constraints
where constraints_name =<<your constraint_name>>

9. How to get list of all tables in Oracle

sql> select owner,table_name from all_tables
NOte:- If you are only concerned with tablesthat you own,not those that you have access to, you could use user_table.
/
sql> select owner,table_name from user_tables
 

10.1. How can you convert a number into words using Oracle Sql Query?

Please see the query below:-

SELECT TO_CHAR (TO_DATE (523, 'j'), 'jsp') from dual


Output:
TO_CHAR(TO_DATE(523,'J'),
-------------------------
five hundred twenty-three

Read More

Conversion function

Implicit conversion:-

A varchar2 or char value can be implicitly converted to
Number or date type value by oracle.
Similarly, a number or data type can be automatically
Converted to character data by oracle server.
For example the below select queries .both the queries will
Give the same output because oracle internally treats 10000
And '10000' are same.

Query-1
select employee_id,first_name,last_name,salary
From employees
Where salary > 10000;


Query-2

YYYY	Full year in numbers
YEAR	Year spelled out (in English)
MM	Two-digit value for month
MONTH	Full name of the month
MON	Three-letter abbreviation of the month
DY	Three-letter abbreviation of the day of the week
DAY	Full name of the day of the week
DD	Numeric day of the month

To_char functions
Element	Result
9	Represents a number
0	Forces a zero to be displayed
$	Places a floating dollar sign
L	Uses the floating local currency symbol
.	Prints a decimal point
,	Prints a comma as thousands indicator

select employee_id,first_name,last_name,salary
from employees
where salary > '10000';

Explicit data type conversion are single row function which are capable of

typecasting column value,literal or expression.
To_char,To_number,To_date  are three functions which perform
change of data types.
1. To_char()
This function is used to typecast a numeric value or date to character value with

a format model.
Syntax
To_char(number1,[format],[nls_paramete])
Consider the below select query.The query format the Hire_date & salary columns of

employees table using To_char function.
select first_name,To_char(hire_date,'Month DD,YYYY') hire_date,To_char

(salary,'$99999.99') salary
from employees
where  rownum < 5;

select to_char(sysdate,'Month') from dual;

Elements Of the Date  Model Function.







The To_number function converts a character value to a numeric data type.
if the string being converted contains non numeric characters, the function return an error.
Syntax
To_number(string1,[format],[nls_parameter])

SELECT  TO_NUMBER('129.24', '9G999D99')
FROM DUAL;
TO_NUMBER('129.24','9G999D99')
------------------------------
                        129.24

To_date Function

This function takes character values as input & return formatted date
Equivalent of the same.
To_date function allows users to enter a date in any format, then it converts the entry
Into the default format by oracle server.

Syntax:-To_date(strin1,[format_mask],[nls_language])

SELECT TO_DATE('July 15, 1987, 09:00 A.M.',  'Month dd, YYYY, HH:MI A.M.',  'NLS_DATE_LANGUAGE = American')
FROM DUAL
/

Read More

Convert Number Into Words

1. How can you convert a number into words using Oracle Sql Query?

Please see the query below:-

SELECT TO_CHAR (TO_DATE (523, 'j'), 'jsp') from dual


Output:
TO_CHAR(TO_DATE(523,'J'),
-------------------------
five hundred twenty-three

2. Write a pl sql block that count the department wise total salary where more than 5 employees are exists.

 SELECT  department_id, count(*),sum(salary) As totalsal
 FROM employees
 GROUP BY department_id
 HAVING COUNT(employee_id) >5

Given below is the output you get

DEPARTMENT_ID   COUNT(*)   TOTALSAL
------------- ---------- ----------
          100          6      51600
           30          6      24900
           50         45     156400
           80         34     304500

Read More

Create User in Oracle

CREATE USER user in Oracle

Controlling User Access

In a multiple-user environment, you want to maintain security of the database access and use. With Oracle server database security, you can do the following:
Control database access.
Give access to specific objects in the database.
Confirm given and received privileges with the Oracle data dictionary.
Create synonyms for database objects.
Database security can be classified into two categories: system security and data security. System security covers access and use of the database at the system level such as the username and password, the disk space allocated to users, and the system operations that users can perform. Database security covers access and use of the database objects and the actions that those users can have on the objects.

Privileges
Privileges are the right to execute particular SQL statements. The database administrator (DBA) is a high-level user with the ability to create users and grant users access to the database and its objects. Users require system privileges to gain access to the database and object privileges to manipulate the content of the objects in the database. Users can also be given the privilege to grant additional privileges to other users or to roles, which are named groups of related privileges.
Schemas
A schema is a collection of objects such as tables, views, and sequences. The schema is owned by a database user and has the same name as that user.

System Privileges
More than 100 distinct system privileges are available for users and roles. System privileges typically are provided by the database administrator.
Typical DBA Privileges

CREATE USER user              
IDENTIFIED BY   password;

Creating a User
The DBA creates a user by executing the CREATE USER statement. The user does not have any privileges at this point. The DBA can then grant privileges to that user. These privileges determine what the user can do at the database level

CREATE USER hema
IDENTIFIED BY   hema

Read More

What is View in oracle

How to Create SQL View

Introduce to SQL View

SQL View is a virtual table which is used to encapsulate a complex queries. After creating SQL view, you can treat a view as a table and

manipulate data on it with only some restrictions. When the data in table changes, the data in view which is dependent on table changes also.

View does not occupied the physical space as table does. The syntax of creating view as follows:


CREATE VIEW SALARY_REC
   AS
   SELECT e.last_name,
          e.salary,
          d.department_name AS department_name
   FROM employees e
   INNER JOIN departments d ON e.department_id = d.department_id

Output:

SELECT * FROM SALARY_REC


OConnell                        2860 Shipping
Grant                           2860 Shipping
Whalen                          4840 Administration
Hartstein                      14300 Marketing
Fay                             6600 Marketing
Mavris                          7150 Human Resources
Baer                           11000 Public Relations
Higgins                        13200 Accounting
Gietz                           9130 Accounting
Sullivan                        2750 Shipping
Geoni                           3080 Shipping
Sarchand                        4620 Shipping
Bull                            4510 Shipping

Read More

Working With Bfiles

Managing Bfiles

Manipulating Large Objects

A LOB is a data type that is used to store large, unstructured data such as text, graphic images, video clippings, and so

on. Structured data, such as a customer record, may be a few hundred bytes, but even small amounts of multimedia data can

be thousands of times larger. Also, multimedia data may reside in operating system (OS) files, which may need to be

accessed from a database.


There are four large object data types:
BLOB represents a binary large object, such as a video clip.
CLOB represents a character large object.
NCLOB represents a multibyte character large object.
BFILE represents a binary file stored in an OS binary file outside the database. The BFILE column or attribute stores a

file locator that points to the external file.

A table can have multiple LOB columns and object type attributes. A table can have only one LONG column.
The maximum size of LONGs is 2 GB; LOBs can be up to 4 GB.

There are two distinct parts to a LOB:
LOB value: The data that constitutes the real object being stored
LOB locator: A pointer to the location of the LOB value stored in the database.



What Are BFILEs?
BFILEs are external large objects (LOBs) stored in OS files that are external to database tables. The BFILE data type

stores a locator to the physical file. A BFILE can be in GIF, JPEG, MPEG, MPEG2, text, or other formats.

Securing BFILEs
Unauthenticated access to files on a server presents a security risk.Oracle Database 10g can act as a security mechanism

to shield the operating system from unsecured access while removing the need to manage additional user accounts on an

enterprise computer system.


A DIRECTORY is a nonschema database object that enables administration of access and usage of BFILEs in Oracle Database

10g.


Example :-


create table emp as select * from employees;
ALTER TABLE emp ADD (photo blob);
UPDATE emp
Empty_CLOB – It is a special function, which can be used in Insert or Update statement of SQL DML to initialize a NULL.
--The EMPTY_BLOB function returns an empty locator of type BLOB (binary large object).
--. It cannot be NULL. The locator might point to an empty BLOB value, but it will be a --valid BLOB locator.
SET photo = empty_blob( )
CREATE OR REPLACE DIRECTORY photo_folder AS 'F:\students:'
CONNECT sys as sysdba
GRANT CREATE ANY DIRECTORY TO Hr;

GRANT DROP ANY DIRECTORY TO hr;
connect hr/hr
CREATE OR REPLACE DIRECTORY photo_folder AS 'f:\students'

-----------------------------------------------
CREATE OR REPLACE PROCEDURE insert_photo
(p_empno NUMBER, p_photo VARCHAR2)
AS
f_photo BFILE;
b_photo BLOB;
BEGIN
-- Update the employee photo
UPDATE emp
SET photo = empty_blob()
WHERE employee_id = p_empno
RETURN photo into b_photo;
-- find where the photo's pointer is located.
f_photo := bfilename('PHOTO_FOLDER', p_photo);
-- open the photo as read-only option.
dbms_lob.fileopen(f_photo, dbms_lob.file_readonly);
-- load the photo into column photo.
dbms_lob.loadfromfile(b_photo,f_photo, dbms_lob.getlength(f_photo));
-- close the photo's pointer.
dbms_lob.fileclose(f_photo);
-- Save the loaded photo record.
COMMIT;
EXCEPTION
-- Check for your error messages
WHEN others THEN
dbms_output.put_line('*** ERROR *** Check you procedure.');
END;

EXECUTE insert_photo(198, '1.GIF')
EXECUTE insert_photo(124, '1.GIF')

  SELECT employee_id,first_name,
 dbms_lob.getlength(photo) "Photo Size"
 FROM emp

Read More

Locate a Test Center

1) PROMETRIC TESTING (P) LTD


MARWAH HOUSE, SAKI VIHAR ROAD
SAKI NAKA, ANDHERI EAST
MUMBAI, Maharashtra 400072
Phone: 022-28573160
Site Code: II004


2) NIIT LTD. BANDRA-MUMBAI


2ND FLOOR GURUVIDYA C.H.S
98/A HILL ROAD
OPP.BANDRA POLICE STATION
BANDRA(W)
MUMBAI, Maharashtra 400 050
Phone: 022-26451126/4493
Site Code: II7


3) HORIZON COMPUTER


A-WING
307, OM RACHNA BUILDING
SECTOR 17
VASHI
MUMBAI, Maharashtra
Phone: 022-55912954
Site Code: IIE69


4) ULTRAMAX INFONET TECHNOLOGIES


GROUND FLOOR
DNYAN SAGAR R.B.S.K BOLE ROAD
DADAR WEST
MUMBAI, Maharashtra 400028
Phone: 24221538
Site Code: IIC89


5) ACIT COMPUTER CENTRE


17, ARABIAN APTS
PITAMBER LANE, OPP DENA BANK
MAHIM (W)
MUMBAI, Maharashtra 400016
Phone: 24468281
Site Code: IIE76


6) CMC LIMITED


C-18,BANDRA-KURLA COMPLEX
BANDRA (EAST)
MUMBAI, Maharashtra 400051
Phone: 26591057
Site Code: IIG31


7) PACIFIC NETWORKS


2, SATENDRA BHAVAN, FIRST
FLOOR, NEW NAGARDAS ROAD,
NEAR PINKI CINEMA
ANDHERI (E)
MUMBAI, Maharashtra 400069
Phone: 66949109
Site Code: IIG36


8) THE SOFT ORIGIN


15 B , Sharda Estate
Madhavdas Pasta Road
Opposite Shree Sound Studio
MUMBAI, Maharashtra 400 014
Phone: 24166655/56
Site Code: IIG41


9) GATES


SENAPATI BAPAT MARG
C-8/A,KASTURCHAND MILLS COMPOUND
DADAR-W
MUMBAI, Maharashtra 400028
Phone: 24367658
Site Code: IIG92


10) CMS COMPUTERS LTD.


COMET 6TH FLOR
BANDRA STATION ROAD
BANDRA(W)
MUMBAI, Maharashtra 400050
Phone: 26418789/90
Site Code: IIH11


11) IIHT LTD


501 PARADISE TOWER
GOKHALE ROAD, NAUPADA
NEXT TO MC DONALDS, THANE(W)
MUMBAI, Maharashtra 400602
Phone: 25303707
Site Code: IIH29


12) NIIT LIMITED-GHATKOPAR-MUMBAI


NIIT LIMITED – GHATKOPAR
2ND FLR,JAYANT ARCADE, ABOVE
SHYAMRAO VITHAL BANK, RAJAWADI
CORNER,M.G.RD,GHATKOPAR (E)
MUMBAI, Maharashtra 400077
Phone: 25093013/14/15
Site Code: IIN30


13) NIIT LTD – CHURCHGATE


EXPRESS BULDING, 14TH E-ROAD
ABOVE SATKAR HOTEL
CHURCHGATE
MUMBAI, Maharashtra 400020
Phone: 22042442/6/9
Site Code: IIN41


14) SSI EDUCATION-ANDHERI-MUMBAI


SSI EDUCATION-ANDHERI
SUKHMANI BLDG,S.V.ROAD
ANDHERI-W
MUMBAI, Maharashtra 400058
Phone: 26705316/27
Site Code: IIO6


15) SSI EDUCATION- GHATKOPAR-MUMBAI


SSI EDUCATION- GHATKOPAR
SATYAM CENTRE, B-WING
M.G.ROAD,
GHATKOPAR
MUMBAI, Maharashtra 400077
Phone: 25100660
Site Code: IIO7


16) SSI EDUCATION – BORIVALI


SSI EDUCATION – BORIVALI-W
204/B, LAXMI PALACE,S V ROAD
ABOVE SONY MONY ELECTORNICS
BORIVALI-W
MUMBAI, Maharashtra 400092
Phone: 28334229
Site Code: IIO9


17) SQL STAR INTERNATIONL LTD-MUMBAI


2nd Floor,Citi Point
Telli Gully
Andheri East
MUMBAI, Maharashtra 400 069
Phone: 26822343/44
Site Code: IIU4


18) CMS COMPUTERS INSTITUTE


NO 301 TO 306 SHOPPERS POINTS BLDG
3RD FLOOR
S.V.ROAD ANDHERI(W)
MUMBAI, Maharashtra 400058
Phone: 26202522/26202533
Site Code: IIH16


19) NIIT LTD. VASI-MUMBAI


1 ST FLOOR SHANTI CENTRE
Sector 17
Near Hotel Navratna
Vashi
Navi – MUMBAI, Maharashtra 400705
Phone: 27896359/27894524
Site Code: IIN33


20) SSI EDUCATION – VASHI


SSI EDUCATION
SECTOR -17, SHANTI CENTRE
NAVI MUMBAI, Maharashtra 400706
Phone: 55911248/49/50
Site Code: IIO29



- See more at: http://www.javabeat.net/prometric-centers/prometric-centers-in-mumbai/#sthash.pFO06bFf.dpuf

Read More

Row Level Trigger In Oracle

create table emp(id number primary key,fname varchar2(50),age number,

address varchar2(50),salary  number)

The following is the program creates a row level trigger

For the EMP table that would fire for insert or update or delete data manipulation performed on the table.

create or replace trigger disp_sal

before delete or insert or update on emp

for each row

when(new.id > 0)

declare

diff_in_sal  number;

begin

diff_in_sal := :new.salary - :old.salary;

dbms_output.put_line('Old Salary :' || :old.salary);

dbms_output.put_line('New salary :' ||:new.salary);

dbms_output.put_line('Differance in salary ' || diff_in_sal);

end;

/

in this example  trigger fire for before insert/delete/update :OLD.salary  will contain the old salary before trigger fire.

: New.salary will contain new value after trigger fire. It will just keep a track of an employee’s previous salary.

Now Let us perform some DML operation on emp table.

SQL> insert into emp values(10,'Rutika',21,'Delhi',5000);

Old salary:

New salary: 5000

Salary difference:

Old Salary :

New salary :5000

Differance in salary

1 row created.

SQL> insert into emp values(20,'Sneha',22,'Mumbai',9000);

Old salary:

New salary: 9000

Salary difference:

Old Salary :

New salary :9000

Differance in salary

1 row created.

select * from emp;

Now let us perform update operation on emp table.

SQL> update emp set salary=salary+1000

  2  where id=10;

Old salary: 5000

New salary: 6000

Salary difference: 1000

Old Salary :5000

New salary :6000

Differance in salary 1000

1 row updated.

Read More

IF-THEN-STATEMENT IN ORACLE

The following is the example using IF_Then statement  in an oracle
 Note:- Once a condition is found is true,then if-Then-statement will execute the corresponding code and not evaluate the conditions any further.

SQL> DECLARE
  2    V_NAME  EMPLOYEES.LAST_NAME%TYPE;
  3    V_SAL   EMPLOYEES.SALARY%TYPE;
  4    V_BONUS V_SAL%TYPE;
  5  BEGIN
  6    SELECT LAST_NAME,SALARY
  7    INTO   V_NAME,V_SAL
  8    FROM   EMPLOYEES
  9    WHERE  EMPLOYEE_ID=&EMPNO;
 10
 11    IF V_SAL   >= 20000 THEN
 12       V_BONUS := V_SAL*.2;
 13    ELSIF V_SAL>=15000 THEN
 14       V_BONUS := V_SAL*.3;
 15    ELSIF V_SAL>= 10000 THEN
 16       V_BONUS := V_SAL *.4;
 17    ELSE
 18       V_BONUS := V_SAL*.5;
 19    END IF;
 20
 21    DBMS_OUTPUT.PUT_LINE('BONUS OF '||V_NAME||' IS '||V_BONUS);
 22  END;
 23  /
Enter value for empno: 200
old   9:   WHERE  EMPLOYEE_ID=&EMPNO;
new   9:   WHERE  EMPLOYEE_ID=200;

PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> /
Enter value for empno: 200
old   9:   WHERE  EMPLOYEE_ID=&EMPNO;
new   9:   WHERE  EMPLOYEE_ID=200;
BONUS OF Whalen IS 2200

PL/SQL procedure successfully completed.

Read More

triggers in Oracle

Trigger Action

A trigger act is the procedure (pL/SQL block) that contains the SQL statement and pL/SQL code to be executed

when a triggering statement is issued and the trigger restriction evaluates to TRUE.
Like to stored procedures, a trigger action can contain SQL language constructs (variables ,constants ,cursors,

exceptions, and so on),and call stored procedures. As well, for row trigger, the statement in a trigger action

have access to column values (new and old) of the current row being handle by the trigger. TWO relationship

names provide access to the old and new values for each column.
TYPES OF Trigger
When you name a trigger , you can specify the number of times the trigger action is to be executed. once for

every row affected by the triggering statement(such as might be fired by UPDATE statement that updates many

rows), or once for the triggering statement  , no  matter how many rows it affects.

ROW Triggers: A row trigger is fired each time the table is affected by the triggering statement. For example,

if an UPDATE statement updates multiple rows of a table, a row trigger is fired once for each row affected by

the UPDATE statement. IF a triggering statement affects no rows, a row trigger is not executed at all.
ROW triggers are helpful if the code in the trigger action depends on data provided by the triggering statement

or rows that are affected. FOR example, Figure 15-3 shows  a row trigger that uses the values of each row

affected by the triggering statement .

Triggers are stored programs, which are automatically executed or fired when some event take place.

These triggers are in fact, written to be executed to any of the following actions.

1.DML A database manipulation.

2.DDL A database definition statements like (create, alter, drop)

Triggers could be defined on tables, views, schemas, database.

Advantages of Triggers

1. Enforcing referential integrity.

2 ensure a data

3. Preventing Invalid/unacceptable Transaction

4. Imposing security consent.

Let us learn how to create a Before Trigger in Oracle.

Before Insert Trigger means that oracle will fire

Triggers before the insert operation takes place.

Syntax

The following is the syntax of before Trigger

Create [or replace ] Trigger trigger_name

Before Insert

On Table_name

[for each row ]

Declare

--variable declaration

Begin

-- Trigger Code

Exception

When ....

---Exception Handling

End;

Parameters or arguments

Or Replace is optional. It allows you to re-create

Triggers with new definition.

trigger_name is the name of trigger to create.

Before Insert indicates that this trigger will fire

before insert operations is executed.

Table_name is the name of table that the trigger is

Created on.

Let us try an example given below.

We will create a table.

SQL> create table emp(empno number primary key,

  2  last_name varchar2(50),

  3  city varchar2(50),

  4  salary varchar2(50));

Table created.

Now we will create a trigger emp_insert on emp table

which will force all last_name to uppercase.

Create or replace trigger emp_insert

before insert

on emp

for each row

declare

s_flag number;

begin

:new.last_name :=upper(:new.last_name);

End;

/

Now let us add a row in emp table as ....

SQL> insert into emp values (101,'smith','mumbai',12000);

We will get the following output.

SQL> select empno,last_name from emp;

     EMPNO LAST_NAME

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

       101 SMITH

BEFORE vs. AFTER Triggers

When defining a trigger , you can identify the trigger timing. That is , you can give whether the

Trigger action is to be executed before or after the triggering statement . BEFORE and AFTER apply

to both statement and row triggers.

BEFORE Triggers BEFORE triggers execute the trigger action before the triggering statement .

This type of trigger is generally used in the following condition:

·         BEFORE triggers are used when the trigger action should settle on whether the triggering statement should be allowed to complete. By using a BEFORE trigger for this reason, you

Can reduce unnecessary processing of the triggering statement and its eventual rollback in

Cases where an exception is raised in the trigger action.

·         BEFORE triggers are used to obtain specific column values before completing a triggering INSERT or UPDATE statement.

AFTER Triggers AFTER triggers execute the trigger action after the triggering statement is

Executed. AFTER triggers are used in the following situations:

·         AFTER triggers are used when you want the trigger statement to complete before executing

               Before executing the trigger action.

·         If a BEFORE trigger is already present , an AFTER trigger can execute different actions on the

              Same triggering statement.

Combinations

Using the options listed in the previous two divisions, you can create  four types of triggers :

·         BEFORE statement trigger  Before executing the triggering statement statement, the trigger

Action is executed.

·         Before row trigger Before modifying each row affected by the triggering statement and

Before checking appropriate integrity constraints, the trigger action is executed

Provided that the trigger restriction was not violated.

·         AFTER statement trigger  After executing the triggering statement and applying any deferred integrity constraints, the trigger action is executed.

 

Read More

PLSQL-Interview-Questions

1.How Many triggers can be applied to a table?

There are maximum 12 triggers can be applied to a table.

2. Difference between Syntax error & runtime error in PLSQL?

A Syntax error is easily identified by a PLSQL compiler.For example Incorrect spelling.

but runtime error can be handled with the help of Exception Section block in PLSQL.

3.How to disabled multiple triggers  of a table at a time ?

Alter table Table_name disable   all triggers.

4. Where is the pre defined procedures stored?

In oracle standard package,procedures,functions.

5.What is a cursor for loop?
Cursor for loop implicitly declares %ROWTYPE as loop index,opens a cursor,fetches rows of a table values from active set into record variable
& it closes when all the records have been carried out process.

6.What is the difference between Procedure and function?

Function must return value and procedure does not.

Function can be used in sql with some restrictions. where as procedure can not be called directly from sql.

Read More