SQL Server Database Triggers

  • A trigger is an object contained within an SQL Server database that is used to execute a batch of SQL code whenever a specific event occurs.
  • A trigger is “fired” whenever an INSERT, UPDATE, or DELETE SQL command is executed against a specific table.
  • A Trigger is associated with Database Table

There are three types of Database Triggers –

  1. INSERT TRIGGER
  2. UPDATE TRIGGER
  3. DELETE TRIGGER

INSERT TRIGGER

  • Body of trigger can have any block which a Stored Procedure can have
  • Body can call SP using Exec
  • When SQL server processes this “INSERT” command, it creates a new virtual table, which contains all nine of the fields in the “INSERT” command. This table is named “Inserted”, and is passed to the trig_addAuthor trigger.
  • At any one time, each trigger only deals with one row.
  • So for INSERT Trigger only hold reference of Inserted virtual Table

CREATE TRIGGER trig_addAuthor
ON authors
FOR INSERT
AS
— Get the first and last name of new author
DECLARE @newName VARCHAR(100)
SELECT @newName = (SELECT au_fName + ‘ ‘ + au_lName FROM
Inserted)
— Print the name of the new author
PRINT ‘New author “‘ + @newName + ‘” added.’

UPDATE TRIGGER

  • The “UPDATE” function is used to check whether or not the “au_fName” and “au_lName” fields have been updated by the “UPDATE” Query
  • “UPDATE” triggers have access to two virtual tables: Deleted (which contains all of the fields and values for the records before they were updated), and Inserted (which contains all of the fields and values for the records after they have been updated)
  • Update triggers can also be used to check field constraints and relationships

CREATE TRIGGER trig_updateAuthor
ON authors
FOR UPDATE
AS
DECLARE @oldName VARCHAR(100)
DECLARE @newName VARCHAR(100)
IF NOT UPDATE(au_fName) AND NOT UPDATE(au_lName)
BEGIN
RETURN
END
SELECT @oldName = (SELECT au_fName + ‘ ‘ + au_lName FROM Deleted)
SELECT @newName = (SELECT au_fName + ‘ ‘ + au_lName FROM Inserted)
PRINT ‘Name changed from “‘ + @oldName + ‘” to “‘ + @newName + ‘”‘

DELETE TRIGGER

  • It has only access to the virtual table “Deleted”
  • Same as INSERT, UPDATE

ADVANTAGES Of TRIGGERS

  1. When triggers are used correctly, they can save a lot of development work. One of the main benefits of using triggers is that they are stored in a central repository (the database), meaning that they are accessible from all client applications / web pages that can connect to the database.
  2. Before triggers came along, if we had a table that needed to be updated and we wanted to perform some actions after that update, then we would have to “hard code” the extra SQL into our application. This meant that if we wanted to change the code later down the track, then each client would need the updated version of our application. This is both annoying and time consuming.

Happy Coding 🙂