Easy Steps to Understanding SQL
SQL is one of the most popular languages for communicating with RDBMs. While the basics of reading/writing data (SELECT, INSERT, DELETE) are straight forward, making use of things like stored procedures and triggers are more advanced topics that we will cover in this article. Specifically, we will look at stored procedures, functions, views, triggers and the advantages of using each to read/write data.
Stored Procedures
Stored procedures are essentially just packaged SQL commands that you can execute with EXEC. They are similar to bash scripts and provide users the ability to perform multiple reads/writes in a single command.
Although stored procedures can't be used in queries themselves, they have their advantages. They are arguably more secure as application logic is separated from changing logic. If an app calls a stored procedure updateUser() to update a user object then it doesn't have to know the details behind what's changing on the backend. It simply calls updateUser(). This makes for better separation of app logic and improves security.Another advantage is performance. Stored procedures can be faster because of precompiled execution. They can also be reused which improves development efficiency.
Triggers
Triggers are like stored procedures in that multiple reads/writes can happen in one command. The main difference between triggers and stored procedures is that triggers are tied to events. They listen for things like insert, update, delete and run based on those events occurring. Unlike stored procedures, you can't explicitly call a trigger. It must be tied to another database event.
There are advantages and disadvantages to triggers. While triggers make for great automated reactions to other events, they can be rather difficult to maintain. Unexpected things can start happening when you have hidden triggers defined that you forget about. By not having to explicitly call a trigger, developers can quickly forget what's happening with seemingly simple read/write operations.
Views
Views are like virtual tables. They can be queried like real tables but are stored on the database server as a SQL statements. Views give you the ability to encapsulate very complex joins, calculations, etc. into custom tables that are easier to query.
Views also have security benefits. Similar to stored procedures, users can query views without knowing the underlying tables or datasets they represent. This also makes it easier to change schemas and data models without breaking existing app logic. Users can still access the same view even though the underlying schema may change.
Functions
Functions can be written to perform additional computations inline with regular SQL statements. Functions are fundamentally different from stored procedures because they can't change data or create permanent changes to the database itself. They are essentially helper functions that make your database queries easier to manage.
Conclusion
Although stored procedures, functions, triggers, views etc. add more complexity to a seemingly intuitive language, it's important to remember that it's all SQL at the end of the day. If you're comfortable with writing plain old SQL statements then these are simply ways to more efficiently package those statements and improve performance.
