Data experts and developers can use the TRIGGER SQL command to program instructions on their database according to certain events.
How do you create a trigger? What are the different types of triggers? What are the advantages and disadvantages? DataScientest answers all your questions.
What is a trigger?
A trigger is a database object associated with a table. It executes a set of instructions when certain events take place. For example, the trigger may be triggered when a row is added to the table, or when a column is updated, and so on.
Whatever the event, the aim of this function is to automate various tasks within a Database Management System (DBMS).
Unlike most SQL commands, the trigger is not invoked directly. It depends on a third-party event. For this reason, the procedure is stored in the database until the event actually occurs. But this is not a traditional stored procedure. Firstly, because the trigger is not invoked or executed manually. Secondly, triggers cannot receive parameters. Finally, because a transaction cannot be validated or cancelled within a trigger.
How do I create a trigger with SQL?
You can use TRIGGER SQL by writing the syntax below:
CREATE TRIGGER trigger_name {BEFORE | AFTER} {INSERT | UPDATE| DELETE} ON table_name FOR EACH ROW trigger_body;
Each of these elements needs to be described:
- CREATE TRIGGER trigger_name: this is where you send the TRIGGER request. This involves naming your trigger.
- {BEFORE | AFTER} : this concerns the temporality of the trigger. Will it take place before or after the event occurs?
- {INSERT | UPDATE| DELETE } : this concerns the event. In this case, add, update or delete. Of course, any type of event is possible.
ON table_name: this is the database involved in the SQL TRIGGER request. - FOR EACH ROW: specify the rows affected by the trigger.
trigger_body: this is the description of your trigger. What will happen if the event occurs?
What are the different types of trigger?
There are 3 main categories of SQL triggers. Here’s what you need to know:
- DDL triggers: DDL stands for Data Definition Language. Command events corresponding to DDL triggers activate this type of SQL trigger.
- For example: Create_table, Create_view, drop_table, Drop_view or Alter_table.
- DML triggers: DML stands for Data Manipulation Language. The associated events are insert_table, update_view, and delete_table. In this case, triggers are used to define data management and integrity rules.
- Logon triggers: these correspond to the creation of a user session with an instance of SQL Server.
What are the advantages and disadvantages of TRIGGER SQL queries?
The benefits of triggers
SQL triggers are widely used in database management. There are several reasons for this:
- Data integrity: triggers define rules for database objects. If these rules are not respected, triggers automatically undo the faulty modifications. Furthermore, before being integrated or updated, the data is always validated by the trigger, which checks that no rule prevents these modifications.
- Automation: triggers examine all DBMS data. When certain events occur, they make changes automatically, without any human intervention.
- Tracking: SQL triggers simplify record-keeping.
- Simplicity: triggers don’t need to be compiled in the database each time they are run. In doing so, they reduce code, improve SQL query performance and optimize labor time.
The disadvantages of SQL triggers
Despite all the advantages mentioned above, SQL triggers also have a few limitations you should be aware of. Here they are:
- Extended validations are only possible with triggers.
- Because triggers are automated, users don’t know when they are executed. This can make problem-solving complex.
- When many events occur at the same time, triggers can increase server load.
- CREATE TRIGGER can only be applied to a single table. If you want to use the same trigger for several tables, you’ll have to retype the syntax several times.
Join DataScientest to develop your skills with SQL
While SQL triggers are one of the must-have queries in the SQL language, they are far from the only one. There are also SELECT, DELETE, UPDATE commands… The possibilities are endless. So if you want to master your database to the full, it’s best to learn how to use these different SQL queries. That’s exactly what DataScientest training courses can do for you.
