PL/SQL: TABLE BASED DATA TYPE.

Record datatype variables are composit data structure made up of different components called fields which have the same name and datatype as that of the columns of the table which is using in creating the record.
Example:

set serveroutput on;
declare
v_emp employees%rowtype;
begin
select * into v_emp from employees where employee_id=100;
dbms_output.put_line(v_emp.first_name||' '||v_emp.salary);
end;

/

REsult:
Steven 24000

In the above example v_emp is of employees row type..

PL/SQL: Backup Trigger.

To make synchronize backup of table, We create a table to store the backup.
Create Table:
create table heros_backup as select * from heros where 1=2;

This will create the table same as heros but does not copy the data of hero table.
Now
Create Backup Trigger:
create or replace trigger heros_bu
before insert or delete or update on heros
for each row
enable
begin
if inserting then
  insert into heros_backup (hero_name) values(:NEW.hero_name);
elsif deleting then
  delete from heros_backup where hero_name=:old.hero_name;
elsif updating then
  update heros_backup set hero_name = :NEW.hero_name where hero_name=:old.hero_name;
end if;

end;

Result:

Trigger HEROS_BU compiled

Now check by inserting data into heros.

insert into heros values('Muhammad Talha Zubair');

To Check:

select * from heros_backup;

Result:

Muhammad Talha Zubair

PL/SQL Audit Trigger

In previous topic we discuss the audit of table when inserting, updatind, or deleting any row. If we want to save the audition in another table then
First of all we have to create a table.

Create Table:
create table audit_heros
(
new_name varchar2(30),
old_name varchar2(30),
user_name varchar2(30),
operation_date varchar2(30),
operation varchar2(30)
);

/
Result:
Table audit_heros created.

Now we create an audit trigger on audit heros, that keeps the record of changing the heros table.

Audit Trigger:
create or replace trigger au_heros
before insert or delete or update on heros
for each row
enable
declare
v_user varchar2(30);
v_date varchar2(30);
begin
select user, to_char(sysdate,'DD/MM/YYYY HH24:MI:SS') into v_user,v_date from dual;
if inserting then
  insert into audit_heros values(:NEW.hero_name,'NULL',v_user,v_date,'INSERT');
elsif deleting then
  insert into audit_heros values('NULL',:OLD.hero_name,v_user,v_date,'DELETE');
elsif updating then
  insert into audit_heros values(:NEW.hero_name,:OLD.hero_name,v_user,v_date,'update');
end if;
end;

/
Result:
Trigger au_heros  compiled.

Now by inserting or deleting or updating the row in heros table. we can check the working of au_heros trigger.
When Deleting:
delete from heros where hero_name='Salman';

Result:
3 rows deleted.
Now chek audit_heros table

select * from audit_heros;
Result:

NULL Salman HR 13/05/2017 16:34:45 DELETE
NULL Salman HR 13/05/2017 16:34:45 DELETE
NULL Salman HR 13/05/2017 16:34:45 DELETE

When updating:
update heros set hero_name='Aybue khan' where hero_name='Ayube';

And again check the audit_heros table
select * from audit_heros;
Result:

NULL          Salman HR 13/05/2017 16:34:45 DELETE
NULL          Salman HR 13/05/2017 16:34:45 DELETE
NULL          Salman HR 13/05/2017 16:34:45 DELETE

Aybue khan Ayube HR 13/05/2017 16:40:56 update

Note:
NEW keyword allows you access a row that is being proceed . It means that it allow acess to a row that is being inserted or updated in the table

OLD keyword allow access to a table that is already inserted or updated in the table.

PL/SQL DML Trigger With Before Insert, Update and Delete DML Examples

To test this trigger, first of all we have to create a table.

Table:

create table heros

(hero_name varchar2(30));

Result:

Table HEROS created.

Now we create a dml trigger,

CREATE OR REPLACE TRIGGER bi_heros

BEFORE INSERT ON heros

FOR EACH ROW 

ENABLE

DECLARE

v_user varchar2(30);

begin

select user into v_user from dual;

dbms_output.put_line('User '||v_user||' Just inserted a line');

end;

/

Result:

Trigger BI_HEROS compiled

To check that is this trigger working or not, We Insert a row into the table and find out the result.

insert into heros values('Talha Zubair');

Result:

1 row inserted.

User HR Just inserted a line

We add another trigger on heros table.

CREATE OR REPLACE TRIGGER bu_heros

BEFORE update ON heros

FOR EACH ROW 

ENABLE

DECLARE

v_user varchar2(30);

begin

select user into v_user from dual;

dbms_output.put_line('User '||v_user||' Just updated a line');

end;

/

Update data

update heros set hero_name='Talha Zubair Khan' where hero_name='Talha Zubair';

Result:

1 row updated.

User HR Just updated a line

