Full text

(1)

PL/SQL

Introduction, Shortcoming in SQL, Structure of PL/SQL, PL/SQL Language Elements, Data Types, Operators Precedence, Control Structure, Steps to Create a PL/SQL, Program, Iterative Control, Cursors, Steps to create a Cursors, Procedure, Function, Packages, Exceptions

Handling, Database Triggers, Types of Triggers. Introduction

The development of database applications typically requires language constructs similar to those that can be found in programming languages such as C, C++, or Pascal. These constructs are necessary in order to implement complex data structures and algorithms. A major restriction of the database language SQL, however, is that many tasks cannot be accomplished by using only the provided language elements.

PL/SQL (Procedural Language/SQL) is a procedural extension of Oracle-SQL that o_ers language constructs similar to those in imperative programming languages. PL/SQL allows users and designers to develop complex database applications that require the usage of control structures and procedural elements such as procedures, functions, and modules.

The basic construct in PL/SQL is a block. Blocks allow designers to combine logically related (SQL-) statements into units. In a block, constants and variables can be declared, and variables can be used to store query results. Statements in a PL/SQL block include SQL statements, control structures (loops), condition statements (if-then-else), exception handling, and calls of other PL/SQL blocks.

PL/SQL blocks that specify procedures and functions can be grouped into packages. A package is similar to a module and has an interface and an implementation part. Oracle o_ers several predefined packages, for example, input/output routines, file handling, job scheduling etc.

Another important feature of PL/SQL is that it o_ers a mechanism to process query results in a tuple-oriented way, that is, one tuple at a time. For this, cursors are used. A cursor basically is a pointer to a query result and is used to read attribute values of selected tuples into variables. A cursor typically is used in combination with a loop construct such that each tuple read by the cursor can be processed individually.

In summary, the major goals of PL/SQL are to • increase the expressiveness of SQL,

(2)

• develop modular database application programs, • reuse program code, and

• reduce the cost for maintaining and changing applications.

Structure of PL/SQL-Blocks

PL/SQL is a block-structured language. Each block builds a (named) program unit, and blocks can be nested. Blocks that build a procedure, a function, or a package must be named.

A PL/SQL block has an optional declare section, a part containing PL/SQL statements, and an optional exception-handling part. Thus the structure of a PL/SQL looks as follows (brackets [ ] enclose optional parts):

[<Block header>] [declare

<Constants> <Variables> <Cursors>

<User defined exceptions>] begin

<PL/SQL statements> [exception

<Exception handling>] end;

The block header specifies whether the PL/SQL block is a procedure, a function, or a package. If no header is specified, the block is said to be an anonymous PL/SQL block. Each PL/SQL block again builds a PL/SQL statement. Thus blocks can be nested like blocks in conventional programming languages. The scope of declared variables (i.e., the part of the program in which one can refer to the variable) is analogous to the scope of variables in programming languages

such as C or Pascal.

Declarations

Constants, variables, cursors, and exceptions used in a PL/SQL block must be declared in the declare section of that block. Variables and constants can be declared as follows:

(3)

Valid data types are SQL data types (see Section 1.1) and the data type boolean. Boolean data may only be true, false, or null. The not null clause requires that the declared variable must always have a value di_erent from null. <expression> is used to initialize a variable.

If no expression is specified, the value null is assigned to the variable. The clause constant states that once a value has been assigned to the variable, the value cannot be changed (thus the variable becomes a constant).

Example:

Declare hire date date; /* implicit initialization with null */ job title varchar2(80) := ’Salesman’;

emp found boolean; /* implicit initialization with null */ salary incr constant number(3,2) := 1.5; /* constant */ . . .

begin . . . end;

(4)

DATATYPES

PL/SQL provides subtypes of data types. For example, the data type NUMBER has a subtype called INTEGER. You can use the subtypes in your PL/SQL program to make the data types compatible with data types in other programs while embedding the PL/SQL code in another program, such as a Java program.

PL/SQL Numeric Data Types and Subtypes

Following table lists out the PL/SQL pre-defined numeric data types and their sub-types –

