Triggers Wizard (SQL Server)
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.
Rapid SQL 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:
- Open a creation wizard for a trigger. For details, see Opening an Object Wizard.
- Use the following topics as a guide to setting properties and performing tasks as you pass through the wizard panels:
- Properties panel - for details, see Triggers (SQL Server) - Properties.
- Definition panel - for details, see Triggers (SQL Server) - Definition.
- 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.