Create Insert, update, delete Trigger at a time.

code:

create or replace trigger tr_heros

before insert or delete or update on heros

for each row

enable

declare

v_user varchar2(30);

begin

Select user into v_user from dual;

if inserting then

dbms_output.put_line('User '||v_user||' Just Inserted A line');

elsif deleting then

dbms_output.put_line('User '||v_user||' Just deleted A line');

elsif updating then

dbms_output.put_line('User '||v_user||' Just Updated A line');

end if;

end;

/

Result:

Trigger TR_HEROS compiled

Now test the trigger.

inserting:

insert into heros values('Salman');

Result:

1 row inserted.

User HR Just Inserted A line

Updating:

delete from heros where hero_name='Salman';

Result:

1 rows deleted.

User HR Just deleted A line

PL/SQL: TRIGGERS INTRO

Named PL/SQL blocks which are stored in database are called triggers.
Or
Specialized stored programs which executes implicitly when trigger event occurs.
Events:
Events may be anything,
such as
1. DML statement, which includes insert or delete statement.
2. DDL statement, which includes create or alter statements.
3. System event, which includes shutdown or restart database.
4. User event, Which include logoff or log on of user on the database.

TYPES of TRIGGERS:
There are 5 types of trigges.

1. DML TRIGGERS.
As name show, this type or trigger fire with the dml statements, before or after the insert, delete or update statement.

2. DDL TRIGGERS:
These are the triggers which are created with the ddl statements, such as create or alter statement, These triggers fire when any changes occure.

3. SYSTEM EVENT TRIGGERS:
This type of triggers occure when database or system event occur, such as logon or log off of the database.

4. INSTEAD-OF TRIGGERS:
These tirggers enables you to stop or redirected the performance of dml triggers.

5. COMPOUND TRIGGERS:
These are multi tasking triggers that act as low level triggers when the data inserted, deleted or updated from the database table. Compound triggers used to audit, check, replace or save even before the change.

PL/SQL:WHILE LOOP

WHILE LOOP is also used for the itteration of  block of statement.
The best usable of while is when the number of itteration are unknown.

Syntax:
begin
while condition loop
statement1;
statement2;
statement3;
.
.
statementn;
end loop;

In the above example, while and loop are the keywords. and condition is the critarea , which decides the number of itteration.

Example:
declare
result number:=0;
counter number:=1;
table_num number:= &Enter_number_For_table;
begin
while counter <=10 loop
result:=counter*table_num;
dbms_output.put_line(table_num||' * '||counter||' = '||result);
counter:=counter+1;
end loop;
dbms_output.put_line('M Talha Zubair, Out from loop');

end;

Result:
19 * 1 = 19
19 * 2 = 38
19 * 3 = 57
19 * 4 = 76
19 * 5 = 95
19 * 6 = 114
19 * 7 = 133
19 * 8 = 152
19 * 9 = 171
19 * 10 = 190
M Talha Zubair, Out from loop


While Loop with Boolean Variable as test Variable:
Example:
declare
v_test boolean:=true;
counter number:=1;
begin
while v_test loop
dbms_output.put_line(counter);
counter:=counter+1;
if counter=10 then
exit;
end if;
end loop;
dbms_output.put_line('M Talha Zubair, Out from loop');
end;

Result:
1
2
3
4
5
6
7
8
9
M Talha Zubair, Out from loop

PL/SQL: FOR LOOP

FOR LOOP  is used for the fixed number of execution of statement or set of statement.

Syntax:
For Counter_variable in lower_limit . . upper limit Loop
statement 1;
statement 2;
statement 3;
.
.
statement n;
end loop;
end;

Counter variable us is used to continue the loop or execution of statements. IN is the keyword. lower LIMIT and UPPER LIMIT is the critarea for which loops executes the statement(s).

Example:
declare
counter number:=0;
begin
for counter in 1 .. 10 loop
dbms_output.put_line(counter||' I Love Pakistan');
end loop;

end;

Result:
1 I Love Pakistan
2 I Love Pakistan
3 I Love Pakistan
4 I Love Pakistan
5 I Love Pakistan
6 I Love Pakistan
7 I Love Pakistan
8 I Love Pakistan
9 I Love Pakistan
10 I Love Pakistan


To print the table of any number input by the user, The query will be as follow

Example:
declare
result number:=0;
table_num number:= &Enter_number_For_table;
begin
for counter in 1 .. 10 loop
result:=counter*table_num;
dbms_output.put_line(table_num||' * '||counter||' = '||result);
end loop;

end;

Result:
Enter_number_For_table = 19
19 * 1 = 19
19 * 2 = 38
19 * 3 = 57
19 * 4 = 76
19 * 5 = 95
19 * 6 = 114
19 * 7 = 133
19 * 8 = 152
19 * 9 = 171
19 * 10 = 190