S.No Data Type & Description

1

NUMBER(prec, scale)

Fixed-point or floating-point number with absolute value in range 1E-130 to (but not including) 1.0E126. A NUMBER variable can also represent 0

2

FLOAT

ANSI and IBM specific floating-point type with maximum precision of 126 binary digits (approximately 38 decimal digits)

3 DATE

DATE datatype stores valid date time format with fixed length.

4 INTEGER

ANSI and IBM specific integer type with maximum precision of 38 decimal digits

Following is a valid declaration – DECLARE

num1 INTEGER; num2 REAL;

num3 DOUBLE PRECISION; BEGIN

null; END; /

(5)

PL/SQL procedure successfully completed

PL/SQL Character Data Types and Subtypes

Following is the detail of PL/SQL pre-defined character data types and their sub-types − S.No Data Type & Description

1 CHAR

Fixed-length character string with maximum size of 32,767 bytes

2 VARCHAR2

Variable-length character string with maximum size of 32,767 bytes

3

RAW

Variable-length binary or byte string with maximum size of 32,767 bytes, not interpreted by PL/SQL

4 ROWID

(6)

PL/SQL Operator Precedence

Operator precedence determines the grouping of terms in an expression. This affects how an expression is evaluated. Certain operators have higher precedence than others; for example, the multiplication operator has higher precedence than the addition operator.

For example, x = 7 + 3 * 2; here, x is assigned 13, not 20 because operator * has higher precedence than +, so it first gets multiplied with 3*2 and then adds into 7.

Here, operators with the highest precedence appear at the top of the table, those with the lowest appear at the bottom. Within an expression, higher precedence operators will be evaluated first.

The precedence of operators goes as follows: =, <, >, <=, >=, <>, !=, ~=, ^=, IS NULL, LIKE, BETWEEN, IN.

Show Examples

Operator Operation

** exponentiation

+, - identity, negation

*, / multiplication, division

+, -, || addition, subtraction, concatenation

== comparison

NOT logical negation

(7)

OR inclusion

CONDITIONAL CONTROL STATEMNETS

In PL/SQL there are three types of conditional control statements: IF, ELSIF and CASE

IF – THEN Statement

This is the most basic kind of a conditional control and has the following structure

If Condition Then Statement 1; ….

Statement 2; End If;

The reserved word IF marks the beginning of the IF statement.

Example:

Q) Write a PL/SQL block to swap two numbers when the first number is greater than second number ?

SQL> declare

a number(10) := &a; b number(10) := &b; c number(10); begin

dbms_output.put_line('a value ='||a||' b value ='||b); if a>b then

c := a; a := b; b := c; end if;

dbms_output.put_line('a value ='||a||' b value ='||b); end;

(8)

OUTPUT:

Enter value for a: 20

old 2: a number(10) := &a; new 2: a number(10) := 20; Enter value for b: 10

old 3: b number(10) := &b; new 3: b number(10) := 10; a value =20 b value =10 a value =10 b value =20

PL/SQL procedure successfully completed.

IF – THEN – ELSE

This statement enables you to specify two groups of statements One group of statements is executed when the condition evaluates to TRUE and the other group of statements is executed when the condition evaluates to FALSE.

If Condition Then Statement 1; ELSE

Statement 2; End If;

Statement 3; Example:

Q) Write a PL/SQL block to test whether the given number is odd or even

SQL> declare

a number(10) := &a; begin

if mod(a,2)=0 then

dbms_output.put_line('a value is even'); else

dbms_output.put_line('a value is odd'); end if;

end; /

Enter value for a: 44

(9)

a value is even

PL/SQL procedure successfully completed. SQL> /

Enter value for a: 33

old 2: a number(10) := &a; new 2: a number(10) := 33; a value is odd

PL/SQL procedure successfully completed.

ELSIF Statement :

This statement has the following structure

If Condition 1 Then Statement 1; ELSIF Condition 2 Then

Statement 2; ELSIF Condition 3 Then

Statement 3; …

ELSE

Statement 4; END IF;

Example:

