Author - StudySection Post Views - 212 views
sql triggers

Introduction to Triggers and Views in SQL

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

fiteesports.com rivierarw.com cratosroyalbet betwoon grandpashabet grandpashabet giriş deneme bonusu veren siteler casino siteleri