Session5_Module9_Triggers pps

Triggers / Session 5/ 1 of 52

Session 5

Module 9: Introduction to triggers

Triggers / Session 5/ 2 of 53

 A stored procedure is a group of Transact-SQL

statements executed as a single block of code.

 SQL Server 2005 provides various system stored

procedures that assist in database administrative

activities.

 User can create custom stored procedures for

performing various tasks.

 Deferred name resolution is the stage where the

processor checks for names of the objects

referenced by the procedure. This check is done

during the execution of the procedure.

 When a procedure is executed, parameters can be

passed between the calling program and the stored

procedure.

Module 7 - Review

Triggers / Session 5/ 3 of 53

Module 8 - Review

 The sp_helptext procedure can be used to display the

stored procedure definition.

 An object that references another object is considered

dependent on that object. The sp_depends procedure

is used to display the information about database

object dependencies.

 In a stored procedure, statements or parameters can

be changed by either re-creating the stored procedure

or by altering an existing stored procedure.

 A stored procedure can be dropped if it is not needed

anymore.

 Stored procedures can be nested; that is, a stored

procedure can be called from within another stored

procedure.

 In stored procedures, error handling is done using the

TRY…CATCH construct.

Triggers / Session 5/ 4 of 53

Module 9: Introduction to

triggers

Objectives

Database Objects/ Session 5/ 5 of 52

Triggers

 A trigger is a stored procedure that is executed when an attempt is

made to modify data in a table that is protected by the trigger.

 Triggers cannot be executed directly, nor do they pass or receive

parameters.

 Triggers are defined on specific tables and these tables are referred

to as trigger tables.

 If a trigger is defined on the INSERT, UPDATE, DELETE action on a

table, it fires automatically when these actions are attempted. This

automatic execution of the trigger cannot be circumvented.

 In SQL Server 2005, triggers are created using the CREATE

TRIGGER statement.