Q)Write a PL/SQL block to find the grade of sailor for a given sid 10, 9, 8 – Grade A

7, 6, 5 – Grade B other – Grade C

SQL> declare

2 a number(10) := &a; c number(10);

begin

select rating into c from sailors where sid = a; if c in (10,9,8) then

dbms_output.put_line('sailor '||a||' has grade A'); elsif c in (7,6,5) then

dbms_output.put_line('sailor '||a||' has grade B'); else

(10)

end if; end;

/

Enter value for a: 22

old 2: a number(10) := &a; new 2: a number(10) := 22; sailor 22 has grade B

PL/SQL procedure successfully completed. CASE:

A case statement has the following structure:

CASE SELECTOR

WHEN EXPRESSION 1 STATEMENT 1; WHEN EXPRESSION 1 STATEMENT 1; …..

WHEN EXPRESSION 1 STATEMENT 1; ELSE STATEMENT N+1;

END CASE;

The reserved word CASE marks the beginning of the case statement. A selector is a value that determines which WHEN clause should be executed.

Example:

Q7) Write a PL/SQL block to print the day name for a given date?

SQL> declare

a date := '&a'; b char(10); begin

b := to_char(a,'D'); case b

when '1' then

dbms_output.put_line('today is sunday'); when '2' then

dbms_output.put_line('today is monday'); when '3' then

dbms_output.put_line('today is thuesday'); when '4' then

(11)

when '5' then

dbms_output.put_line('today is thrusday'); when '6' then

dbms_output.put_line('today is friday'); when '7' then

dbms_output.put_line('today is saturday'); end case;

end; /

Enter value for a: 10-mar-09 old 2: a date := '&a';

new 2: a date := '10-mar-09'; today is thuesday

PL/SQL procedure successfully completed.

ITERATIVE CONTROL

In PL/SQL there are three types of loops : Simple LOOP, WHILE loops and Numeric FOR loop

A simple loop, as you can see from its name, is the most basic kind of loop and has the following structure:

LOOP

STATEMENT 1; STATEMENT 2; …….

STATEMENT N; END LOOP;

The reserved word LOOP marks the beginning of the simple loop. Statement 1 through N are a sequence of statements that is executed repeatedly.

EXIT statement causes a loop to terminate when exit condition evaluates to TRUE.

LOOP

STATEMENT 1; STATEMENT 2; IF CONDITION THEN

(12)

END IF; …….

STATEMENT N; END LOOP;

Example:

Q8) Write a PL/SQL block to print number from 1 to 5 using loop statements

SQL> declare

a number :=0; begin

loop a := a+1;

dbms_output.put_line('a value'||a); if a>5 then

exit; end if; end loop; end; /

a value1 a value2 a value3 a value4 a value5 a value6

PL/SQL procedure successfully completed.

Note: here numbers are printed 1 to 6 because this loop acts as do-while so it executes the statements and then check the condition next.

LOOP

STATEMENT 1; STATEMENT 2;

EXIT WHEN CONDITION; …….

(13)

Q8) Write a PL/SQL block to print number from 1 to 5 using loop statements

SQL> declare

a number :=0; begin

loop a := a+1;

dbms_output.put_line('a value'||a); exit when a>5 ;

end loop; end; /

a value1 a value2 a value3 a value4 a value5 a value6

PL/SQL procedure successfully completed.

WHILE LOOPS :

A while loop has the following structure

WHILE CONDITION LOOP STATEMNET 1; STATEMNET 2; ……

(14)

The reserved word WHILE marks the beginning of a loop construct. The word CONDITION is the test condition of the loop that evaluates to TRUE or FALSE.

Example:

Q9) Write a PL/SQL block to print number from 1 to 5 using while loop statements

SQL> declare

a number:=1; begin

while a<5 loop

dbms_output.put_line('a value'||a); a := a+1;

end loop; * end; SQL> / a value1 a value2 a value3 a value4

PL/SQL procedure successfully completed.

NUMERIC FOR LOOP:

A numeric FOR loop is called numeric because it requires an integer as its terminating value. Its structure is as follows.

