Triggers

A database trigger is code that is automatically executed in response to certain events on a particular table in a database.

Syntax for creating a Trigger:

CREATE TRIGGER <TriggerName> on <TableName> 
FOR INSERT, UPDATE, DELETE
AS
… Create your Code here GO

The Trigger will automatically be executed when data is inserted, updated or deleted in the table as specified in the Trigger header.

INSERTED and DELETED:

Inside triggers we can use two special tables: the DELETED table and the INSERTED tables. SQL Server automatically creates and manages these tables. You can use these temporary, memory-resident tables to test the effects of certain data modifications. You cannot modify the data in these tables.

The DELETED table stores copies of the affected rows during DELETE and UPDATE statements. During the execution of a DELETE or UPDATE statement, rows are deleted from the trigger table and transferred to the DELETED table.

The INSERTED table stores copies of the affected rows during INSERT and UPDATE statements. During an insert or update transaction, new rows are added to both the INSERTED table and the trigger table. The rows in the INSERTED table are copies of the new rows in the trigger table.

Example:

We will use the CUSTOMER table as an example:

We will create a TRIGGER that will check if the Phone number is valid when we insert or update data in the CUSTOMER table. The validation check will be very simple, i.e., we will check if the Phone number is less than 8 digits (which is normal length in Norway). If the Phone number is less than 8 digits, the following message “Phone Number is not valid” be written in place of the wrong number in the Phone column.

The TRIGGER becomes something like this:

IF EXISTS (SELECT name
FROM	sysobjects
WHERE name = 'CheckPhoneNumber' AND	type = 'TR')
DROP TRIGGER CheckPhoneNumber
GO

CREATE TRIGGER CheckPhoneNumber ON CUSTOMER FOR UPDATE, INSERT
AS

DECLARE
@CustomerId int, @Phone varchar(50), @Message varchar(50)

set nocount on

select @CustomerId = CustomerId from INSERTED

select @Phone = Phone from INSERTED
set @Message = 'Phone Number ' + @Phone + ' is not valid'


if len(@Phone) < 8 --Check if Phone Number have less than 8 digits update CUSTOMER set Phone = @Message where CustomerId =
@CustomerId set nocount off
GO

We test the TRIGGER with the following INSERT INTO statement:

INSERT INTO CUSTOMER
(CustomerNumber, LastName, FirstName, AreaCode, Address, Phone)

VALUES
('1003', 'Obama', 'Barak', 51, 'Nevada', '4444')

The results become:

As you can see, the TRIGGER works as expected.

We try to update the Phone number to a valid number:

update CUSTOMER set Phone = '44444444' where CustomerNumber = '1003'

The results become: