Triggers:
Triggers are event-condition-action (ECA) rules. When an event occurs, test the condition if the condition is satisfied, execute the action.
Example:
- Event: Save Percentage
- Condition: Marks in subjects is not equal to zero
- Action: Calculate the percentage of the student.
Trigger syntax and example:
create trigger student_marks
before INSERT
on
Students
for each row
WHEN (Student.subj1>=0) AND (Student.subj2>=0)
set Student.total = Student.subj1 + Student.subj2, Student.percentage = Student.total * 60 / 100;
Trigger options
Possible events include:
- INSERT ON table
- DELETE ON table
- UPDATE [OF column] ON table
Granularity—trigger can be activated:
- FOR EACH ROW modified
- FOR EACH STATEMENT that performs modification
Timing—action can be executed:
- AFTER or BEFORE the triggering event
- INSTEAD OF the triggering event on views7
VIEWS:
A view is sort of a “virtual” table defined by a question, which describes the way to compute the view contents on the fly. DBMS stores the view definition query rather than view contents.
Creating and dropping views:
To create a view for members of Jennifer’s Circle
CREATE VIEW JenCircle AS SELECT * FROM User WHERE userid IN (SELECT userid FROM Member WHERE groupid = 'jen');
- Tables (User and Member) used in defining a view are called “base tables”
To drop a view
- DROP VIEW JessicaCircle
Why use views?
- To hide data from users
- To hide complexity from users
- Logical data independence
- If applications affect views, we will change the underlying schema without affecting applications
- Recall physical data independence: we can change the physical data organization without affecting the applications related.
- A uniform interface can be provided to different implementations or sources.
Real database applications use tons of views
Get certification for your knowledge in the fundamentals of Computer functioning by clearing the Computer Certification exam conducted by StudySection. After going through this Computer Certification Exam, you will be able to evaluate your basic knowledge of computers.