FOR loop_counter IN[REVERSE] Lower_limit..upper_limit LOOP STATEMENT 1;

STATEMENT 2; ……

STATEMENT N; END LOOP;

(15)

Example:

Q10) Write a PL/SQL block to print number from 1 to 5 using for loop statements

SQL> begin

for a in reverse 1..5 loop

dbms_output.put_line('a value'||a); end loop;

end; /

a value5 a value4 a value3 a value2 a value1

(16)

CURSOR

A cursor is a handle or pointer to the context area. Through the cursor a PL/SQL program can control the context area and what happens to it as the statements is processed.

1) Cursor allow you to fetch and process rows returned by a SELECT statement, one row at a time.

2) A cursor is named so that it can be referenced.

Types of cursors:

1. An implicit cursor is automatically declared by oracle every time an SQL

statement is executed. The user will not be aware of this happening and will not be able to control or process the information in an implicit cursor.

2. An explicit cursor is defined by the program for any query that returns more than one row of data. That means the programmer has declared the cursor within the PL/SQL code block. This declaration allows for the application to sequentially process each row of data as it is returned by the cursor.

IMPLICIT CURSOR

In order to better understand the capabilities of an explicit cursor, you first need to run through the process of an implicit cursor.

1) Any given PL/SQL block issues an implicit cursor when ever an SQL statement is executed, as long as an explicit cursor does not exist for that SQL statement. 2) A cursor is automatically associated with every DML statement.

3) All UPDATE and DELETE statements have cursors that identify the set od rows that will be affected by the operations.

4) An INSERT statement needs a place to receive the data that is to be inserted in the database; the implicit cursor fulfills this need.

5) The most recently opened cursor is called the ‘SQL%’ cursor.

EXPLICIT CURSOR

(17)

1) Declaring the cursor defines the name of the cursor and associatedit with a SELECT statements

CURSOR cursor_name IS SELECT statement

2) Opening the cursor is to process the select statement and set a active pointer to the first row

OPEN cursor_name;

3) Fetching cursor into PL/SQL local variables, these variables are declared as ROWTYPE in declare section

FETCH cursor_name INTO PL/SQL variables

4) Closing a cursor by CLOSE cursor_name;

Explicit cursor attributes

Cursor Attribute Syntax Explanation

%NOTFOUND Cursor_name%NOTFOUND A Boolean attribute that returns TRUE if the previous FETCH did not return a row and FALSE if it did

%FOUND Cursor_name%FOUND A Boolean attribute that returns TRUE if the previous FETCH did return a row and FALSE if it did not

%ROWCOUNT Cursor_name%ROWCOUNT # of records fetched from a cursor at that point in time %ISOPEN Cursor_name%ISOPEN A Boolean attribute that returns

TRUE if cursor is open, FALSE if it is not.

Q22) Create a cursor which fetches data from sailors table whose rating is greater than 5?

SQL> declare

cursor sail is select sid,sname,age from sailors where rating>5; s sail%rowtype;

(18)

open sail; loop

fetch sail into s;

exit when sail%notfound;

dbms_output.put_line('sailor sid = '||s.sid||' name = '||s.sname||' age = ' ||s.age);

end loop; close sail; end; /

sailor sid = 64 name = horatio age = 35 sailor sid = 22 name = dustin age = 45 sailor sid = 31 name = lubber age = 55.5 sailor sid = 32 name = andy age = 25.5 sailor sid = 58 name = rusty age = 35 sailor sid = 71 name = zobra age = 16 sailor sid = 74 name = horatio age = 35

PL/SQL procedure successfully completed.

Q24) Create cursor which fetches data from reserves table where sid is22?

SQL> declare

cursor reser is select * from reserves where sid=22; r reser%rowtype;

begin open reser; loop

fetch reser into r;

exit when reser%notfound;

dbms_output.put_line('reserve sid = '||r.sid||' bid = '||r.bid||' day = '|| r.day);

end loop; close reser; * end; SQL> /

