Create a Trigger

A trigger is a database object that you create by specifying:

• The current table.
• The data modification transactions that activate the trigger: adding new data (INSERT), updating existing data (UPDATE), or deleting existing data (DELETE).
• The actions that the trigger will take immediately following the transactions you specified.

You write triggers in Transact-SQL for MicrosoftSQL Server databases.

1. In the Database window, click Tables under Objects.
2. Right-click the name of the table that you want to create a trigger on.
3. Choose Triggers from the shortcut menu.
4. In the Triggers for Table: <table name> dialog box, click New.

   A new trigger is created in the SQL text source code editor with the default SQL statements already defined for you.

   

5. Modify the default trigger text as follows:

   Replace	With
   TABLE1_Trigger1	The name you want to assign to the trigger
   dbo.TABLE1	The name of the table you want to attach the trigger to
   /*Insert, Update, Delete*/	The type of transactions that will activate this trigger
   /*If Update (column_name) …*/	The transactions that will take place

6. Write the remaining trigger text in SQL.

For syntax and examples of triggers, see the Microsoft SQL documentation.

Note   Include both a RAISERROR statement and ROLLBACK transaction together in a trigger for situations when the trigger determines that a modification must be disallowed. This will ensure that the client record set stays in synch with the data at the server. If you include only the RAISERROR statement or ROLLBACK transaction, but not both, the client record set will not reflect the current state of the data at the server. The RAISERROR statement should be defined with a severity level greater than 10. For more information, see RAISERROR and ROLLBACK Transaction in the SQL Server documentation.