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, Procedure, Function, 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,
• process query results in a tuple-oriented way,
• optimize combined SQL statements,

1
• 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:

<variable name> [constant] <data type> [not null] [:= <expression>];

2
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;

Instead of specifying a data type, one can also refer to the data type of a table column (so-
called anchored declaration). For example, EMP.Empno%TYPE refers to the data type of the
column Empno in the relation EMP. Instead of a single variable, a record can be declared
that can store a complete tuple from a given table (or query result). For example, the data
type DEPT%ROWTYPE specifies a record suitable to store all attribute values of a complete
row from the table DEPT. Such records are typically used in combination with a cursor. A
field in a record can be accessed using <record name>.<column name>, for example,
DEPT.Deptno.

3
 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

NUMBER(prec, scale)
1 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

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

DATE
3
DATE datatype stores valid date time format with fixed length.

INTEGER
4
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;
/
When the above code is compiled and executed, it produces the following result −

4
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

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

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

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

ROWID
4
Physical row identifier, the address of a row in an ordinary table

5
 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

AND conjunction

6
 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;
/

7
 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

old 2: a number(10) := &a;
new 2: a number(10) := 44;

8
 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
dbms_output.put_line('sailor '||a||' has grade C');

9
 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
dbms_output.put_line('today is wednesday');

10
 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
EXITL;

11
 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;
…….
STATEMENT N;
END LOOP;

12
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;
……
STATEMNET N;
End loop;

13
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;
The reversed word FOR marks the beginning of a FOR loop construct. The variable
loop_counter is an implicitly defined index variable. There is no need to define the loop
counter in the declaration section. The values of the lower_limit and upper_limit are
evaluated once for the iteration of the loop.

14
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

PL/SQL procedure successfully completed.

15
 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

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

SQL> create or replace procedure sum(a in number,b in number)

is
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;
/

17
 Procedure created.

SQL> declare
a number := &a;
b number := &b;
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);

18
 end;
/
Enter value for a: 64
old 2: a number := &a;
new 2: a number := 64;
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;

19
 end;
/

Function created.

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);

20
 dbms_output.put_line('sailor with sid '||a||'has age '||c);
end;
/
Enter value for a: 58
old 2: a number:= &a;
new 2: a number:= 58;
sailor with sid 58has age 35

PL/SQL procedure successfully completed.

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 ;

21
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 .
trigger_condiditon: is a Boolean condition
trigger_body : contains the code for the trigger

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');

22
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;

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

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

23
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> /
Enter value for deptnp: 30
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(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

24
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.

We can also make After Trigger on DDL statements and cannot use Instead of the
clause.

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

25
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.

Syntax to use The Logon Trigger

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.
As– After this action and condition are performed.
SQL_statement upto n

26