reserve sid = 22 bid = 101 day = 10-OCT-98 reserve sid = 22 bid = 102 day = 10-OCT-98 reserve sid = 22 bid = 103 day = 10-SEP-98 reserve sid = 22 bid = 104 day = 10-JUL-98

(19)

PROCEDURES:

Modular code :

A PL/SQL module is any complete logical unit of work. There are four types of PL/SQL modules: 1) anonymous blocks that are run with a text script( you have used until now), 2) Procedures, 3) Functions, and 4) Packages.

There are two main benefits to using modular code: 1) it is more reusable and 2) it is more manageable.

Procedure:

A procedure is a module performing one or more actions: it does not need to return any value.

The syntax for creating a procedure is

CREATE OR REPLACE PROCEDURE name

[(PARAMETER 1 {IN,OUT,INOUT} DATATYPE(SIZE), PARAMETER 2 {IN,OUT,INOUT} DATATYPE(SIZE),…. PARAMETER N {IN,OUT,INOUT} DATATYPE(SIZE))] AS

[local declaration] BEGIN

Executable statements [EXCEPTION

exception handler] END [name];

Example

Q16) create a procedure to add two number and call the block with a PL/SQL block?

(20)

c number := 1; begin

c := a+b;

dbms_output.put_line('c value '||c); end;

/

Procedure created.

SQL> declare

a number := &a; b number := &b; begin

sum(a,b); end; /

Enter value for a: 10 old 2: a number := &a; new 2: a number := 10; Enter value for b: 20 old 3: b number := &b; new 3: b number := 20; c value 30

PL/SQL procedure successfully completed.

Q17) Create a procedure to add two number and return the value to a PL/SQL block?

SQL> create or replace procedure sum1(a in number,b in number,d out number) is

c number := 1; begin

c := a+b; d:=c; end; /

Procedure created.

SQL> declare

(21)

d number; begin

sum1(a,b,d);

dbms_output.put_line('d value '||d); end;

/

Enter value for a: 10 old 2: a number := &a; new 2: a number := 10; Enter value for b: 30 old 3: b number := &b; new 3: b number := 30; d value 40

PL/SQL procedure successfully completed.

Q18) Create a procedure to accept sailors sid and return age of sailor to a PL/SQL block? SQL> create or replace procedure sail(a in number,b out number)

is

c number; begin

select age into c from sailors where sid = a; b := c;

exception

when no_data_found then

dbms_output.put_line('no such sailors'); end;

/

Procedure created.

SQL> declare

a number := &a; b number; begin sail(a,b);

dbms_output.put_line('sailors with '||a||'has age '||b); end;

/

(22)

sailors with 64has age 35

PL/SQL procedure successfully completed.

FUNCTION:

The syntax for creating a function is as follows:

CREATE OR REPLACE FUNCTION name

[(PARAMETER 1 {IN,OUT,INOUT} DATATYPE(SIZE), PARAMETER 2 {IN,OUT,INOUT} DATATYPE(SIZE),…. PARAMETER N {IN,OUT,INOUT} DATATYPE(SIZE))] RETURN datatype

IS

[local declaration] BEGIN

Executable statements [EXCEPTION

exception handler] END [name];

The function does not necessarily have any parameters, but it must have a RETURN value declared in the header, and it must return values for all the varying possible execution streams.

Q19) Create a function to add two number and return the value to a PL/SQL block?

SQL> create or replace function f(a in number) return number

as

b number; begin b:=10; b:=a+b; return b; end; /

(23)

SQL> declare

a number:=10; c number; begin c := f(a);

dbms_output.put_line('c value'||c); end;

/

c value20

PL/SQL procedure successfully completed.

Q20) Create a function to accept sailors sid and return age of sailor to a PL/SQL block?

SQL> create or replace function sailf(a in number) return number

as

b number; begin

select age into b from sailors where sid=a; return b;

exception

when no_data_found then

dbms_output.put_line('no such sailors'); end;

/

Function created.

SQL> declare

a number:= &a; c number; begin c := sailf(a);

dbms_output.put_line('sailor with sid '||a||'has age '||c); end;

/

(24)

new 2: a number:= 58; sailor with sid 58has age 35

