In this post I will tell something about Basic Database Objects in SQL like Databases, Tables, Views, Stored Procedures and Triggers.
What is a Database?
A database contains a set of tables and other objects. Each database ofcourse has a name.
What is a table?
Within a database you create tables that store information about something. For example a simple Table that stores Persons and information about them like names and their ages. For every field we need to specify what data it is going to hold (datatype). Will it, for example contain a boolean, text or integer data?
What is a view?
With a view you could obscure the real tablename for a virtual one. A big advantage is that you can define for example a WHERE clause in your view. This way when referring to this view you don’t need to specify the where clause. This makes certain operations like building or maintaining the query for your users a lot simpler!
What is a Stored Procedure and Trigger?
A stored procedure is prepared SQL code that you save. This way you can reuse it over and over again just like functions in programming languages. Triggers do the same thing except that they will be only executed when they are triggered by an event. So triggers can’t be executed manual, Stored Procedures do.
What is a Synonym?
Synonyms are a great function to obscure table names. What if a table name changes every day because of archiving reasons? With a synonym you could easily fix this problem. Synonyms are being used to redirect to a source that changes regularly like Archive Tables. This way you don’t need to hardcode the reference to the specific database.