Implement a PL/SQL block using triggers for transaction operations of a typical application
EXERCISE :12
AIM : Implement
a PL/SQL block using triggers for transaction operations of a
typical application
Description :
Trigger:
Trigger: A
trigger is a stored procedure in database which automatically invokes whenever
a special event in the database occurs.
Example: a trigger can be invoked when a row
is inserted into a specified table or when certain table columns are being
updated.
Syntax:
create
trigger [trigger_name]
[before |
after]
{insert |
update | delete}
on
[table_name]
[for each
row]
[trigger_body]
create
trigger [trigger_name]: Creates or replaces an existing trigger with the
trigger_name.
[before |
after]: This specifies when the trigger will be fired.
{insert |
update | delete}: This specifies the DML operation.
on
[table_name]: This specifies the name of the table associated with the trigger.
[for each
row]: This specifies a row-level trigger, i.e., the trigger will be executed
for each row being affected.
[trigger_body]:
This provides the operation to be performed as trigger is fired Each trigger is
attached to a single, specified table in the database.
SQL>
create table customers(id int,name varchar2(10),age int,address
varchar2(10),salary int);
Table
created.
SQL>
select * from customers;
ID NAME AGE ADDRESS SALARY
----------
---------- ---------- ---------- ----------
1 ramesh 23
allahabad 20000
2 suresh 22 kanpur 22000
3 mahesh 24
ghaziabad 24000
4 chandan 25
noida 26000
5 alex 21 paris 28000
6 sunita 20 delhi 30000
6 rows selected.
SQL> set
serveroutput on;
Create
trigger:
The
following trigger will display the salary difference between the old values and
new values:
SQL>
CREATE OR REPLACE TRIGGER display_salary_changes
BEFORE
DELETE OR INSERT OR UPDATE
ON customers
FOR
EACH ROW
WHEN
(NEW.ID > 0)
DECLARE
sal_diff
number;
BEGIN
sal_diff
:= :NEW.salary - :OLD.salary;
dbms_output.put_line('Old
salary: ' || :OLD.salary);
dbms_output.put_line('New
salary: ' || :NEW.salary);
dbms_output.put_line('Salary
difference: ' || sal_diff);
END;
/
Trigger
created.
Above
trigger has been written in such a way that it will fire before any DELETE or
INSERT or UPDATE operation on the table, but you can write your trigger on a
single or multiple operations, for example BEFORE DELETE, which will fire
whenever a record will be deleted using DELETE operation on the table.
SQL>
DECLARE
total_rows
number(2);
BEGIN
UPDATE customers
SET
salary = salary + 5000;
IF
sql%notfound THEN
dbms_output.put_line('no
customers updated');
ELSIF sql%found THEN
total_rows
:= sql%rowcount;
dbms_output.put_line(
total_rows || ' customers updated ');
END
IF;
END;
/
Old salary:
20000
New salary:
25000
Salary
difference: 5000
Old salary:
22000
New salary:
27000
Salary
difference: 5000
Old salary:
24000
New salary:
29000
Salary
difference: 5000
Old salary:
26000
New salary:
31000
Salary
difference: 5000
Old salary:
28000
New salary:
33000
Salary
difference: 5000
Old salary:
30000
New salary:
35000
Salary
difference: 5000
6 customers
updated
PL/SQL
procedure successfully completed.
Let us
perform some DML operations on the CUSTOMERS table. Here is one INSERT
statement, which will create a new record in the table:
INSERT INTO
customers VALUES (7,'Karthik',22,'GEC',47500);
When a
record is created in CUSTOMERS table, above create trigger
display_salary_changes will be fired and it will display the following result:
Output:-
SQL>
INSERT INTO customers VALUES (7,'Karthik',22,'GEC',47500);
Old salary:
New salary:
47500
Salary
difference:
1 row
created.
Descrpition:-
SQL
(Implicit) Cursor Attribute
1. A SQL
(implicit) cursor is opened by the database to process each SQL statement that
is not associated with an explicit cursor.
2. Every SQL
(implicit) cursor has six attributes, each of which returns useful information
about the execution of a data manipulation statement.
Keyword and
Parameter Descriptions
%BULK_ROWCOUNT
A composite
attribute designed for use with the FORALL statement. This attribute acts like
an index-by table. Its ith element stores the number of rows processed by the
ith execution of an UPDATE or DELETE statement. If the ith execution affects no
rows, %BULK_ROWCOUNT(i) returns zero.
%BULK_EXCEPTIONS
An
associative array that stores information about any exceptions encountered by a
FORALL statement that uses the SAVE EXCEPTIONS clause.
You must
loop through its elements to determine where the exceptions occurred and what
they were. For each index value i between 1 and SQL%BULK_EXCEPTIONS.COUNT,
SQL%BULK_EXCEPTIONS(i).ERROR_INDEX specifies which iteration of the FORALL loop
caused an exception. SQL%BULK_EXCEPTIONS(i).ERROR_CODE specifies the Oracle
Database error code that corresponds to the exception.
%FOUND
Returns TRUE
if an INSERT, UPDATE, or DELETE statement affected one or more rows or a SELECT
INTO statement returned one or more rows. Otherwise, it returns FALSE.
%ISOPEN
Always
returns FALSE, because the database closes the SQL cursor automatically after
executing its associated SQL statement.
%NOTFOUND
The logical
opposite of %FOUND. It returns TRUE if an INSERT, UPDATE, or DELETE statement
affected no rows, or a SELECT INTO statement returned no rows. Otherwise, it
returns FALSE.
%ROWCOUNT
Returns the
number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned
by a SELECT INTO statement.
2. Convert
employee name into uppercase whenever an employee record is inserted or
updated. Trigger to fire before the insert or update.
SQL>
create table Employeee(ID VARCHAR2(4 BYTE) NOT NULL,
First_Name
VARCHAR2(10 BYTE),
Last_Name
VARCHAR2(10 BYTE),
Start_Date
DATE,
End_Date DATE,
Salary
NUMBER(8,2),
City
VARCHAR2(10 BYTE),
Description VARCHAR2(15 BYTE) )
/
Table
created.
SQL>
CREATE OR REPLACE TRIGGER employeee_insert_update
BEFORE
INSERT OR UPDATE
ON
employeee
FOR
EACH ROW
DECLARE
dup_flag
INTEGER;
BEGIN
--Force all employee names to uppercase.
:NEW.first_name
:= UPPER(:NEW.first_name);
END;
/
Trigger
created.
SQL> insert
into Employeee(ID,First_Name, Last_Name, Start_Date, End_Date, Salary, City,
Description)
values ('01','karthik', 'jayavarapu',
to_date('20200101','YYYYMMDD'), to_date('20200110','YYYYMMDD'), 27500, 'gec',
'Professor')
/
1 row
created.
SQL>
insert into Employeee(ID,First_Name, Last_Name, Start_Date, End_Date, Salary,
City, Description)
values ('02','siva', 'prasad',
to_date('20200101','YYYYMMDD'), to_date('20200110','YYYYMMDD'), 97500, 'gec',
'Professor')
/
1 row
created.
SQL> set
pagesize 100;
SQL> set
linesize 100;
SQL>
select * from employeee;
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
----
---------- ---------- --------- --------- ---------- ---------- ---------------
01 KARTHIK
jayavarapu 01-JAN-20 10-JAN-20
27500 gec Professor
02 SIVA
prasad 01-JAN-20
10-JAN-20 97500 gec Professor
EXAMPLE-2
SQL> create table empa(id number(3),name
varchar2(10),income number(4),expence number(3),savings number(3));
Table
created.
SQL>
insert into empa values(2,'kumar',2500,150,650);
1 row
created.
SQL> insert into empa
values(3,'venky',5000,900,950);
1 row
created.
SQL> insert into empa
values(4,'anish',9999,999,999);
1 row
created.
SQL> select * from empa;
ID NAME INCOME
EXPENCE SAVINGS
----------
---------- ---------- ---------- ----------
2 kumar 2500 150 650
3 venky 5000 900 950
4 anish 9999 999 999
TYPE 1-
TRIGGER AFTER UPDATE
SQL>
CREATE OR REPLACE TRIGGER after_update
AFTER
UPDATE OR INSERT OR DELETE ON empa
FOR
EACH ROW
BEGIN
IF
UPDATING THEN
DBMS_OUTPUT.PUT_LINE('TABLE
IS UPDATED');
ELSIF
INSERTING THEN
DBMS_OUTPUT.PUT_LINE('TABLE
IS INSERTED');
ELSIF
DELETING THEN
DBMS_OUTPUT.PUT_LINE('TABLE
IS DELETED');
END
IF;
END;
/
Trigger
created.
SQL> SET
SERVEROUTPUT ON;
SQL>
update empa set income =9000 where name='kumar';
TABLE IS
UPDATED
1 row
updated.
SQL>
insert into empa values(40,'Chandru',700,250,80);
TABLE IS
INSERTED
1 row
created.
SQL>DELETE
FROM EMPA WHERE ID = 4;
TABLE IS
DELETED
Create a
Trigger to check the age valid or not Using Message Alert
SQL>
CREATE TABLE TRIG(NAME CHAR(10),AGE NUMBER(3));
Table
created.
SQL> DESC
TRIG;
Name
Null? Type
-----------------------------------------
-------- ----------------------------
NAME
CHAR(10)
AGE NUMBER(3)
SQL>
CREATE TRIGGER TRIGNEW
AFTER
INSERT OR UPDATE OF AGE ON TRIG
FOR
EACH ROW
BEGIN
IF(:NEW.AGE<0)
THEN
DBMS_OUTPUT.PUT_LINE('INVALID
AGE');
ELSE
DBMS_OUTPUT.PUT_LINE('VALID
AGE');
END
IF;
END;
/
Trigger
created.
SQL> insert into trig values('abc',15);
VALID AGE
1 row
created.
SQL> insert into trig values('xyz',-12);
INVALID AGE
1 row
created.
SQL>
SELECT * FROM TRIG;
NAME AGE
----------
----------
abc 15
xyz -12
Create a
Trigger to check the age valid and Raise appropriate error code and error
message.
SQL> create table data(name char(10),age
number(3));
Table
created.
SQL> desc data;
Name
Null? Type
-----------------------------------------
-------- ----------------------------
NAME
CHAR(10)
AGE
NUMBER(3)
SQL>
CREATE TRIGGER DATACHECK
AFTER
INSERT OR UPDATE OF AGE ON DATA
FOR
EACH ROW
BEGIN
IF(:NEW.AGE<0)
THEN
RAISE_APPLICATION_ERROR(-20000,'NO NEGATIVE
AGE ALLOWED');
END
IF;
END;
/
Trigger
created.
SQL>
INSERT INTO DATA VALUES('ABC',10);
1 row
created.
SQL> INSERT INTO DATA VALUES ('DEF',-15);
INSERT INTO DATA VALUES ('DEF',-15)
*
ERROR at
line 1:
ORA-20000:
NO NEGATIVE AGE ALLOWED
ORA-06512:
at "JK.DATACHECK", line 3
ORA-04088:
error during execution of trigger 'JK.DATACHECK'
SQL>
SELECT * FROM DATA;
NAME AGE
----------
----------
ABC 10
2.
Statementlevel Trigger:
Description:
- Statement
level triggers executes only once for each single transaction.
- Used for
enforcing all additional security on the transactions performed on the
table.
- “FOR EACH
ROW” clause is omitted in CREATE TRIGGER command
- If 1500 rows
are to be inserted into a table, the statement level trigger would execute
only once.
Sql>create table student(id number(10) primary
key,marks number(10));
Output:Table created.
Sql>create table count(des varchar(20));
Output:Table created.
Sql>create or replace trigger st_lev
After
insert or update or delete on student
begin
insert into count values(‘stmt fixed’);
end;
/
Output:Trigger created.
Sql>insert into student values(1,80);
Output:1 row inserted.
Sql>insert into student values(2,45);
1 row created
Sql>select * from count;
VIVA QUESTIONS:
1. What is Trigger?
2. Syntax for creating a trigger.
3.
How many types of triggers are there and what are they?
Comments
Post a Comment