T-SQL » Triggers » Creating a Trigger

Syntax:
CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
    {
       { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
       [ WITH APPEND ]
       [ NOT FOR REPLICATION ]
       AS
       [
          {
             IF UPDATE ( column )
                [ { AND | OR } UPDATE ( column ) ... ]
             |
           &nbs
trigger_name
Is the name of the trigger. It should conform to the rules for identifiers.
table
Is the table on which the trigger is to be created.
WITH ENCRYPTION
If this option is specified, the syscomments entries that contain the text of CREATE TRIGGER will be encrypted.
DELETE, INSERT, UPDATE
These keywords specify on which action the trigger should be activated. One of these keywords or any combination thereof in any order can be used.
WITH APPEND
This specifies that an additional trigger should be added.
IF UPDATE
This checks for an INSERT or UPDATE to a specified column and is not used with the DELETE operations. One or more columns can be specified here.
column
It is the name of the column to check for an INSERT or UPDATE action.
IF
Checks, whether the mentioned column or columns were inserted or updated.
COLUMNS_UPDATED
Can be used anywhere inside the body of the trigger.
bitwise_operator
It is the bitwise-operator used for the comparison.
updated_bitmask
This is the integer bitmask of the columns that are actually updated or inserted.
comparison_operator
Is the comparison operator. The equals (=) sign checks whether all columns specified in the updated_bitmask are actually updated. The greater (>) than symbol checks whether any or some of the columns specified in updated_bitmask are updated.
column_bitmask
Is the integer bitmask of those columns to check whether they are updated or inserted.
sql_statement
Is the trigger condition(s) and action(s).

A TRIGGER is created using the CREATE TRIGGER command.

Examples

Code:
CREATE TRIGGER trigAddStudents
ON Students
FOR INSERT
AS
DECLARE @Newname VARCHAR(100)
SELECT @Newname =(SELECT Name FROM INSERTED)
PRINT 'THE STUDENT ' + @Newname + ' IS ADDED.';
Explanation:

Executing this creates a new trigger named trigAddStudents, which is attached to the 'Students' table. Whenever a new record is added to the 'Students' table, SQL Server will automatically execute our trigger.

Let's look at the above example in detail:

CREATE TRIGGER trigAddStudents
ON Students
--A new trigger object, trigAddStudents, should be attached to the 'Students' table.

FOR INSERT
--The trigger will be fired when an INSERT command is executed on the 'Students' table
(If we would like to handle the INSERT and UPDATE events, we would have to use FOR INSERT, UPDATE).

AS
DECLARE @Newname VARCHAR(100)
SELECT @Newname = (SELECT Name FROM Inserted)
--When the trigger is called the queries after the AS keyword is executed.

SELECT Name FROM INSERTED
--The SQL command retrieves the 'Name' field from the inserted table. The inserted table, which contains all the values we inserted using the INSERT command.
(Similarly for UPDATE and DELETE command we can use UPDATED and DELETED.)

PRINT 'THE STUDENT ' + @Newname + ' IS ADDED.'
--Prints the name selected from the INSERTED table

Language(s): MS SQL Server
Code:
INSERT INTO Students VALUES (6,'George Mathew', 1, 25542, 1);
Output:
THE STUDENT George Mathew IS ADDED.
(1 row(s) affected)
Explanation:

When the above INSERT statement is executed, the new record is added to the 'Students' table and automatically calls the trigger.

Note:

  1. The name of a trigger should follow the rules for identifiers.
  2. The CREATE TRIGGER must be the first statement in the batch.
  3. Triggers cannot be created on a view, temporary table or system table, but they can reference views or temporary tables.
  4. The system tables should not be referenced in a trigger. Use the Information Schema Views instead.

Language(s): MS SQL Server