A Trigger In SQL – What Does it Trigger?

SQL – An Intuitive Way to Interact with Databases

SQL has become very popular and is a universally acceptable database language used to communicate with a relational database. Every business has data and requires organizational methods or data management processes to maintain the data. So it is important to learn and understand SQL and how data structures are used to store information in an organization. SQL is the language you use to convey your needs to the database. You can learn this single language, and with minor adjustments, can apply it to a wide variety of database platforms, applications, and products. You can request specific information from within a database by writing a query in SQL. A trigger is one such query that gets executed when changes are made to the structure of a database table.

What’s a Trigger in SQL?

Triggers are event-driven specialized procedures that are stored and managed by the DBMS. A trigger is a special form of a stored SQL procedure that initiates an action or rather fires an action when an event such as INSERT, DELETE or UPDATE occurs. A trigger can also contain INSERT, UPDATE and DELETE logic within itself, so when the trigger is fired because of data modification it can also cause another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself is called a nested trigger.

Are Triggers Stored Procedures?

Triggers are similar to stored procedures in many aspects as in that both of them use procedural logic stored at the database level. Triggers are often referred to as a “special kind of stored procedure.” But unlike triggers, stored procedures are not event-drive and are not attached to a specific table. Each trigger is attached to a single, specified table in the database and is fired only when an event of INSERT, UPDATE or DELETE occurs. A Trigger cannot be called or executed but DBMS automatically fires the trigger as a result of a data modification in the associated table. While creating a trigger you have to specify the modification action that will fire the trigger.

These are the trigger events which are permitted:

• INSERT: The trigger activates whenever a new row is inserted into the table; for example, through INSERT, LOAD DATA, and REPLACE statements.

• UPDATE: The trigger activates whenever a row is modified; for example, through UPDATE statements.

• DELETE: The trigger activates whenever a row is deleted from the table; for example, through DELETE and REPLACE statements.

A trigger is mostly used by developers to enforce business rules and to perform tasks such as inserting data into any audit log. It is used to perform a certain action as a result of an INSERT, UPDATE or DELETE event.

Creating and Working with Triggers

Here are the basic steps you need to follow to create and modify a trigger:

1. Click and expand the node for the table to which you want to add the trigger.
2. Now right click the ‘Trigger’ node and select ‘New Trigger.’
3. A new query window opens, and type in the query for the new trigger here.
4. Execute ‘CREATE TRIGGER.’
5. The newly created trigger will now appear under the lsit of triggers associated with that table.

Basic Syntax

The syntax for a trigger is:

CREATE TRIGGER Trigger_Name
On Table_Name
For [INSERT], [UPDATE], [DELETE]
AS
……Trigger Code

Expanded Trigger Statement Formats

The statement below creates a new trigger. The trigger becomes associated with the table named tbl_name, which must be a permanent table.

CREATE
[DEFINER = { user}]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
trigger_body

Explanation of the syntax:

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

All triggers must have unique names within a schema, but triggers in different schemas can have the same name.

The DEFINER clause determines the checking access privileges f the users at the time a trigger is activated.

trigger_time is the trigger action time. BEFORE or AFTER can be used to indicate that the trigger activates before or after each row to be modified.

trigger_event indicates the kind of operation that activates the trigger.

Examples of Trigger Statements

Example 1

The Trigger example given below checks if the product the user is trying to delete is discontinued. If the product is discontinued then the delete process stops and an error message appears.

CREATE TRIGGER Deleterec
ON dbo.Products
FOR DELETE
AS
DECLARE @Discontinued Bit
SELECT @Discontinued = Discontinued FROM deleted
If @Discontinued = 0
BEGIN
ROLLBACK TRAN
RAISERROR (‘Active product cannot be deleted’, 16,10)
END

Example 2

The following example inserts data into an audit log when a user updates data in the Products table:

CREATE TRIGGER InsertProductAudit
ON dbo.Products
FOR UPDATE
AS
DECLARE @ProductID int, @Productname VarChar (40)
SELECT @ProductID = ProductID, @Productname = Productname from inserted
INSERT ProductsAudit VALUES (@ProductID, @Productname)

Example 3

CREATE TABLE Source (Source_ID int IDENTITY, Source_Desc varchar(10))
go
CREATE TRIGGER Source_INSERT
ON Source
FOR INSERT
AS
PRINT GETDATE()
go
INSERT Source (Source_Desc) VALUES ('Test 1')

Output of the above Trigger code:

———–Apr 28 2001  9:56AM——–

Why do Some Programmers Avoid Using Triggers?

Many database developers or programmers avoid triggers entirely. The biggest disadvantage of triggers is that triggers get buried in your database and are difficult to debug and troubleshoot as they appear as nodes under the table they are associated with. Triggers also slowdown database operations. They also cause inconvenience as they often lock data for relatively long periods of time. That’s why most developers opt to use stored procedure functions instead of Triggers.

Conclusion

To know more about stored procedure functions and to have a better understanding of other SQL queries and codes, browse through SQL Queries 101. Courses such as SQL Server Essentials: What you should know!  and Practical SQL Skills from Beginner to Intermediate will also help you to learn the key SQL skills to boost your career.