Triggers Wizard (SQL Server)

From DBArtisan
Jump to: navigation, search

Go Up to Microsoft SQL Server Object Wizards

Triggers are a special type of procedure that automatically fire when defined data modification operations (insert, update, or delete) occur on a target table or view. Triggers fire after an insert, update or delete, but belong to the same transaction as the data modification operation. Triggers can be implemented to enforce business rules or referential data integrity.

Important Notes

  • For more information on the syntax for Trigger bodies, consult the Microsoft SQL Server Transact-SQL Documentation.

DBArtisan also supports natively-compiled triggers, which are Transact-SQL stored procedures compiled to native code that access memory-optimized tables. Natively compiled stored procedures allow for efficient execution of the queries and business logic in the stored procedure.

To create a new trigger using a wizard:

  1. Open a creation wizard for a trigger. For details, see Opening an Object Wizard.
  2. Use the following topics as a guide to setting properties and performing tasks as you pass through the wizard panels:
  3. Finally, use the Execute button to create the object.
    Note: You can use the Trigger Editor opens to create dependencies or alter the trigger statement.

Triggers (SQL Server) - Properties

When creating or editing a trigger, this tab/panel lets you work with the following settings:

Setting Description

Parent Type

Select the TABLE or VIEW on which the trigger is to be created.

Parent Schema

Select the owner of the table or view on which the trigger is to be created.

Parent Name

Select the specific table or view in which the trigger is to be created.

Name

Provide a name for the trigger.

Trigger Timing

INSTEAD OF: This is the only option for a View trigger. An INSTEAD OF trigger fires in place of the triggering statement and will not make changes to the data unless the conditions of the INSTEAD OF statement are met first. So, your UPDATE execution statement is replaced by an INSTEAD OF UPDATE statement as a way to enforce particular business rules you establish. AFTER: An AFTER trigger fires following the successful completion of the triggering action. So, for example, the trigger would fire after an UPDATE statement has executed and after constraints have been checked and verified.

Fire On Insert

An INSERT trigger must be associated with an INSERT statement. For example, if a data load operation doesn’t include an INSERT statement, the trigger won’t be invoked.

Fire On Update

An UPDATE trigger can be associated with specific columns of the base table and will only be activated if those columns are updated.

Fire On Delete

A DELETE trigger is associated with a DELETE operation.

Encrypted

If you choose to encrypt the trigger, the trigger can’t be published as part of SQL Server replication.

Native Compilation

This option sets natively-compiled triggers that allow for efficient query execution within the stored procedure.

4. Finally, use the Execute button to create the object.

Triggers (SQL Server) - Definition

Complete the CREATE TRIGGER outline provided by typing or pasting the body of the trigger.