DATABASE TRIGGERS
Triggers are
similar to procedures or functions in that they are named PL/SQL blocks with
declarative, executable, and exception handling sections. A trigger is executed
implicitly whenever the triggering event happens. The act of executing a
trigger is known as firing the trigger.
RESTRICTIONS ON
TRIGGERES
·
Like packages,
triggers must be stored as stand-alone objects in the database and cannot be
local to a block or package.
·
A trigger does
not accept arguments.
USE OF
TRIGGERS
·
Maintaining
complex integrity constraints not possible through declarative constraints
enable at table creation.
·
Auditing
information in a table by recording the changes made and who made them.
·
Automatically
signaling other programs that action needs to take place when chages are made
to a table.
·
Perform
validation on changes being made to tables.
·
Automate
maintenance of the database.
TYPES OF
TRIGGERS
·
DML Triggers
·
Instead of
Triggers
·
DDL Triggers
·
System Triggers
·
Suspend Triggers
CATEGORIES
Timing
-- Before or After
Level
-- Row or Statement
Row level
trigger fires once for each row affected by the triggering statement. Row level
trigger is identified by the FOR EACH ROW clause.
Statement level
triggers fires once either before or after the statement.
DML TRIGGER
SYNTAX
Create or replace trigger
trigger_name
Before | after on insert or update or delete
[For each row]
Begin
--null
End trigger_name
DML TRIGGERS
A DML trigger is fired on an INSERT, UPDATE, or DELETE
operation on a database table. It can be fired either before or after the
statement executes, and can be fired once per affected row, or once per
statement.
The combination of these factors determines the types of
the triggers. These are a total of 12 possible types (3 statements * 2 timing *
2 levels).
ORDER OF DML
TRIGGER FIRING
·
Before
statement level
·
Before row
level
·
After row level
·
After statement
level
Ex:
Suppose we have
a following table.
select * from
student;
NO NAME MARKS
----- ------- ----------
1
a
100
2
b
200
3
c
300
4
d
400
Also we have triggering_firing_order table with
firing_order as the field.
CREATE OR REPLACE TRIGGER
TRIGGER1
BEFORE INSERT
ON student
BEGIN
INSERT INTO
trigger_firing_order
VALUES ('Before Statement Level');
END
TRIGGER1;
CREATE OR REPLACE TRIGGER
TRIGGER2
BEFORE INSERT
ON student
FOR EACH ROW
BEGIN
INSERT INTO
trigger_firing_order
VALUES ('Before Row Level');
END TRIGGER2;
CREATE OR REPLACE TRIGGER
TRIGGER3
AFTER INSERT
ON student
BEGIN
INSERT INTO
trigger_firing_order
VALUES ('After Statement Level');
END TRIGGER3;
CREATE OR REPLACE TRIGGER
TRIGGER4
AFTER INSERT
ON student
FOR EACH ROW
BEGIN
INSERT INTO
trigger_firing_order
VALUES ('After Row Level');
END TRIGGER4;
Output:
SELECT * FROM
trigger_firing_order;
no rows selected
INSERT INTO student
VALUES (5, 'e', 500);
1 row created.
SELECT * FROM
trigger_firing_order;
FIRING_ORDER
--------------------------------------------------
Before
Statement Level
Before Row
Level
After Row Level
After Statement
Level
SQL select *
from student;
NO
NAME MARKS
---- -------- ----------
1
a
100
2
b
200
3
c
300
4
d
400
5
e
500
CORRELATION IDENTIFIERS
IN ROW-LEVEL TRIGGERS
Inside the
trigger, you can access the data in the row that is currently being processed.
This is accomplished through two correlation identifiers - :old and :new.
A correlation
identifier is a special kind of PL/SQL bind variable. The colon in front of
each indicates that they are bind variables, in the sense of host variables
used in embedded PL/SQL, and indicates that they are not regular PL/SQL
variables. The PL/SQL compiler will treat them as records of type
Triggering_table%ROWTYPE.
Although
syntactically they are treated as records, in reality they are not. :old and
:new are also known as pseudorecords, for this reason.
Suppose we have a table called marks with fields no, old_marks, new_marks.
CREATE OR REPLACE TRIGGER OLD_NEW
BEFORE INSERT OR UPDATE OR DELETE
ON student
FOR EACH ROW
BEGIN
INSERT INTO marks
VALUES (:old.no, :old.marks, :new.marks);
END OLD_NEW;Output:
SQL select *
from student;
NO NAME MARKS
----- ------- ----------
1
a
100
2
b
200
3
c
300
4
d
400
5
e
500
SQL select *
from marks;
no rows
selected
SQL insert into
student values(6,'f',600);
1 row created.
SQL select *
from student;
NO NAME MARKS
---- -------- ----------
1
a
100
2
b
200
3
c
300
4
d
400
5
e
500
6
f
600
SQL select *
from marks;
NO OLD_MARKS NEW_MARKS
---- --------------- ---------------
600
SQL update
student set marks=555 where no=5;
1 row updated.
SQL select *
from student;
NO NAME MARKS
----- ------- ----------
1 a
100
2
b
200
3
c
300
4
d
400
5
e
555
6
f
600
SQL select *
from marks;
NO
OLD_MARKS NEW_MARKS
------
---------------- ---------------
600
5
500
555
SQL delete
student where no = 2;
1 row deleted.
SQL select *
from student;
NO NAME MARKS
---- -------- ----------
1
a
100
3
c
300
4
d
400
5
e
555
6
f
600
SQL select *
from marks;
NO OLD_MARKS NEW_MARKS
----- -------------- ----------------
600
5
500
555
2 200
REFERENCING
CLAUSE
If desired, you
can use the REFERENCING clause to specify a different name for :old ane :new.
This clause is found after the triggering event, before the WHEN clause.
Syntax:
REFERENCING [old as old_name] [new as new_name]
Ex:
CREATE OR REPLACE TRIGGER
REFERENCE_TRIGGER
BEFORE INSERT OR UPDATE OR DELETE
ON student
REFERENCING OLD AS
old_student NEW AS new_student
FOR EACH ROW
BEGIN
INSERT INTO marks
VALUES (:old_student.no, :old_student.marks, :new_student.marks);
END
REFERENCE_TRIGGER;
WHEN CLAUSE
WHEN clause is
valid for row-level triggers only. If present, the trigger body will be
executed only for those rows that meet the condition specified by the WHEN
clause.
Syntax:
WHEN trigger_condition;
Where trigger_condition
is a Boolean expression. It will be evaluated for each row. The :new and
:old records can be referenced inside trigger_condition as well,
but like REFERENCING, the colon is not used there. The colon is only valid in
the trigger body.
Ex:
CREATE OR REPLACE TRIGGER
WHEN_TRIGGER
BEFORE INSERT OR UPDATE OR DELETE
ON student
REFERENCING OLD AS
old_student NEW AS new_student
WHEN (:new_student.marks > 500)
BEGIN
INSERT INTO marks
VALUES (:old_student.no, :old_student.marks, :new_student.marks);
END WHEN_TRIGGER;
TRIGGER
PREDICATES
There are three
Boolean functions that you can use to determine what the operation is.
The predicates
are
INSERTING
UPDATING
DELETING
Ex:
CREATE OR REPLACE TRIGGER
PREDICATE_TRIGGER
BEFORE INSERT OR UPDATE OR DELETE
ON student
BEGIN
IF INSERTING
THEN
INSERT INTO
predicates
VALUES ('I');
ELSIF UPDATING
THEN
INSERT INTO
predicates
VALUES ('U');
ELSIF DELETING
THEN
INSERT INTO
predicates
VALUES ('D');
END IF;
END PREDICATE_TRIGGER;Output:
SQL delete
student where no=1;
1 row deleted.
SQL select *
from predicates;
MSG
---------------
D
SQL insert into
student values(7,'g',700);
1 row created.
SQL select *
from predicates;
MSG
---------------
D
I
SQL update
student set marks = 777 where no=7;
1 row updated.
SQL select *
from predicates;
MSG
---------------
D
I
U
INSTEAD-OF
TRIGGERS
Instead-of
triggers fire instead of a DML operation. Also, instead-of triggers can be
defined only on views. Instead-of triggers are used in two cases:
·
To allow a view
that would otherwise not be modifiable to be modified.
·
To modify the
columns of a nested table column in a view.
SYSTEM TRIGGERS
System triggers
will fire whenever database-wide event occurs. The following are the database
event triggers. To create system trigger you need ADMINISTER DATABASE TRIGGER
privilege.
STARTUP
SHUTDOWN
LOGON
LOGOFF
SERVERERROR
Syntax:
Create or
replace trigger trigger_name
{Before |
after} {Database event} on {database | schema}
[When (…)]
[Declare]
-- declaration section
Begin
-- trigger body
[Exception]
-- exception section
End
Ex:
SQL create
table user_logs(u_name varchar(10),log_time timestamp);
CREATE OR
REPLACE TRIGGER AFTER_LOGON
after logon on database
BEGIN
insert into user_logs values(user,current_timestamp);
END
AFTER_LOGON;
Output:
SQL select *
from user_logs;
no rows selected
SQL conn
saketh/saketh
SQL select *
from user_logs;
U_NAME
LOG_TIME
----------
------------------------------------------------
SAKETH
22-JUL-07 12.07.13.140000 AM
conn
system/oracle
SQL select *
from user_logs;
U_NAME
LOG_TIME
----------
------------------------------------------------
SAKETH
22-JUL-07 12.07.13.140000 AM
SYSTEM
22-JUL-07 12.07.34.218000 AM
conn
scott/tiger
SQL select *
from user_logs;
U_NAME
LOG_TIME
----------
-----------------------------------------------
SAKETH
22-JUL-07 12.07.13.140000 AM
SYSTEM
22-JUL-07 12.07.34.218000 AM
SCOTT
22-JUL-07 12.08.43.093000 AM
SERVERERROR
The SERVERERROR
event can be used to track errors that occur in the database. The error code is
available inside the trigger through the SERVER_ERROR attribute function.
Ex:SQL create
table my_errors(error_msg varchar(200));
CREATE OR REPLACE TRIGGER
SERVER_ERROR_TRIGGER
AFTER SERVERERROR
ON DATABASE
BEGIN
INSERT INTO
my_errors
VALUES (DBMS_UTILITY.format_error_stack);
END SERVER_ERROR_TRIGGER;
Output:
SQL create
table ss (no));
create table ss
(no))
ERROR at line
1:
ORA-00922:
missing or invalid option
SQL select *
from my_errors;
ERROR_MSG
-------------------------------------------------------------
ORA-00922:
missing or invalid option
SQL insert into
student values(1,2,3);
insert into
student values(1,2,3)
ERROR at line
1:
ORA-00942:
table or view does not exist
SQL select *
from my_errors;
ERROR_MSG
-------------------------------------------------------------
ORA-00922:
missing or invalid option
ORA-00942:
table or view does not exist
SERVER_ERROR
ATTRIBUTE FUNCTION
It takes a
single number type of argument and returns the error at the position on the
error stack indicated by the argument. The position 1 is the top of the stack.
Ex:
CREATE OR
REPLACE TRIGGER SERVER_ERROR_TRIGGER
after servererror on database
BEGIN
insert into my_errors values(server_error(1));
END SERVER_ERROR_TRIGGER;
SUSPEND
TRIGGERS
This will fire
whenever a statement is suspended. This might occur as the result of a space
issue such as exceeding an allocated table pace quota. This functionality can
be used to address the problem and allow the operating to continue.
Syntax:
Create or
replace trigger trigger_name
after
suspend on {database | schema}
[When (…)]
[Declare]
-- declaration section
Begin
-- trigger body
[Exception]
-- exception section
End <trigger_name>;
Ex:
SQL CREATE TABLESPACE my_space DATAFILE 'f:\my_file.dbf' SIZE 2M;
SQL CREATE TABLE student (sno NUMBER (2), sname VARCHAR (10))
TABLESPACE my_space;
TABLESPACE my_space;
CREATE OR
REPLACE TRIGGER SUSPEND_TRIGGER
after suspend on database
BEGIN
dbms_output.put_line(‘ No room to insert in your tablespace');
END
SUSPEND_TRIGGER;
Output:
Insert more rows in student table then , you will get
No room to insert in your tablespace
