What is a MS SQL Server Trigger?

A trigger is an object contained within a SQL Server database that gets called each time a row in a table is INSERTED, DELETED, or UPDATED. It is used to execute a batch of SQL code whenever one of these SQL commands, INSERT, UPDATE, or DELETE, is executed against a specific table.

Trigger is stored in the database and can be accessed from any client or web page that connects to the database. If used correctly, trigger can save developers a large amount of time and work.

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 ) ]

[ ...n ]

| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )

{ comparison_operator } column_bitmask [ ...n ]

} ]

sql_statement [ ...n ]

}

}

Example:

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.'

Read more on this at www.devarticles.com.