PL/SQL procedure successfully completed.

Packages

Packages are schema objects that groups logically related PL/SQL types, variables, and subprograms.

A package will have two mandatory parts −

 Package specification

 Package body or definition

Package Specification

The specification is the interface to the package. It just DECLARES the types, variables, constants, exceptions, cursors, and subprograms that can be referenced from outside the package. In other words, it contains all information about the content of the package, but excludes the code for the subprograms.

All objects placed in the specification are called public objects. Any subprogram not in the package specification but coded in the package body is called a private object.

Example

The following program provides a more complete package. We will use the CUSTOMERS table stored in our database with the following records −

Select * from customers;

+----+---+---+---+---+

| ID | NAME | AGE | ADDRESS | SALARY | +----+---+---+---+---+

(25)

The following code snippet shows a package specification having a single procedure. You can have many global variables defined and multiple procedures or functions inside a package.

CREATE PACKAGE cust_sal AS

PROCEDURE find_sal(c_id customers.id%type); END cust_sal;

/

When the above code is executed at the SQL prompt, it produces the following result − Package created.

Package Body:

The package body has the codes for various methods declared in the package specification and other private declarations, which are hidden from the code outside the package.

The CREATE PACKAGE BODY Statement is used for creating the package body. The following code snippet shows the package body declaration for the cust_sal package created above. CREATE OR REPLACE PACKAGE BODY cust_sal AS

PROCEDURE find_sal(c_id customers.id%TYPE) IS c_sal customers.salary%TYPE;

BEGIN

SELECT salary INTO c_sal FROM customers

WHERE id = c_id;

dbms_output.put_line('Salary: '|| c_sal); END find_sal;

END cust_sal; /

When the above code is executed at the SQL prompt, it produces the following result − Package body created.

Using the Package Elements

The package elements (variables, procedures or functions) are accessed with the following syntax −

package_name.element_name;

Consider, we already have created the above package in our database schema, the following program uses the find_sal method of the cust_salpackage −

(26)

code customers.id%type := &cc_id; BEGIN

cust_sal.find_sal(code); END;

/

When the above code is executed at the SQL prompt, it prompts to enter the customer ID and when you enter an ID, it displays the corresponding salary as follows −

Enter value for cc_id: 1 Salary: 3000

(27)

Exception Handling

A PL/SQL block may contain statements that specify exception handling routines. Each error or warning during the execution of a PL/SQL block raises an exception. One can distinguish between two types of exceptions:

• system defined exceptions

• user defined exceptions (which must be declared by the user in the declaration part of a block where the exception is used/implemented)

System defined exceptions are always automatically raised whenever corresponding errors or warnings occur. User defined exceptions, in contrast, must be raised explicitly in a sequence of statements using raise <exception name>. After the keyword exception at the end of a block, user defined exception handling routines are implemented. An

implementation has the pattern when

<exception name> then <sequence of statements>;

The most common errors that can occur during the execution of PL/SQL programs are handled by system defined exceptions. The table below lists some of these exceptions with their names and a short description.

Exception name Number Remark

CURSOR ALREADY OPEN

ORA-06511 You have tried to open a cursor which is already open

INVALID CURSOR ORA-01001 Invalid cursor operation such as fetching from a closed cursor

NO DATA FOUND ORA-01403 A select . . . into or fetch statement returned no tuple

(28)

ZERO DIVIDE ORA-01476 You have tried to divide a number by 0

Q3) Write a PL/SQL programe to extract sailors sid and sname for a given sid?

SQL> declare

a number(10); b varchar2(10); c number(10) := &c; begin

select sid,sname into a,b from sailors where sid = c;

dbms_output.put_line('sailors id = '||a||'sailors Name= '||b); exception

when no_data_found then

dbms_output.put_line('no such sailor found with sid = '||c); end;

/

Enter value for c: 22

old 4: c number(10) := &c; new 4: c number(10) := 22;

sailors id = 22 sailors name = Dustin

PL/SQL procedure successfully completed.

SQL>/

Enter value for c: 20

old 4: c number(10) := &c; new 4: c number(10) := 20; no such sailor found with sid = 20

