Lab 9
Triggers- View-Sequence
Eng. Ibraheem Lubbad
The Islamic University of Gaza
Faculty of Engineering
Dept. of Computer Engineering
Database Lab (ECOM 4113)
Triggers:
A trigger is a PL/SQL block or a PL/SQL procedure associated with a table, view, schema,
or database. Executes implicitly whenever a particular event takes place. Oracle
Database automatically executes a trigger when specified conditions occur
Trigger Timing: When should the trigger fire?
BEFORE: Execute the trigger body before the triggering DML event on a table not
on views.
AFTER: Execute the trigger body after the triggering DML event on a table not on
views.
NSTEAD OF: Execute the trigger body instead of the triggering statement. This is
used for views that are not otherwise modifiable, trigger on the nested table
column of the view.
A trigger event: Determines which DML statement causes the trigger to execute.
Types are:
INSERT
UPDATE [OF column]
DELETE
A trigger body (Executable-statements): Is a PL/SQL block or a CALL to a procedure. It
determines what action is performed.
The syntax of trigger
Of CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF } -- TIMING
{INSERT [OR] | UPDATE [OR] | DELETE} -- EVENT
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
DECLARE
Declaration-statements
BEGIN
Executable-statements
END ;
Type of trigger:
Row trigger (row level)
Statement trigger (table level )
The trigger type determines whether the body executes for each row or only
once for the triggering statement.
A row trigger(row level):
An event is triggered for each row upated, inserted or deleted.
Is not executed if the triggering event does not affect any rows.
Is indicated by specifying the “FOR EACH ROW” clause.
Useful if the code in the trigger action depends on data provided by the
triggering statement or rows that are affected.
*If there are four instructor in department Computer Science, then the trigger fires four times when this statement is entered, because four rows are affected.
CREATE OR REPLACE TRIGGER SALARY_CHANGE
BEFORE UPDATE ON INSTRUCTOR
FOR EACH ROW
WHEN (OLD.SALARY <> NEW.SALARY)
BEGIN
DBMS_OUTPUT.PUT_LINE('OLD SALARY =' || :OLD.SALARY );
DBMS_OUTPUT.PUT_LINE('NEW SALARY =' || :NEW.SALARY );
DBMS_OUTPUT.PUT_LINE('DIFF SALARY =' || SAL_DIFF);
END;
UPDATE INSTRUCTOR
SET SALARY= SALARY+ SALARY*.1
WHERE ID= 10101;
UPDATE INSTRUCTOR SET
SALARY= SALARY+ SALARY*.1
WHERE dept_name= ‘Comp. Sci.’;
Example: write a trigger that is fire when someone is trying to modify an instructor’s
salary. The trigger will insert the values of the old salary and the new salary into an
AUDIT table named “AUDIT_INST”.
1. Create “AUDIT_INST” table.
2. Create trigger “TRACK_SAL_CHANGES”
Create table AUDIT_INST (
INST_ID VARCHAR2(5),
PREV_SAL NUMBER(8,2),
NEW_SAL NUMBER(8,2),
MODFIY_DATE DATE
);
CREATE OR REPLACE TRIGGER TRACK_SALARY_CHANGE
AFTER UPDATE
OF SALARY
ON INSTRUCTOR
REFERENCING OLD AS O NEW AS N
FOR EACH ROW
WHEN (O.SALARY <> N.SALARY)
BEGIN
INSERT INTO AUDIT_INST VALUES(:O.ID,:O.SALARY,:N.SALARY ,SYSDATE);
END;
UPDATE INSTRUCTOR SET SALARY= 100000 WHERE ID= 12121;
UPDATE INSTRUCTOR SET SALARY= 110000 WHERE ID= 12121;
UPDATE INSTRUCTOR SET SALARY= 120000 WHERE ID= 12121;
Example: Create a trigger can be used to keep the tot_cred attribute value, up-to-date
when the grade attribute is updated for a row in the takes table. The trigger is executed
only when the grade attribute is updated from a value that is either null or ’F’, to a
grade that indicates the course is successfully completed
A statement trigger (table level ):
The absence of the FOR EACH ROW option indicates that the trigger fires only once for
each applicable statement, but not separately for each row affected by the statement.
An event is triggered for each sql statement executed
Is the default type of trigger?
Fires once even if no rows are affected at all
Useful if the code in the trigger action does not depend on the data
provided by the triggering statement or the rows affected
In this type we cannot use NEW, OLD
For example, if a DELETE statement deletes several rows from a table, a statement-
level DELETE trigger is fired only once.
CREATE OR REPLACE TRIGGER CREDIT_EARNED
AFTER UPDATE OF GRADE ON TAKES
FOR EACH ROW
WHEN((NEW.GRADE<> 'F' AND NEW.GRADE IS NOT NULL ) AND
(OLD.GRADE= 'F' OR NEW.GRADE IS NULL ))
BEGIN
UPDATE STUDENT
SET TOT_CRED= TOT_CRED+(
SELECT CREDITS FROM COURSE
WHERE COURSE_ID= :NEW.COURSE_ID)
WHERE ID = :NEW.ID;
END;
For more than one type of DML operation can fire a trigger
For Example: [NSERT OR DELETE OR UPDATE ] ON Std_tab), the trigger body can use
the conditional predicates INSERTING, DELETING, and UPDATING to check which type of
statement fire the trigger.
Example: create a (LOGS_STUDENT) to keep track actions on STUDENT table. That
monitor all operation
1. Create “LOGS_STUDENT” table.
2. Create trigger “TRACK_STUDENT_TABLE”
CREATE TABLE LOGS_STUDENT (
USERNAME VARCHAR2(20),
OPERATION_TYPE VARCHAR2(20),
OPERATION_DATE DATE
);
CREATE OR REPLACE TRIGGER TRACK_STUDENT_TABLE
AFTER UPDATE OR DELETE OR INSERT
ON STUDENT
DECLARE
USERNAME VARCHAR2(20);
OPERATION_TYPE VARCHAR2(20);
BEGIN
SELECT USER INTO USERNAME FROM DUAL ;
IF INSERTING THEN
OPERATION_TYPE := 'INSERTING ';
ELSIF UPDATING THEN
OPERATION_TYPE := 'UPDATING ';
ELSE
OPERATION_TYPE:= 'DELETING ';
END IF;
INSERT INTO LOGS_STUDENT VALUES(USERNAME,OPERATION_TYPE,SYSDATE);
END;
Get current user to database
Return true if event is insert
Views:
A views are a stored query, which represent the data of one of more tables. Views can
be based on actual tables or another view.
Whatever DML operations you performed on a view they actually affect the base table
of the view. You can treat views same as any other table. You can Query, Insert, Update
and delete from views, just as any other table.
Syntax of Create View
CREATE [OR REPLACE] VIEW view_name AS
SELECT column1, column2.....
FROM table_name WHERE [condition];
[WITH CHECK OPTION][WITH READ ONLY]
Example: Create a view to find all Computer Sciences courses offered in the spring 2010
semester.
Using Views:
We can use the view name to refer to the virtual table that the view generates
Example: Use upper view to find all Computer Sciences courses the spring 2010
semester in the Watson building.
Caution: when you create a view that select an expression, then you have to name this expression with a column alias, in this case select values from the view using alias name:
Example:
Example
CREATE OR REPLACE VIEW CS_SPRING_2010 AS
SELECT COURSE.COURSE_ID, SEC_ID, BUILDING, ROOM_NUMBER, SEMESTER
FROM COURSE JOIN SECTION
ON COURSE.COURSE_ID = SECTION.COURSE_ID
AND COURSE.DEPT_NAME = 'Comp. Sci.'
AND SECTION.SEMESTER = 'Spring' AND SECTION.YEAR = 2010;
Example
SELECT * FROM CS_SPRING_2010
WHERE BUILDING='Watson';
Example
CREATE OR REPLACE VIEW DEPARTMENTS_TOTAL_SALARY AS
SELECT DEPT_NAME, SUM (SALARY) AS "Total Salary"
FROM INSTRUCTOR
group by dept_name;
Then you can select values from the view using alias name
Or we can specify explicitly names for attribute of a view as follows:
Update of a View:
An updatable view is one you can use to insert, update, or delete base table rows.
We can insert values to base table using view, if it has enough value to column on base
table that has not null constraint.
The view Updatable, if the following conditions are all satisfied by the query defining
the view:
The from clause has only one database table.
The select clause contains only attribute names of the table, and does not have
any expressions, aggregates function, or distinct operator.
The query does not have a group by or having clause
The query does not have A subquery in a SELECT list
The query does not have A subquery designated WITH READ ONLY
Example
SELECT DEPT_NAME, "Total Salary"
FROM DEPARTMENTS_TOTAL_SALARY;
Example
CREATE OR REPLACE VIEW DEPARTMENTS_TOTAL_SALARY
(DEPARTMENT,TOTAL_SALARY)AS
SELECT DEPT_NAME, SUM (SALARY) AS "Total Salary"
FROM INSTRUCTOR
group by dept_name;
If you want a join view to be updatable, then all of the following conditions must be
true:
The DML statement must affect only one table underlying the join
For an INSERT / UPDATE statement, the view must not be
created WITH CHECK OPTION,
For example:
We can create view from another view:
Example
CREATE OR REPLACE VIEW FACULTY AS
SELECT ID, NAME ,DEPT_NAME FROM INSTRUCTOR;
Example
view insert into faculty values(50501,'ibraheem','Comp. Sci.')
Use Example
function CREATE OR REPLACE VIEW CS_FACULTY AS
SELECT ID, NAME ,DEPT_NAME FROM FACULTY
WHERE DEPT_NAME='Comp. Sci.';
What about the following statement:
Will row inserted into base table, but logical it’s wrong to insert instructor in Elec. Eng.
Using Comp. Sci. view .
To solve this problem we can use < WITH CHECK OPTION> clause
The insertion is rejected by the database system if row inserted into the view does not
satisfy the view’s where clause
Use faculty view
insert into CS_faculty values(21212,'Edin','Elec. Eng.');
Example
create or replace view CS_faculty as
select id, name ,dept_name from faculty
where dept_name='Comp. Sci.'
with check option ;
WITH READ ONLY clause;
Specify WITH READ ONLY to indicate that the table or view cannot be updated.
Update join view:
We will get error if we try to update multiple table though join view
Example
create or replace view CS_faculty as
select id, name ,dept_name from faculty
where dept_name='Comp. Sci.'
with read only ;
Example
CREATE OR REPLACE VIEW INST_DEPT AS
SELECT ID, NAME ,DEPT_NAME , BUILDING
FROM INSTRUCTOR NATURAL JOIN DEPARTMENT
Example
view INSERT INTO INST_DEPT values('19871','ibraheem','Taylor' );
But still we can use “INST_DEPT” view to insert record in instructor table.
Since any not exist column in view will insert into base table as null value so, cannot
above view to insert department record in base table .
Viewing ‘Views’ in the Data Dictionary
SELECT * FROM USER_VIEWS;
Sequence:
A sequence is a schema object that can generate unique sequential values. These values
are often used for primary and unique keys. You can refer to sequence values in SQL
statements with these pseudocolumns:
CURRVAL: Returns the current value of a sequence
NEXTVAL: Increments the sequence and returns the next value
You must qualify CURRVAL and NEXTVAL with the name of the sequence:
sequence.CURRVAL
sequence.NEXTVAL
To create a sequence, use the following syntax:
By default, the sequence starts with 1 and the increment value for it is 1.
How to Use Sequence Values:
When you create a sequence, you can define its initial value and the increment between
its values. The first reference to NEXTVAL returns the initial value of the sequence.
Subsequent references to NEXTVAL increment the sequence value by the defined
increment and return the new value. Any reference to CURRVAL always returns the
current value of the sequence, which is the value returned by the last reference to
NEXTVAL.
Before you use CURRVAL for a sequence in your session, you must first
initialize the sequence with NEXTVAL.
Within a single SQL statement containing a reference to NEXTVAL, Oracle
increments the sequence once.
Syntax of SEQUENCE
CREATE SEQUENCE SEQUENCE_NAME [START WITH N] [INCREMENT BY M];
CREATE SEQUENCE SEQ;
Example
SELECT SEQ. NEXTVAL FROM DUAL; -- RETURN 1
SELECT SEQ. NEXTVAL FROM DUAL; -- RETURN 2
SELECT SEQ. NEXTVAL FROM DUAL; -- RETURN 3
SELECT SEQ. CURRVAL FROM DUAL; -- RETURN 3
SELECT SEQ. CURRVAL FROM DUAL; -- RETURN 3
SELECT SEQ. NEXTVAL FROM DUAL; -- RETURN 4