_edited_edited.png)
Introduction
Triggers
Triggers are a specific type of procedure that run only when certain conditions are met, such
as the occurrence of an INSERT, UPDATE, or DELETE operation in a table. When a
predetermined event occurs, the trigger is activated and acts as if it were a predefined
operation. The trigger, like the process, can be used without being expressly invoked. The
purpose of setting up triggers is to have predetermined actions taken after the occurrence of
predetermined events.
Certain database activities, such as DDL statements (INSERT, UPDATE, or DELETE),
DML statements (INSERT, UPDATE, or DELETE), or others, can trigger the execution of
the trigger (SHUTDOWN, STARTUP, LOGOFF, LOGIN, and SERVERERROR).
The three parts of the trigger are as follows.
Event:
An event is any occurrence that triggers the action specified in the trigger. In either case, a
trigger could be instructed to run either BEFORE or AFTER the event is carried out.
Condition:
In the context of the trigger, its presence is purely discretionary. There is no need to tell the
trigger when to run; it will do so automatically as soon as the event occurs. If the trigger has a
condition, it will examine the rules to see if it should be triggered.
Action:
When a Trigger is run, it will carry out the actions specified in its Action. These actions are
often SQL statements.
The following describes the organizing framework of event generation:
CREATE TRIGGER /Replace Trigger trigger-name
[Before/After]
Condition [Insert/Update/Delete]
ON table_name
Action;
Triggers Types
There are three distinct trigger types in SQL Server.
Data Manipulation Language (DML) Triggers
Data Definition Language (DDL) Triggers
Logon Triggers
DML Triggers enable us to run scripts whenever there is a change in the underlying data.
That is to say, they make it possible for us to execute supplementary code whenever an
INSERT, UPDATE, or DELETE statement is carried out.
Data-Driven Logic Triggers allow us to react to events on the server, such as the creation or
deletion of a table, or on the database, such as a user logging in, by executing predefined
scripts. Significance determines the two distinct categories of DDL Triggers.
Server Scoped DDL Triggers
Database Scoped DDL Triggers
Server-scoped DDL triggers can be further categorized into Logon Triggers, which are
activated in response to LOGON event which is triggered whenever a new user session is
created.
Why Do We Need Triggers?
The primary goal of using triggers is to automatically run code when a certain event happens.
To clarify, triggers are the way to go if you want a set of instructions to be automatically
carried out once a particular event occurs. Most importantly, they ensure that code would be
performed, even if the event that triggered the trigger fails to occur.
There are a few different uses for triggers:
Initiate further verification of the concerned table insert, update, and delete activities.
They permit the encoding of default values that are too complicated for default restrictions.
Guarantee cross-database consistency by enforcing referential integrity.
They give us fine-grained command over the behavior's of views that access several tables during CRUD operations (INSERT, UPDATE, or DELETE).
Aggregated column values in a table can be calculated with the help of triggers.
When it comes to auditing and enforcing corporate standards, triggers stand out as the clear
frontrunner. A trigger can be used to create a log record that includes details about the user
who made a change and the data that was modified in a specific table.
Example for SQL Trigger
Here example to illustrate.
Creating the trigger “film_title_trigger”
Delete the trigger
The Advantages of Using Triggers
It helps maintain the integrity restrictions of the database tables, especially when commonplace definitions of primary keys and foreign keys are lacking.
These modifications, deletions, and additions are tracked in audit tables together with their respective values, allowing for more visibility into the context of each table and record.
While this may not be the case, badly maintained code might help enforce the database restrictions that have been established for table(s) in which the trigger gets placed. One example is a website for online classes that allows students to sign up for many courses at once.
The Disadvantages of using Triggers
Updating can be challenging since a novice developer may not know when data may be automatically added, removed, or updated without understanding about database and trigger.
When compared to other forms of database code (such as stored procedures, pictures, functions, etc.), they are difficult to inspect and, consequently, debug.
It is possible to severely slow down an application processing performance by defining triggers in several tables. Triggers execute every time data is added, removed, or altered in a database (according also on trigger description).
Overall performance of apps will suffer if complicated algorithms are included in the triggers.
Tables with frequent DML (insert, delete, and update) operations, such as bulk insert, may incur greater expenses due to the development of triggers.
Conclusion
When a predetermined condition is met in a database server, a stored procedure known as a
trigger is activated and runs. While triggers can be helpful, they are typically avoided when
possible because of the complexity they add to the underlying data. There is a suitable
approach that can be used to substitute triggers on occasion. In SQL, triggers are essential
because they facilitate code reuse and reduce computation costs by accelerating response
times. In conclusion, the use of stored procedures in SQL improves not only the likelihood of
code reuse but also the overall efficiency of the database. How? Because less data is being
transmitted across the network, network traffic is being reduced. While memory usage
increases, the seek speed decreases, and code reuse is helpful, the overall cost is minimal.
Stored procedures can be written with or without variables, and they can be called with or
without providing the parameters they were given.