PL/SQL procedure successfully completed.

(29)

COMMIT- makes events within a transaction permanent ROLLBACK – Erases events within a transaction

SAVEPOINT – We can control transactions by using save points. These break transactions into pieces so that we can roll back to a specified save point with out roll backing the total transaction.

SYNTAX: savepoint name;

rollback to savepoint name; Example:

SQL> create table empl(eid number(10),ename varchar(10),desg archar(10),salary number(5));

Table created

SQL> begin savepoint a;

insert into empl values (101,'abc','acc',2000); savepoint b;

insert into empl values (102,'bcd','clk',3000); savepoint c;

insert into empl values (103,'sde','gar',1200); rollback to c;

end; /

PL/SQL procedure successfully completed

SQL> select * from empl;

EID ENAME DSEG SALARY

101 abc acc 2000

102 Bcd clk 3000

(30)

TRIGGER

A trigger is a procedure i.e., run automatically by the database when a specified DML statement (INSERT, UPDATE, DELETE) is run against a certain database .

A trigger may fire before or after DML statements run.

A row level trigger runs each and every row updated in the table

For example , if we update a 10 rows in the table the row level trigger runs trigger body 10 times.

A statement level trigger runs only once when we update a table.

For example , when we update a 10 rows in the table the statement level trigger runs only once trigger body

A row level trigger has access to the old the new column values when the trigger fires. As a result of an update statement on that column.

CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE | AFTER }trigger_event

ON table_name [FOR EACH ROW]

[WHEN trigger_condition] BEGIN

Trigger_body ; END trigger_name ;

Where OR REPLACE means the trigger is to replace an existing trigger , if present trigger_nameis the name of the trigger

BEFORE means the trigger fires before the trigger_event is performed AFTER means the trigger fires after the trigger_event is performed trigger_event is the event that causes the trigger to fire

table_name is the table name that the trigger references

FOR EACH ROW : means the trigger is a row level trigger , if we vomit , FOR EACH ROW the trigger is a statement level trigger .

(31)

Eg.

CREATE OR REPLACE trigger before_product_update BEFORE update of price on product for each row when (new.price<old.price * 0.75)

begin

dbms_output.put_line(‘Product_id’||:old.prod_id); dbms_output.put_line(‘Old_Price’||:old.price); dbms_output.put_line(‘New_Price’||:new.price);

dbms_output.put_line(‘The price reduction is more than 25%’); Insert into product_audit(prod_id,old_price,new_price) values(:old.prod_id,:old.price,:new.price)

End before_produt_update;

FIRING A TRIGGER

To fire a trigger we must perform the DML operations on the table. Eg : Update product set price = price * 0.70 where prod_id in (10,15);

Example:

To write a TRIGGER to ensure that SAILORS TABLE does not contain duplicate of null values in SID column.

CREATE OR RELPLACE TRIGGER trig1 before insert on sailors

for each row DECLARE a number; BEGIN

if(:new.sid is Null) then

raise_application_error(-20001,'error::sid cannot be null'); else

select count(*) into a from sailors where sid=:new.sid; if(a=1) then

raise_application_error(-20002,'error:: cannot have duplicate sid'); end if;

end if; END;

Trigger created.

SQL>select * from sailors;

(32)

22 Dustin 7 45.0

29 Brutus 1 33.0

31 Lubber 8 55.5

32 Andy 8 25.5

58 Rusty 10 35.0

64 Horatio 7 35.0

71 Zobra 10 16.0

74 Horatio 9 35.0

85 Art 3 25.5

95 Bob 3 63.5

SQL> insert into sailors values(&sid,'&sname',&rating,&age); Enter value for sid: null

Enter value for sname: mark Enter value for rating: 7 Enter value for age: 35.5

