Introduction
This article will teach us about Create, Delete, and Update Triggers in SQL Server.
What are triggers in SQL Server?
Triggers are a special procedure fired automatically when a query is executed on a table or view. Triggers provide a powerful way of controlling how action queries modify data in your database. With the triggers, you can enforce design rules, implement business logic and prevent data inconsistency with flexibility that can't be duplicated any other way. This article with code snippet explains how to write create, update, and delete triggers in SQL. Find a detailed article about Triggers in SQL Server here: Triggers in SQL Server.
How to create triggers in SQL Server?
The CREATE TRIGGER statement provides for two types of triggers: AFTER triggers and INSTEAD OF triggers. Both types of triggers can be defined to fire for an insert, update, or delete operation if an action query has an AFTER trigger; the trigger fires after the successful completion of the action query. If an action query has an INSETEAD OF trigger the trigger is fired instead of the action query. In other words, the action query is never executed.
CREATE TRIGGER trigger_name ON {table_name|view_name}[WITH ENCRYPTION]{FOR|AFTER|INSTEAD OF} [INSERT] [,] [UPDATE] [,] [DELETE] As sql_statements
FOR is the same as AFTER, but it is for backward compatibility.
Each trigger is associated with the table or view names in the ON clause. Although each trigger is associated with a single table or view, a single table can have many AFTER triggers. Since two or more triggers on the table can confuse managing and debugging, placing all the related code in one trigger for each action is better. A view can't have AFTER triggers.
CREATE TRIGGER Shashi_INSERT_UPDATEON Shashi AFTER INSERT,UPDATEASUPDATE ShashiSET ln_name = UPPER(ln_name) WHERE Shashi_id in (SELECT Shashi_id from Inserted)
The CREATE TRIGGER statement in the above example defines an AFTER trigger for the Authors table. In this case, the trigger fires are performed on the table after an insert or update operation. If you closely observe the trigger body, we have used a subquery and a table named Inserted in from clause; this is a unique table that SQL Server creates during an insert operation. It contains the rows that are being inserted into the table. This table exists while the trigger executes; you can only refer to it in the trigger code. In addition to the inserted table, you have one more table, i.e., deleted, which contains the information about the rows deleted. These tables are called Magic tables.
AFTER triggers can be used to enforce referential integrity. An AFTER trigger fires after the action query is executed. If the action query causes an error, the AFTER trigger never fires.
An INSTEAD of trigger can be associated with a table or view. However, INSTEAD OF triggers is often used to control updatable views better.
INSTEAD OF trigger is executed instead of the action query that causes it to fire. Because the action query is never executed, the trigger typically contains code that operates. Each table or view can have only one INSTEAD OF trigger for each type of action.
How to delete or Change a Trigger?
To change the definition of a trigger, you can use ALTER TRIGGER or drop the trigger using DROP TRIGGER.
The syntax of the DROP triggers statement.
DROP TRIGGER trigger_name [,...]
The syntax of the ALTER TRIGGER statement
ALTER TRIGGER trigger_nameON {table_name|view_name}[WITH ENCRYPTION]{FOR|AFTER|INSTEAD OF} [INSERT] [,] [UPDATE] [,] [DELETE]As sql_statements
Conclusion
This article taught us about Create, Delete, and Update Triggers in SQL Server. Continue reading about triggers here: Triggers in SQL Server