old 1: insert into sailors values((&sid,'&sname',&rating,&age) new 1: insert into sailors values(null,’mark',7,35.5)

insert into sailors values(null,’mark',7,35.5) *

ERROR at line 1:

ORA-20001: error::sid cannot be null ORA-06512: at "SCOTT.TRIG1", line 5

ORA-04088: error during execution of trigger 'SCOTT.TRIG1' SQL> /

Enter value for deptnp: 22 Enter value for dname: mana Enter value for rating: 9 Enter value for age:25.5

old 1: insert into sailors values((&sid,'&sname',&rating,&age) new 1: insert into sailors values(22,’mana’,9,25.5)

insert into sailors values(22,’mana’,9,25.5)) *

ERROR at line 1:

ORA-20002: error:: cannot have duplicate sid ORA-06512: at "SCOTT.TRIG1", line 9

ORA-04088: error during execution of trigger 'SCOTT.TRIG1' SQL> /

(33)

Enter value for age:25.5

old 1: insert into sailors values((&sid,'&sname',&rating,&age) new 1: insert into sailors values(30,’mana’,9,25.5)

I row created

SQL>select * from sailors;

SID SNAME RATING AGE

22 Dustin 7 45.0

29 Brutus 1 33.0

31 Lubber 8 55.5

32 Andy 8 25.5

58 Rusty 10 35.0

64 Horatio 7 35.0

71 Zobra 10 16.0

74 Horatio 9 35.0

85 Art 3 25.5

95 Bob 3 63.5

30 mana 9 25.5

Types of Triggers:

There are four types of triggers used in SQL server: 1. DDL Triggers (Data Definition Language Triggers) 2. DML Triggers (Data Manipulation Language Triggers) 3. CLR Triggers

4. Logon Triggers DDL Triggers:

Users can create triggers on DDL statements like Alter, Create, Drop, etc., and stored procedures based on a system that perform DDL operations.

Example: Suppos, a user executes the Create Login statement or sp_addlogin stored procedure for user login, then both these procedures can fire a DDL trigger that user can create on the create_login event.

(34)

DDL triggers are helpful to manage administrator tasks like; regulating auditing database operations used to control actions on the SQL Server.

DML Triggers

In SQL Server we can create triggers on DML statements (like; INSERT, UPDATE, and DELETE) and stored procedures that perform DML-like operations. DML Triggers are of two types:

1. After Trigger: When execution of the action performed on SQL Server is about to finish, then this trigger is fired. For example: When a user insert record in a table, then the trigger related to this insert event will fire only after the ‘all constraints pass in the row’, like; primary key, rules, etc. If it fails then SQL Server won’t fire this trigger.

2. Instead of Trigger:

This trigger starts before the execution of the action performed by SQL Server. It used before the action is performed opposite to the after trigger. The user can apply Instead of clause like; update, deleted, insert triggers for a table. Actually, it doesn’t include insert and all to the table.

For example:When a user insert record in a table, then the trigger related to this insert event will fire only before the ‘all constraints passed’ in the row like

primary key, rules, etc. If it fails then SQL server will fire this trigger.

3. CLR Triggers

These are the special types of triggers that are based on Common Languages Runtime in .net framework. CLR is integrated with the SQL Server 2008 and allows triggers to be programmed in .net languages like; VB, C# etc.

User can write code for both DDL and DML triggers using CLR languages. For more on CLR triggers

Logon Triggers

These triggers are used when a LOGON event of the SQL Server occurs. This event gets raised when a user session is being established with the SQL Server after the authentication phase has finished. All the messages defined in the trigger like; error messages will be redirected to the error log of the SQL Server. If

authentication fails, then the logon triggers aren’t being fired. To audit server controls and server sessions like login activity, limit the no. of sessions etc., these triggers has used.

(35)

Create trigger trigger_name On all server

[with encryption] {For|after} logon As

Sql_statement [1..n]

Syntax for trigger

Create trigger trigger_name On {table|view}

[with encryption|Execute As] -- this is optional, if specified, then text in the trigger will be encrypted.

{ for|after|instead of } { [create|alter|drop|delete|update|insert] }

[not for replication] – It indicates that trigger shouldn’t be executed when a table is modified by the replication process.

Figure

Table created SQL&gt; begin

Table created

SQL&gt; begin p.29

References

Related subjects : PL/1 Pl Sql